SQL Server Physical Location Master

[phrilly]

Hi,

We have just got a new machine and intend to install SQL Server 2000 on it. We have got 3 physical mirrored pairs withthe intention of placing operating system on pair 1, db on pair 2 and log on pair 3. My question is where is the best place to put the master, msdb, model databases ? They are currently on pair 2. Any help greatly appreciated.

Best wishes

Phil

 

[sguslan]

It really will not matter as query activities against these databases is usually low. But what i would do is to create a RAID 10(some people prefer to call it RAID 01) which is essentially RAID 0 + RAID 1. It creates a striped set of 3 drives with the other 3 as mirror. Just make sure the RAID controller supports it....

 

[phrilly]

I don't think that your Raid 10 idea is appropriate for us as we do very little writing to the database, mainly reads and raid 1 I understand is more appropriate for that (in particular for the transaction log sequential writes).

What I wondered was whether there was any better resilience as a consequence of having the master separate from the data, or indeed having the tran log of the master separate from the master ? I can't think of any reason myself but always on the scrounge for ideas.

 

[sguslan]

Definitely having Master DB on a separate drive with data and log on their own drives would greatly help improve server resilience and performance...
What i always do is dedicate a RAID 0 (three drives) for OS and SQL server installation. Another RAID 0 with 3-5 drives for logs; and yet another RAID 0 with more drives for data depending on the size of data and performance requirements. But this scenario is usually used for installations with higher resilience and performance requirements.
For larger installations with critical data with extreme uptime requirements i recommend RAID 0+1 with as many drives as can be afforded. I am preparing a 2 TB SQL cluster with a Compaq SAN box with RAID 10 that has around 60 drives with dual controllers and dual fiber links into the SAN switch.

 

[tlbroadbent]

I recommend reading the SQL Server 2000 Operations Guide. It available online and can be downloaded. Chapter six of the guide is about capacity planning and has information about "Disk Planning," "Data Storage Subsystem Management," and "Database File Placement," amoung othr sub-topics.

SQL Server 2000 Operations Guide

http://www.microsoft.com/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp

Chapter 6 - Capacity and Storage Management

http://www.microsoft.com/technet/prodtechnol/sql/maintain/operate/opsguide/sqlops6.asp

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.