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?
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.
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.
You're right that 8 disks would be better in this situation but the most important thing to consider is that you want the logs on a different disk than the data. If you're really stuck with only 6 and you need the data space of 4 disks then I'd be tempted to put the logs on a second partition on the OS R1 pair. This isn't ideal as the heads will have to move a lot when both logging and doing OS work but if you have plenty of memory then the OS work should be quite low. If you don't need much disk space then how about a 2+2+2 setup?
Personally I'm of the opinion that you should separate OS operations from log operations from database operations as each has it's own unique, non-complementary disk I\O patterns. The databases and logs should never be kept on the same array except in very small implementations. RAID10 is a bit overkill for the databases unless your databases\applications are very heavily disk I\O intensive, IMHO. I might suggest:
RAID1 = OS RAID1 = Logs RAID1 = Databases
In most of my common setups, I tend to have a RAID1 for the OS; a RAID0 for temporary, non-essential stuff; and various RAIDs for data.
If the client wants maximum disk space, that last set will be 1 x RAID5; if they are more concerned with performance, the latter will be 1 x RAID10; and if they are more concerned with redundancy, I'd go 2 x RAID1.
Often I have a second controller for the data volume; sometimes altering the priority of read to write on both, depending on the database's usage profile (we tend to favour the OS, Temp and Logs on Controller 1, with 25% read:75% write and the Data on Controller 2, with 75% read:25% write -- such is the nature of the apps we sell)
In your case, assuming the PERC can do it (I tend to favour the HP SmartArrays), I'd probably go with the following:
That should give you (approx):
In my opinion, though, your performance concerns are probably going to be with the SATA disks. I've never managed to get them to run anywhere near well enough to be stable, so I always choose at least 10K SAS drives. Maybe they've got a lot better since I last used them, so your mileage may vary...