Detach a database and take out a cluster.

During testing I managed to take my SQL 2008 cluster offline by simply detaching a database.

See http://msdn.microsoft.com/en-us/library/ms189128.aspx  explaining the background.

When you detach a database the ntfs file permissions go to the account that issued the detach, any other permissions, such as those to the SQL Service account are removed. This apparently is considered safer, when you attach, the permissions revert to the SQL Service so everything is ok.

Well should your database mdf or ldf file exist in the root of a clustered drive and you detach the database the drive will instantly go offline, as this will be a dependant drive this will also take your SQL Server offline in double quick time.

The moral of the story is to always make sure your ldf and mdf files exist in a sub folder and never in the root.

Personally, I think this is a bad move ( the permissions ) I spend some time making sure that no indivdual can ever own databases, database files, jobs and so on, now if I detatch a database as myself ownership becomes mine alone -- aarrghhh!  And before there's a comment about logging on as the service account, best practice says you should never use a generic or servcie account to connect - doesn't really leave a good audit trail and if multiple people have access to an account that's even worse!

 

Published 01 February 2010 14:44 by GrumpyOldDBA

Comments

# Don't put your mdf and ldf in the root of your cluster drives

05 February 2010 22:33 by Simon Sabin UK SQL Consultant's Blog

# Don't put your mdf and ldf in the root of your cluster drives

05 February 2010 22:57 by SimonS Blog on SQL Server Stuff

Colin has just blogged about a nasty error that occurs if you have your data and/or log files in the