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 Teamcenter on SQL Server. RAID Level 1SQL 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. RAID Level 5SQL 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. Read more: Understanding RAID for SQL Server Part 1, Part 2
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. RAID Level 5SQL 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. Read more: Understanding RAID for SQL Server Part 1, Part 2