In my posts about RAID (part 1 and part 2), I didn't cover RAID 1 + 0 because RAID 1 + 0 isn't a good combination for SQL Server running Teamcenter. However, we have received many questions about why I didn't cover RAID 1 + 0. So, I'll explain how SQL Server can provide the 0 of RAID 1 + 0, and you just need to configure the RAID array as RAID 1 to improve performance.

To understand this last statement, you need to know a few things:

  • RAID 1 + 0 is a type of RAID configuration that combines the properties of RAID 1 with RAID 0.
  • RAID 1 is commonly called mirroring. In RAID 1, data is written to two hard drives at the same time. This provides data redundancy.
  • RAID 0 is commonly called stripping. In RAID 0, data is written to and read from only one drive at a time. This provides speed because data is divided across multiple spindles: one spindle for every drive in the RAID array. However, this doesn't provide data redundancy.

You can read more about RAID 0 in my first post and about RAID 1 in my second post. You can visualize RAID 1 + 0 like this image:

clip_image001

(Image courtesy of Microsoft TechNet)

SQL Server Provides RAID 0

SQL Server provides data stripping akin to RAID 0 without having to configure RAID. You can set up data stripping by adding additional data files to a filegroup of a database. See this post for more information about how to do this. For each data file that you add to the filegroup, SQL Server divides the data between the data files. In other words, it strips the data across all the data files. If each data file is on a different logical hard drive, the data is stripped across those drives.

Here is the really great part: If the data files are of equal size, the data block is written to a single data file. This means that to page a block of data (called an extent) from the disk, SQL Server needs to access only one hard drive. An extent is the smallest amount of data (64 KB) that SQL Server pages. Accessing one drive for one extent of data reduces I/O bottlenecks and increases performance.

Here's another way you can think about it: Because SQL Server understands its own data structures, it is the best judge of how to strip the data on the disk. Let's compare SQL Server stripping to RAID 0: Both strip data across multiple drives. However, with RAID 0, there is no "inside" knowledge of the extents, so the data for the extent could be written to multiple drives. This would mean that in order to page the extent from the hard disk to RAM, RAID 0 might have to access multiple drives. And accessing multiple drives can result in compounded seek times and a performance loss.

RAID 1 to the Rescue

If we let SQL Server strip the data to multiple drives, we still need to protect those drives from disk failure. The way to do that is to make sure that the data files are written to the logical drives of a RAID 1 array. This way, a single disk failure will not affect the stability of SQL Server. Adding RAID 1 underneath the RAID 0 stripping is very important, because with stripping, the data only exists in a single data file.

Summary

Have you ever wanted to implement RAID 1 + 0, but your hardware didn't support it? Using a hardware RAID 1 configuration to produce multiple logical drives for each data file and asking SQL Server to strip the data across each data file gives you a RAID 1 + 0-like configuration by deploying only RAID 1.

Wayne Berry (6230289B-5BEE-409e-932A-2F01FA407A92) is a computer programmer and serial entrepreneur with a passion for dynamic Web sites that run on SQL Server. When he is not blogging for MSDN, you can find him blogging for Project 31-A. http://www.31a2ba2a-b718-11dc-8314-0800200c9a66.com