Mfcttrf

I'm considering using a RAID0 setup for one of our SQL Server clusters. I'll outline the situation and am looking for why this may be a bad idea. Also if someone you have use cases, white papers or other documentation you can point me to on this topic, that would be great.

We have 3 servers in 2 datacenters that are part of an SQL cluster. They are all running SQL Server in an Availability Group. The primary has a replica sitting right next to it and another in the other datacenter. They are running synchronous replication with automatic failover. All drives are enterprise class SSD's. They will be running SQL Server 2017 or 2019.

I'm thinking that there would be multiple benefits to running them on RAID0 arrays over other methods with few, if any, real drawbacks. The only negative I'm currently seeing is lack of redundancy on the primary server, so it failing increases. As pros:

1. If a drive fails, rather than run in a slowed, degraded state until someone receives a notice an manually acts on it, the server will immediately fail to a secondary maintaining full operational capability. This will have an added benefit of notifying us of a failover, so we can investigate the cause sooner.




2. It reduces the chance of failure overall per TB capacity. Since we don't need parity or mirror drives, we reduce the number of drives per array. With fewer drives there is less total chance of a drive failure.




3. It is cheaper. Needing fewer drives for our required capacity obviously costs less.

I know this isn't the conventional business thinking, but is there something I'm not considering? I'd love any input either pro or con.

Drive failure should be taken into consideration here.

Imagine for a second that our drives on any particular day have a 1/1000 failure rate. Imagine then that we have 20 drives in each of our 3 arrays.

The chance of a single drive failing in an array is therefore 20/1000 = 1/50. The chance of two drives failing within the same array is something close to 20/1000 * 20/1000 = 400/1000000 = 1/2500. So by switching from RAID 0 to RAID 5 we're already significantly less likely to kill one of our arrays.

So we can take this further - if the chance of an array failing on a day is 1/50, then the chance of two arrays failing in a day is 1/(50*50) = 1/2500. The chance of two identical RAID 0 arrays failing is the same chance as one RAID 5 array failing, assuming the same disk set. This exponential increase in the chances of failure should concern you, as it massively increases the chance that more than one array fails at once.

As these disks are likely to have a long life time, you can likely run the numbers as above and directly see what effect this will have on reliability - if you can post the drive specifications I can add that calculation to this post. Whether the risk is then acceptable or not is for your organisation to decide.

Disclaimer: The above calculations have been simplified - they are still relatively accurate.

I'm thinking that there would be multiple benefits to running them on RAID0 arrays over other methods with few, if any, real drawbacks.

This is a pretty common configuration when running AGs with internal / direct-attached storage drives. Especially with NVMe or other PCI-based flash storage divices.

It simply amounts to treating a drive failure like a server failure. With solid state drives you don't really have a significantly lower MTBF for the drive than you do for the other solid-state components of the server, and so you simply treat the drives as a point-of-failure for the server, and replace/rebuild the server in case of a drive failure.