SQL Server, any advantage to dual RAID controllers?

Question

We're setting up a new Hyper-V server, primarily to host a SQL Server VM, and the question came up, how much advantage is there if we used two RAID controllers instead of one? For the responsiveness of SQL Server, that is.

Physical drive setup would be:

• Data drive RAID 10
• Log drive RAID 1
• TempDB drive RAID 1
• OS/App drive
• Drives for other VMs

This would be a Hyper-V box, with a Sql Server VM. Sql Server VM would be the only one with a VHDX on those Data/Log/TempDB drives.

Would there be any advantage to spreading them out over two RAID controllers as opposed to one? How much? And if so, how would you divide Data, Log, and TempDB drives between the two controllers?

Answer 1

I would not spend on dual controller; rather, if low service time are required, be sure to invest in enterprise-grade SSD:

• for maximum performance, you can/should use an Optane NVMe drive;
• if an Optane is too much, you can use an Intel P4610 (or Samsung equivalent);
• if budged is constrained, go with SATA drives as Intel S4610 or Samsung SM863a.

Finally, I often found Hyper-V much slower in disk I/O than VMWare or KVM. I suggest you to try your workload with other hypervisors, if possible, before committing to Hyper-V.

Answer 2

A different reason for using dual controllers is high availability. Distinct hardware paths to disks. I don't know whether your system supports it, tends to be an expensive RAS feature.

Many workloads can be handled by the performance of a handful of SSDs on a single controller. However, the only way to be sure is to test. Estimate IOPS requirements, multiply that by a safety factor, and pummel your disks to that level with something like DiskSpd.