6

Is it worth moving the SQL Server system databases master, model and msdb from the default install path on the main OS hard drive over to their own drive?

I've already moved TempDB on to 2 separate drives one for the log file and one for the data file and am just wondering if its worth doing the same to the other system database or if this is overkill and will see little to no benefit?

4 Answers 4

4

I see not reason for moving them at all -- they are small DBs that are cached in memory pretty much all the time. You are not going to make changes very often in those DBs (writes) to have them moved to another drive to gain some additional performance.

3

It depends. There are some cases where you might want to put them on a dedicated drive (which I've done once or twice). Usually this is in a clustered setup where you can't put them on the OS drive, and you don't want them on the drive with the user databases on them, so a separate drive it is.

1

I couldn't think of any reason to move your system databases to dedicated drive.

It's good (and recommended) practice to dedicate drives to TempDB and all user databases - as a very general directive.

0

There is a big downside to moving System databases (including master) to a separate drive. Say you have SQL Server on the C drive. Then you put your System databases on a D drive with the User databases. And finally, you backup to an E drive (both System and User databases as compressed .bak files). If your server goes down, you're going to restore C and E drives, but D will often not be part of the backup plan. However, you can't RESTORE to a SQL Server instance that has no System databases (it cannot be started), plus your System backups are compressed into .bak files (not .mdf and .ldf). And you can't REPAIR to a SQL Server instance that has no system databases. Therefore, you have to uninstall and reinstall SQL Server to recreate the System databases.

There's more. When you re-install, your instance of SQL Server must be brought back to the exact patch level the server was at when it went down (System databases are version specific). However, Microsoft does not keep Cumulative Updates indefinitely, so if you are on an old enough CU, and it's no longer available to download, you're in a bad spot. So, remember to keep the install for your current patch level somewhere safe (network store, part of the backup plan).

Alternatively, I think you can try to restore your System database .bak files to another SQL Server (at or above the patch level of the down server?) as different file names, then detach them and rename them back. Finally, move them to the server you are restoring. I haven't tried this, but I've read of others who did.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .