Tuesday, April 08, 2008

Configuring SQL 2005 so new database put files on different drives

Microsoft's SQL 2005 best practice recommendations include splitting binary, database files, and transaction logs onto different drives. When you install SQL 2005, you can used the advanced options to change the default data directory but this will put the MDF and LDF files in on the same drive. This really doesn't matter for the system databases but you don't want that to be true for all database that get created later. You actually need to do two different processes to ensure that new databases get configured correctly:

Change the defaults for manually created databases

  1. Use SQL Management Studio and connect to the database
  2. Right click the server name at the top of the hierarchy and select options
  3. Go to Database Settings
  4. Change the Default Database location




Change the model database

Databases that are created by scripts or software installers do not grab the settings we just changed. They copy the settings from the model database. Since the model database defaults to putting the MDF and LDF files in the same folder you need to modify it. This gets a little tricky, though:

  1. Open SQL Configuration Manager
  2. Find the service that defines you database, right click it, and select properties
  3. Go to Advanced and find the start up parameters section
  4. Enter ;-c;-T 3608 at the end of the current string. As a side note, the Microsoft documentation for this does not mention the semi-colons
  5. Restart the SQL service. The parameters that we put in will let you modify the temp database. Without those tags, the database is "locked".
  6. Use SQL Management Studio to connect to the database
  7. Open a new query and make sure the current database is set to master
  8. Run the following query: sp_detach_db 'model'
  9. Move the modellog.ldf file to the path you want logs to be
  10. Run the following query:sp_attach_db 'model','[Path to Data Files]\model.mdf','[Path to log files]\modellog.ldf'
  11. Use SQL Configuration Manager to remove the parameters we added.
  12. Restart the SQL service


That should make your SQL server create new databases the way you want them to.


No comments: