Sql-server – SQL Server: One 12-drive RAID-10 array or 2 arrays of 8-drives and 4-drives

Knowing how to setup your storage is all about measuring and budgeting the IOPS and bandwidth. (I'm being simplistic here because the size of the percentage mix of read/write, average IOs, RAID stripe size, and cache hit percentages matter greatly. If you can get those numbers you can make your calculations even more accurate.)

There's a really nice little IO calculator here that I frequently use when planning out storage. wmarow's storage directory is also nice for getting some fairly contemporary disk performance numbers.

If I dedicate a pair spindles to a particular task, such as transaction logs, and they're not even breaking a sweat with the workload...why not just put that workload onto a larger RAID 10?

Remember that putting sequential IO onto a spindle with random IO makes that sequential IO random. Your transaction log disks may look like they're not breaking a sweat because you're seeing sequential IO operations. Sequential reads and writes to a RAID-1 volume will be quite fast, so if you're basing "not breaking a sweat" on disk queue length, for example, you're not getting the whole story.

Measure or calculate the maximum possible random IOPS for the intended destination volume, take a baseline of the current workload on that volume, and then decide if you have enough headroom to put those transaction log IOPS into the remaining random IO in the destination volume. Also, be sure to budget the space necessary for the workload (obviously). If you're so inclined, build in a percentage of additional "headroom" in your IO workload / space allocation.

Continue with this methodology for all of the other workloads that you want to put into the destination RAID-10 volume. If you run out of random IOPS then you're piling too much into the volume-- add more disks or put some of the workload on dedicated volumes. If you run out of space, add more disks.

What is the downtime tolerance of the array? Is it physically close, or in a remote data center? Bascially, if you can tolerate it, a cold spare allows you to do RAID10. The spare is sitting close by, but you have to physically do the swap. If that is not an acceptable scenario, then RAID5 with a hot spare is the only answer left.

Since you already have two RAID1 sets that have 1 drive failure tolerance, you really gain nothing by going RAID10 with no hot spare. Your entire array can still only survive a single drive failure.