Derrick Leggett Blog

This is something I see posted continuously on various forums.  Some fine, young idiot on one forum yesterday said the solution was to just detach and attach all the system databases.  That doesn't work in case you 're just itching to try it.

Luckily for us Oracle, I mean Microsoft, has done a fine job though of outlining a solution for this very issue (hard to believe I know).  Every DBA should take this link and store it away in their little toolbox of bookmarks:

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

Why would you want to do this?

• Many people, myself included, like to keep all the databases in a standard directory structure so it's easy to manage.  This becomes more important in larger environments with multiple servers and instances.  Where I work now, we are implementing a structure to standardize across all servers since I have over 30 instance spread across more then 25 servers.
  • Data Files for example will all be located in:  d:\mssql\data\instance\files.mdf
  • Log Files will be:  d:\mssql\log\instance\files.ldf
  • Standby Files will be:  d:\mssql\standby\instance\files.ldf
  • Backup Files will be:  d:\mssql\backup\instance\database\files.bak
    
• Another reason to do this, and probably the most common, is to seperate the log and data files, tempdb, and system databases onto seperate drive arrays.
  • In an optimal environment, the tempdb data file is on a seperate array, preferrably RAID 10 or 1.
  • The data files are generally kept on RAID 5 for reasons of economy.  RAID 10 will provide better performance but costs much more.
  • The log files are usually kept on RAID 10 to allow maximum write capacity.  For larger databases, it would further increase performance to have each log file on it's own array; however, there is a cost-to-benefit ratio that normally does not allow this.

Items to be aware of:

• Make sure you pay special attention to the order outlined in the Microsoft document.  The order of placing/removing the trace flag, starting/restarting the services, and moving the database files is extremely important if you don't want to be pulling your hair out at 3:30 in the morning trying to figure it out.  They didn't make any mistakes on this document, so if it gets messed up, it's your fault.
• If you screw up and get stuck with a server that won't start because you changed the master database files to the wrong location, the registry will need to be edited.  The appropriate keys are here:  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters.
• Be EXTREMELY careful that you attach and detach the msdb and model databases in the appropriate order.  You will notice that if you do them at the same time, there is a different order then doing them seperately.  Not following this order has been known to cause serious ulcers and heartburn in the unsuspecting, would-be DBA or the tired, over-worked DBA.

Favorite words used: 0 (idiot, itching, Oracle, hair, screw up, ulcres, heartburn, unsuspecting, tired, over-worked)

--Oracle because I hate them....hair because so many have none.  All things considered, it's quite the list.

Mean level (1-10):  1 --The idiot deserved it.

Education level (1-10):  3 (Hopefully someone will read this and save themselves a lot of pain.)

Entertainment level (1-10): 2 --I got to call someone an idiot, so it was entertaining for me.