Placement of the Master database...

[bessebo]

Let's say that yesterday was a pretty harrowing one for me as we had 2 of our drives in a Raid 5 array fail over the weekend. These were data drives pertaining to our ERP system. As a result, we had to replace the drives, reinstall SQLServer, and restore full backups from Friday night and apply transaction log backups for any Saturday activity.
After the fact it may have made sense for me to have the master database placed on a different drive than the data because, if I did, SQLServer would still be running and I wouldn't have had to restore the master database or re-install SQLServer. I am wondering if most DBAs place the Master database on a different RAID than the data in order to facilitate recovery? I have Tempdb on a separate drive and my transaction logs on another drive which is recommendd practice. I also assume that if I want to move the master at this point that I simply use the alter database command?

 

[mrdenny]

System database placement will depend on the type and size of the system. For smaller servers I'll put everything in the same place. Same goes for servers on a SAN.

For larger systems (think data warehouse sized systems) I'll put the system databases on a seperate drive for performance purposes.

If I had to rebuild a server that had the system databases on the same drive as the user databases I'd fix the array, then shutdown a server on the same patch level and copy over master, model and msdb to the busted server. Placing them in the correct folders (I standardize my folder locations and drive letters). Fire up SQL on both boxes ignorring the errors and suspect databases on the busted server. Now restore all databases and your back up and running.

Moving master is more than an ALTER database command. There is a procedure in the MSKB. If you search for "SQL master move database" it should popup in the list.

I've got a FAQ out there on disk setups faq962-5747.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[timscronin]

I would always have Master on separate array. If you log ship can make some failover scenarios easier