October 20, 2005 at 2:39 pm
Currently we are using sql2000 [standard]
with system database on local C drive and
user db and backups on RAID-5.
Now we have 2 new servers for installing
Sql Server2005.
One will be used for Database Mirroring.
It will be Active/Passive arrangement.
with one additional witness as well.
We are planning RAID-5 setup.
I have few question related to that
1. Where to place System databases, on local
drive C or Raid -5[D].
2. Should we keep Data and Log for all database
on Same Raid -5[D]
3. Should Database and Backups to all db be on
same Raid -5[D]
I see recommendation of data and log files on separate disk ,plus database and backup on separate drives, for security
against catastrophic failure as well as performance .
We are set to go live with
Production database by end of next month.
I will highly appreciate time and suggestion on this from any one of you,
If Steve Jones can share some thoughts that will be great.
Thanks,
Sameer Raval
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
October 20, 2005 at 3:54 pm
Here are the benchmark numbers from running the SQLIOStress utility on 2 servers where one is RAID-5 and the other is RAID-1.
The results are seperated by !
DB File Placement!Data and Log on Same Drive!Data and Log on Same Drive!Data and Log on Different Drives
Raid!5!1!1
Average Read MB per second!0.86!2.39!6.71
Average Writes per second!104.23!287.47!800.21
Total Elapsed Time!1540.8!556.2!198
Note that RAID-5 with log and data on the same drives has about 1/8 of the thruput of RAID-1 with log and data on different drives.
This is why I strongly recommend RAID-1 and do not recommend RAID-5 under any circumstances.
If your server only have room for 6 drives, such as a HP DL380, here are my recommendation:
On Drive 0, create a C: partitions of about 8Gb for the swap file and software (Windows and SQL Server) and then use the remaing space to create a D: partition for the tempdb datafile and log.
On Drive 1, create one partition for the datafile (*.mdf) with an NTFS cluster size of 64K
On Drive 2, create one partition for the transaction log (*.ldf) with an NTFS cluster size of 64K
The reason for seperate partitions on Drive 0 are:
1) As the performance of a disk is affected by fragementation, one way to reduce fragementation is to isolate static files, such as the Software, from dynamic files, such as tempdb. This way, you can defrag C: once and be done until you next modify the software files via a service pack.
2) For partitions housing SQL Server datafiles, the NTFS cluster size should be 64K but for file system, 4K is the typical choice.
You may get some resistance to placing transaction logs on a dedicated drive. You can get the actual reads and writes for all database files by running
SELECT * FROM :: fn_virtualfilestats(default,default)
Regarding backups, having the database files and the backups on the same drive will gain you nothing for recovery. If you loose the drive array, your backups are gone. If you lose the server, you will need to move the drives to a different server before you can begin recovery. Often there is a file server that has little usage during off-peak hours (such as the server for the staff's home drive). To this file server, put the database backups.
SQL = Scarcely Qualifies as a Language
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply