This is a continuation of our series on designing a SQL Server file subsystem. Our post on March 30 discussed software RAID (redundant array of independent disks) and RAID level 0. Today, we discuss the RAID levels that provide data redundancy—the ones that you really care about if you are smart about running ENOVIA V6 on SQL Server.
SQL Server 2008 Books Online says, "This level is also known as disk mirroring because it uses a disk file system called a mirror set. Disk mirroring provides a redundant, identical copy of a selected disk. All data written to the primary disk is written to the mirror disk. RAID 1 provides fault tolerance and generally improves read performance but may degrade write performance" ("RAID Levels and SQL Server," SQL Server 2008 Books Online, MSDN).
Level 1 is one of our favorite ways to set up SQL Server. It is fast and provides data protection like a superhero straddling the speed and safety worlds. All superheroes have weaknesses, and RAID level 1 is no exception—it uses two hard disks of identical sizes, and this can cause several drawbacks. The first drawback is that the size of your logical disk is the same as the size of one of the physical disks. In other words, you pay for two disks, and you only get to use the storage size of one.
The other, more important, drawback is that you can only store files of up to the size of one of the disks on the logical disk. For example, if you have two 185 gigabyte (GB) drives in the RAID level 1 configuration, you end up with a single logical drive of 185 GB. In this example, the maximum file you could hold on the disk would be less than 185 GB in size. Since your largest database files are the .mdf file (which holds the data) and the .ldf file (which holds the transaction logs), you need to make sure that they do not exceed the size of the level 1 logical drive.
One way to prevent the data from exceeding the size of the level 1 drive is to create a secondary database file (.ndf) of an equal size on another level 1 logical drive set. This divides the data in the database between the two files.
You can keep your transaction log file (.trn) from getting too big by frequently backing up the transaction log, which allows it to be stored on a RAID level 1 array.
As we continue this series of posts about the disk subsystem, we will talk more about which database files go on which kinds of logical drives and about the arrangement of physical disks.
SQL Server 2008 Books Online says that level 5 is also known as striping with parity. "Data redundancy is provided by the parity information. The data and parity information are arranged on the disk array so that the two types of information are always on different disks" ("RAID Levels and SQL Server," SQL Server 2008 Books Online, MSDN).
A RAID level 5 configuration lets you have more than two physical drives in the RAID configuration. In fact, there is really no benefit until you have there or more drives. The data is written to all three drives at the same time. However, the data is read from any of the drives without involving the other physical drives in the array.
The amount of storage on the logical drives is equal to the sum of all the physical disk storage minus one disk. For example, if you have three 185 GB drives, you have 370 GB of logical storage. With four 185 GB disks, you have 555 GB of logical storage. This lets you design any size logical drive to fit even the biggest database file.
The issue with RAID level 5 is that the more disks you add, the higher the costs of writing data. One poor approach that we have seen is to take all the physical disks on the server and lump them into one big drive by using RAID level 5. The thought behind this technique is that the IT administrator and DBA would never have to worry about individual drives running out of room─the server either has room or it doesn't. The IT administrator and DBA would not worry about disproportionate file growth across separate arrays or about having to move and manage files. The reason this technique is a bad idea is that each write to the RAID has to be committed on all eight disks—no matter what the write (i.e., database data files, transaction logs, Windows virtual file). For this reason, we are not fans of RAID level 5 arrays over a large number of disks on a computer that is running SQL Server.
SQL Server 2008 Books Online says, "This level is also known as mirroring with striping. RAID 10 uses a striped array of disks that are then mirrored to another identical set of striped disks. For example, a striped array can be created by using five disks. The striped array of disks is then mirrored using another set of five striped disks. RAID 10 provides the performance benefits of disk striping with the disk redundancy of mirroring. RAID 10 provides the highest read-and-write performance of any one of the other RAID levels, but at the expense of using two times as many disks" ("RAID Levels and SQL Server," SQL Server 2008 Books Online, MSDN).
RAID level 10 is really the best of both worlds—fast write times and infinitely large logical drives. The extent of your ability to deploy RAID level 10 is dependent on the drives that you have. The minimum number of drives is four for RAID level 10. In some cases (which we will discuss in a later post), having two sets of RAID level 1 drives will perform better than dedicating all four of those drives to a single RAID level 10 array.
For RAID level 1, you need at least two physical drives; for RAID level 5, you need at least three drives; and for RAID level 10, you need at least four drives. So, the RAID level that is optimal for your SQL Server file subsystem depends on the number of drives that you have available and the data that you are trying to store. The next post in this series will be about how the count of physical disks affects your configuration of SQL Server and your RAID choices.