I'm setting up a SQL Server on Win 2k8 with following hardware:

  1. 6 x WD RE3 500G SATA
  2. 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?

  • 2
    What are you going to be using the server for? Your indented usage pattern matters. If you're not going to have a high transaction volume and are more concerned about having space (particularly if you're going to be functioning in a mostly-read capacity) then "option 2" might actually make sense. – Evan Anderson Jul 29 '10 at 17:06
  • I have been wondering about this exact thing but with 8 spindles. I would love to see actual numbers that having the transaction logs on a different spindle does beat have a raid 10 array that is an extra 2 disks larger. This is of course only thinking about speed.... – Kyle Brandt Jul 29 '10 at 17:25
  • @Kyle: 'twould be fun to simulate, eh? There are definitely a lot of moving parts (RAID controller cache, OS cache, SQL Server). You've got the user activity data to actually simulate the real world. If you've got enough spare hardware to spin up a test universe you can see. >smile< – Evan Anderson Jul 29 '10 at 17:46
  • 1
    @Kyle, my concern about having data and logs in the same array/disk/partition is more to do with recoverability (data but no logs=good, backup + logs=good, no data + no logs=bad) – Chopper3 Jul 29 '10 at 18:37
  • 3
    @Chopper3: That depends on your recovery model, though. For OLTP applications I'd agree with you, but there are other applications where you may not care at all about point-in-time restore (like, say, if you entire database is an ETL'd view of some other OLTP database) and you're more interested in maximizing your dollar for space. – Evan Anderson Jul 29 '10 at 22:25

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.

| improve this answer | |
  • 3
    Three partitions on the same disk are likely to perform worse than a single partition, particularly if the disk is nowhere near full, as the use of multiple partitions will increase the distance the heads have to travel between cylinders* that contain requested data. (* I fully acknowledge that any reference to "cylinders" on a hard disk may be an indication that I am getting old.) – Skyhawk Jul 29 '10 at 18:23
  • +1 for explaining the importance of separating logs from data. – Skyhawk Jul 29 '10 at 18:26

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.

| improve this answer | |
  • 2
    Option 1. No question asked. The RAID 1 for OS+Logs is good - OS does not a lot of IO normally. The other gives you perfect speed for the data. – TomTom Jul 29 '10 at 17:55

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?

| improve this answer | |

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

| improve this answer | |

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:

  1. Create array A with 2 disks
  2. Create array B with 2 disks
  3. Create array C with 2 disks
  4. Create volume A1 on array A as RAID1 with approx 20-25% total size
  5. Create volume A2 on array A as RAID0 with the remainder
  6. Create volume B1 on array B as RAID1 with all available space
  7. Create volume C1 on array C as RAID1 with all available space

That should give you (approx):

  • C:\ RAID1-OS - 90GB (should be ample for the OS, SQL Server and a decent pagefile)
  • D:\ RAID0-TEMP - 790GB (Ideal for backup staging, temp exports, etc)
  • E:\ RAID1-DATA - 480GB (MDF files)
  • F:\ RAID1-LOGS - 480GB (LDF files)

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...

| improve this answer | |
  • 2
    I think your #4 & #5 are off, I don't know of a way you can make a RAID 0 and a RAID 1 on two disks. The best you can do there is make one RAID1 with multiple partitions. I'd also generally disagree with a RAID 0 anywhere in a production environment, all you need to do is lose a disk, and you've lost the array. – Edinor Jul 31 '10 at 1:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.