RAID for SQL Server: Advantages and Disadvantages

What would be the best RAID configuration for SQL Server databases? It is always being asked and discussed in my classes, so thought to put what we discussed (explained) as a post:

What is RAID?
RAID stands for Redundant Array of Independent Disks. It is a technology that can be used for increase (high) data availability and increase IO performance and it was introduced by David Patterson, Garth Gibson, and Randy Katz at the University of California, Berkeley in 1987. It was formerly known as Redundant Array of Inexpensive Disks. There are different types of architectures for RAID, which are named with the word RAID suffixed by a number, such as RAID 0, RAID 1, and RAID 5.

What is different between Hardware RAID and Software RAID?
Hardware RAID is implemented with set of physical storage devises and RAID controller cards. It is expensive than Software RAID. Software RAID IS implemented with system resources and managed by the OS. It is less expensive than Hardware RAID but requires more CPU power and memory. Software RAID does not support (or merely support) fault-tolerance but Hardware RAID.

RAID 0 with SQL Server
RAID 0 is known as Disk Striping and uses set of configured disks called as Stripe Set. This can be configured with minimally two disks and set as an array. It provides good read and write performance because data is distributed across multiple disks. This does not support fault-tolerance, hence if one disk is failed, the entire Stripe Set fails. Although it does not provide fault-tolerance, it can be used with SQL Server, mainly for data files, to speed up read and write performance. In addition to that, it uses all the space available, and worth for what you have spent on it.

Note: All RAID configured SQL Server systems I have seen are NOT RAID 0. Most go for other RAID configuration when it comes to SQL Server, though the cost and performance are key factors for deciding the RAID.

RAID 1 with SQL Server
RAID 1 is known as Disk Mirroring and uses two disks configured as a Mirror Set. Data is written (duplicated) to both disks, hence supports fault tolerance. If one fail, recovery is easier, and because of a full duplicate disk, operation can be continued until the failed one is replaced. It does not give good performance on data writes because data must be written for two disks. Data read performance is higher than a single disk because reading can be split over two disks.

Biggest disadvantage of RAID 1 is, losing 50% of disk space. If your requirement is 100 GB, you have purchase 200GB for setting up RAID 1. You have to double the required amount of space but gain no additional storage space.

RAID 1 is a good choice for Operating System of SQL Server system and SQL Server log files. Since RAID 1 can continue with a failed disk and replacement is faster than any other RAID configuration, it is recommended to have the OS in RAID 1. SQL Server Log files are mostly written to, rarely read from, if there is a transaction to rollback. Although RAID one takes some additional resources/time for writing, dedicated RAID 1 for each log file is recommended. One reason could be the way it fills the log file, it writes sequentially. Other reason is fault-tolerance. Considering all these reasons, best choice for log files is RAID 1.

RAID 5 with SQL Server
This is know as Striping with Parity and configured as a Stripe Set. This is similar to RAID 0 which write data across multiple disk. This requires at least three disks. Not like RAID 0, RAID 5 support fault-tolerance by striping data across multiple disks and storing parity information as data is written. Since parity information is available, in case of a disk failure, parity information can be used for re-creating the data lost on failed disk, allowing us to continue the operation. Note that in case of multi-disks failure, parity will not be available for re-creating all data lost, hence entire Stripe Set will fail.

You noted that RAID 1 gives good read performance. RAID 5 gives good read performance too, but better than RAID 1. This is because reads are performed across multiple disks. More disks in array gain more performance on reads. But write performance in RAID 5 is poor. This is because of more IO operations and parity calculation. In terms of storage space, again, RAID 5 is ahead. RAID 5 with three disks used, uses one-third of total disk space for holding parity information, hence the overhead is around 33%. This overhead decreases as number of disks are increased.

RAID 5 is usually used for holding history data which frequently read, and rarely write. When a table is partitioned, old data can be placed on a file group which files are configured with RAID 5. So, can we use RAID 5 for SQL Server data files? Yes you can, useful rule of thumb is, if your database has 90% read operations and 10% write operations, your choice would be RAID 5.

RAID 0+1 with SQL Server
RAID 0+1 is a combination of disk striping and mirroring. It is a two Stripe Sets mirrored, or two duplicates of Stripe Sets (do not confuse with 1+0, which is a Stripe Sets configured with 2 or more mirrored sets). This gives best performance of Stripe Sets while providing fault-tolerance. Data read is faster, just like RAID 0 or 5, and read can be split over mirrored set too. Data write requires two IO operations because of duplicate set. Overall this provides best performance while supporting fault-tolerance. This has an ability to continue the operation even after multiple disks failure in one Stripe Set. Failure of disk from both side of mirror will be a failure of entire RAID.

As stated in RAID 5, it is better for more read operations. But RAID 0+1 offers performance on both read and write. The biggest disadvantage of RAID 0+1 is the number of disk required. This RAID configuration minimally requires 4 disks.

Generally, we use RAID 0, 1, 5 or 0+1 for SQL Server. Key factors to be considered for selecting the architecture are cost, amount of each type of operations (reading and writing) and whether fault-tolerance is required or not.