The basic form of storage is a single physical disk, be it a hard disk drive, or more recently, a solid state drive. One single unit can provide storage for the Operating System, Applications, SQL Server, etc. While you can certainly run SQL Server on a single physical disk and you may find it cost effective, you would run into the following disadvantages:
- Reliability – a single point of failure, no redundancy, no backup
- Speed – limited to speed of a single disk, a single I/O queue (even with multiple logical partitions)
If SQL Server is going to host valuable data, the data you care about, you want to make sure there is some redundancy and some level of backup in case things go wrong. You also want to utilize multiple physical disks to help with I/O performance. This is where various RAID levels come into play, so let’s quickly go through them.
First, what is RAID? It stands for Redundant Array of Independent Disks, and simply put, it’s a combination of several physical drives that can act as one or multiple virtual drives. These drives will share data providing performance and/or redundancy normally not available with a single physical drive.
The RAID Levels:
This is the most basic form of RAID and the way it works is you group multiple physical drives (at least 2) into a single volume (can split into multiple if necessary) and the data is split across all drives evenly.
The greatest benefit of RAID 0 is speed. The more drives you have, the faster I/O throughput you’ll get when reading/writing files.
The biggest drawback is there’s absolutely no redundancy. If any drive fails, the data will become corrupt.
You do not want to put your SQL data files on this kind of volume, with the exception of perhaps TempDB files, which can provide a serious performance increase if your server sees a lot of TempDB I/O contention.
Like RAID 0, this is also a very basic setup requiring at least 2 drives (or multiples of 2) to work. Instead of splitting data however, it mirrors the data. So if you have 2 drives, data is written to one drive but also to the other simultaneously.
The performance is usually equivalent to that of a single drive, but it provides at least some redundancy. If one disk fails, it won’t affect the data as it is already copied. The RAID will remain in degraded mode until the failed disk has been replaced.
If there are cost limitations preventing you from acquiring a better RAID setup, this would be the one to choose, if you need redundancy more than speed. Otherwise, keep on reading.
We’re going to skip levels 2, 3 and 4 as they’re not exactly as popular as the next 2.
This is the most common type of RAID level, usually setup for most servers, often including SQL servers as well.
It requires that you have at least 3 physical drives. RAID 5 works in such a way that similar to RAID 0, it will split data across multiple disks. In addition to that, it will also put Parity data on all disks as well. Without getting too technical, Parity is simply a blueprint for data. It describes data, without being data itself. The reason this is important is because even when a single drive fails inside of a RAID 5 setup, other drives can be used to recalculate the missing data with the help of remaining data and parity bits on the other drives.
Striping performance goes down when you have a failure, but there’s at least some redundancy along with an increase in performance compared to RAID 0 or RAID 1. A failed drive should be quickly identified and replaced as soon as possible.
This would be a satisfactory model for SQL Server, but we can do a little bit better with our next example.
This RAID level is probably the most favorable level to have for the SQL Server. It combines RAID 1 and RAID 0 (in that order, order is important) to provide the best of both, performance and redundancy.
You need to have at least 4 drives to setup RAID 10, and it works in the following way:
Disks 0 and 1 are mirror of each other, and Disks 2 and 3 are mirrors of each other as well. Then, data is striped (or split) across the two mirrors. This means that you can have multiple points of failure, as long as an entire mirror doesn’t go down. For example, if Disks 2 and 3 go down, that would cause data loss.
However, if Disks 1 and 3 go down, or Disks 0 and 2, the array will continue to function until the disks are replaced without data loss.
Due to the performance and redundancy benefits, this is the most favorable setup for SQL Server.
In the next installment, we’ll discuss how to properly choose cache size, cluster size, and formatting the volumes.