What is the best way to configure 6 disk raid for SQL Server + OS?
I'm setting up a SQL Server on Win 2k8 with following hardware:
- 6 x WD RE3 500G SATA
- PERC H700
I've read some suggested that putting log file and OS on its own RAID1 while the rest of data is on RAID10 would be the best config for 8 disks. However I'm short of 2 disks and wondering which config would be just as good.
Option 1) 2 x RAID1 for OS and Logs, 4 x RAID10 for Data
Option 2) 6 x RAID10 for OS, Logs and Data
P.S. Does partitioning the disk in RAID10 makes any difference?
Solution 1:
The general point here is that having two sets of files on different physical disks (or RAID volumes) makes things much faster when both sets are accessed at the same time; this is why the O.S. usually gets its own volume (it does quite some I/O on its own, and you don't want it to slow down when doing huge work on data), and it's also why you usually want to split your data based on their usage patterns.
That said, an even more important point with SQL Server is that not only are transaction logs usually accessed at the same time as data files, thus making separating the two a good choice, but also you'll need at least one of them if you want to be able to truly recover things in the case of a hardware failure. If you lose the transaction logs, you can just create another set, but your actual data are safe... and if you lose your data, transaction logs will let you recover from the last backup up to the point of the failure. If you lose both data and logs at the same time, you can only restore your last backup, nothing more.
The best choice in your scenario would be creating three RAID 1 volumes, one for the O.S., one for the database files, and one for the transaction logs. I know this looks like a big waste of space (the O.S. is nowhere to use 500 GB, and so the transaction logs if you follow a proper backup plan), but it will help a lot when it comes to performance and recoverability. And, if you aren't going to need more than 500 GB of actual data, it will work greatly for you.
If you can't do that, then use a RAID 1 volume for the O.S. and logs and one RAID 10 volume for the data. This is the best you can do with the hardware you have. But, whatever you do, you really shouldn't put the data and the logs in the same place.
About multiple partitions: that is totally useless, and can even worsen things. Three partitions in the same disk (or RAID) perform exactly the same as a single partition (= badly), or even worse due to disk geometry issues; and it doesn't help with recover, because in the case of a hardware failure you're going to lose all of them anyway.
Oh, and for your own sake, don't even think of using RAID 5, if you care at all about write performance.
Solution 2:
I would use Option 1 personally if you cannot add 2 additional disks for a Raid 1 Log disk.
Partitioning for RAID 10 gives you your mirror and striping for speed and redundancy.
This question was asked and answered in StackOverflow as well. Perhaps it will have the answers your are looking for.