Skip to content
Home » T-SQL Tuesday Retrospective #004: I/O

T-SQL Tuesday Retrospective #004: I/O

  • by

Mike Walsh invited us on March 1st 2010 to write about I/O. This abbreviation stands for Input / Output, and is often used as shorthand for persisted storage.

Given the breadth of the topic I decided to write about RAID levels, but I must state outright that RAID is not a backup. A Redundant Array of Independent Disks is meant to provide redundancy for your storage subsystem, meaning that if a drive were to fail the system would recover gracefully for long enough to replace the failed drive.

In practice, this is not as reliable. Many RAID levels do not provide the expected redundancy, especially if more than one drive fails at the same time, or a second drive fails before recovery is complete. With large storage capacities recovery can take longer than you might expect, increasing the window of opportunity for multiple failures.

So, it is vital — always — to have a Plan B, where B stands for Backup. Make sure you back up your SQL Server databases — and test those backups — even before planning a RAID level.

Storage devices — most commonly spinning hard drives and various forms of solid-state drives (Including NVMe and persistent memory) — have a limited lifespan. This is measured in hours using a term called MTTF, or Mean Time To Failure. Considering there are 8,760 hours a year, and most server-grade hardware is expected to run 24 hours a day, a MTTF of, say, 40,000 hours means that a drive has an average lifespan of 4 ½ years. If you only use your machine between 8am and 5pm on weekdays, your lifespan for the same MTTF is increased dramatically, assuming nothing else goes wrong inside (or outside) the computer.

When purchasing drives for a RAID array, it is recommended best practice not to buy all the drives from the same factory batch, as that spreads the risk should a drive fail. If they all come from the same batch then you might find yourself having to replace multiple drives at the same time, and as I noted previously, this can end badly.

Note that in all cases except Level 0, all drives in an array must be the same capacity.

Level 0

This level requires at least two drives, and is also known as striping. Data is written in blocks across several drives, which increases the performance of reads and writes compared to a single drive. If one drive fails, the chance of catastrophic (total) data loss is high because file segments are likely to be distributed across the drives. Available storage capacity is n, where n is the number of drives.

Suitability for SQL Server: Not recommended.

Level 1

This level requires two drives, and is also known as mirroring. Data is written to both “sides” of the RAID array using blocks. This level is expensive because you need double the storage capacity. This level of RAID allows for one drive to be lost without incurring catastrophic data loss. Available storage is n / 2, where n is the number of drives.

Suitability for SQL Server: Recommended, but pricey.

Level 2 and 3

Rarely used. RAID 2 works at the bit level with a bit-correction algorithm, but offers little benefit over the parity block available in higher levels. RAID 3 works at the byte level, with a dedicated parity drive. It also requires synchronizing the spindles of each drive, which offers little benefit over higher levels.

Suitability for SQL Server: Not recommended.

Level 4

This level requires at least three drives. RAID 4 stripes data across multiple drives like RAID 0, but with the addition of a dedicated parity drive which adds error detection. Should a drive fail, the missing data can be recalculated. There is a performance bottleneck with writes occurring on the parity drive, so it is not commonly used.

Suitability for SQL Server: Not recommended.

Level 5

Like Level 4, RAID 5 stripes data across the disks with a parity bit. The main difference is that the parity is also spread across drives to improve write performance. One drive can be lost before catastrophic data loss occurs. Available storage capacity is n – 1, where n is the number of drives.

Suitability for SQL Server: Recommended for data files, not recommended for transaction log files or tempdb.

Level 6

Like Level 5, RAID 6 also uses a distributed parity block, but adds a second one as well. This means that two drives can be lost before catastrophic data loss occurs. Minimum drives needed: 4 (Two data and two parity), with available storage capacity being n – 2, where n is the number of drives.

Suitability for SQL Server: Recommended for data files, not recommended for transaction log files or tempdb.

Level 10 (1+0, or 0+1)

Striping plus mirroring, or mirroring plus striping, RAID 10 combines the performance characteristics of Level 0 with the redundancy of Level 1. One side of the array is striped across multiple drives, and then duplicated on the other side using a mirror. This is expensive, but offers the best performance. One drive from each side of the array can be lost without incurring a catastrophic failure. Available storage capacity is n / 2, where n is the number of drives.

Suitability for SQL Server: Recommended, especially for transaction log files and tempdb.

Share your thoughts in the comments below.