Running SQL Server on RAID

I'm often asked about using a redundant array of inexpensive disks (RAID) for fault tolerance. Should SQL Server be installed on a RAID device? The answer is yes, if you can afford it. RAID is the easiest and best way to implement fault tolerance, and you definitely want your production databases housed in a fault-tolerant environment. RAID server systems are more expensive than ordinary single-disk servers because of the additional hardware and software you use to implement the RAID configurations. Several types of RAID are commonly available, and each has its own specific use.

RAID 0 uses striping, a method that creates a disk partition that spans multiple hard drives to take advantage of the many operational read/write heads associated with multiple spindles (similar in concept to Windows NT striping). RAID 0 is the fastest type of RAID, but unlike most RAID implementations, it doesn't provide fault tolerance. If one of the drives in a RAID 0 configuration fails, all the data is lost. Don't use RAID 0 if you value your data.

RAID 5 is the most common way to implement fault-tolerance. RAID 5's read data transaction rate is high, and its write data transaction rate is also good compared to other configurations. RAID 5 also offers a good aggregate transfer rate. A typical RAID 5 configuration contains three or more hard drives. RAID 5 divides up the data and writes it in chunks spread across all the disks in the array. Redundancy is provided by the addition of data parity information, which the RAID controller calculates from the data, and which is written across all the disks in the array, interleaved with the data. The parity information enables the RAID controller to reconstruct data if one of the disks and the data stored on it is lost or corrupted. RAID 5 is the most cost-effective way to implement fault-tolerance. Store SQL Server system and user data files on a RAID 5 device.

RAID 1 uses mirroring, a method in which each drive has a mirror copy on another drive. Mirroring is the most fault-tolerant RAID scheme, but it's also the most expensive because of the additional hardware and software you need to support mirroring. SQL Server stores data sequentially on the transaction logs and in TempDB, which makes these essential parts of your database well suited for RAID 1 protection. Put the transaction log and TempDB on a RAID 1 device at least, even if you can't afford RAID for any other parts of your database.

RAID 10 is a combination of RAID 1 and RAID 0 that uses mirroring and striping. It's expensive, but it's fast and provides the best redundancy and performance. RAID 10 involves mirroring two or more RAID 0 drives. If you can afford it, put the transaction log and TempDB on a RAID 10 device rather than a RAID 1 device for the extra protection that it offers. For a good comparison of RAID types, see Advanced Computer and Network Corporation's RAID tutorial.