SQL Server MVP Deep Dives 2, Chapter 5
I’ve really enjoyed reading all of the chapters on design, but I believe I learned more new things reading this chapter than all of the others combined. Probably because I was particularly ignorant of the subject before I started. As I’ve stated before I am not a hardware guy. Scarily enough I have been responsible for multiple servers and choosing the storage used for SQL server databases without really knowing what I was using. I stuck to typical set ups and got advice when I needed it. When that wasn’t enough I brought in someone I trusted to take over and figure it out for me. It worked out well for the most part and I knew enough to swap out a failed drive when I needed to on a RAID 5. After reading Mr. Cherry’s chapter on storage I believe I could hold my own in a conversation with the operational DBA’s and the SAN administrators. The chapter starts out with a discussion of the different RAID types.
The first thing I learned is that RAID can stand for Redundant Array of Inexpensive Disks or Redundant Array of Independent Disks. I was curious about this so I tried to look up where the names originated. It appears that the original definition was “Inexpensive Disks” when it was invented at Berkeley in the late 1980’s. Somewhere along the way some marketing folks probably changed it to “Independent Disks” so we wouldn’t get the idea it was cheap.
Then I learned what the different types of RAIDs are and what some of the advantages and disadvantages are of each one. I’m going to try to define each one in my own words so if I mess anything up here don’t blame Denny.
RAID 0 is just basic striping on the disk. There is no redundancy and the only real advantage is that you gain speed for IO by reading and writing to multiple disks.
RAID 1 is mirrored disks. All of the data is automatically copied to another disk to create the mirror. There is a decrease in performance when writing the data because of the mirror, but it is worth the trade off for having a complete copy of the data.
RAID 10 (or 1+0) is a combination of RAID 0 and RAID 1. The data in a RAID 0 array is mirrored using a RAID 1 array to another RAID 1 array. You gain all of the redundancy of a RAID 1, but get the speed of IO on separate disks. This is typically the best configuration for database files, but it can be expensive because you are buying twice the disk for the space you are using.
RAID 5 is an array of disks using parity information to create redundancy. The data and the parity information are typically striped across the disks allowing any one of the disks to fail without losing any data. RAID 5 seems to be the acceptable step down if RAID 10 is too expensive.
RAID 6 is essentially the same as RAID 5, but the parity is doubled allowing two disks within the array to fail. Better than RAID 5, but cost could be an issue here as well.
RAID 50 (or 5+0) is a combination of RAID 5 and RAID 0 striping data across multiple RAID 5 arrays using RAID 0. This provides added redundancy because a single disk can fail in each of the RAID 5’s before any data would be lost.
After defining the types of RAIDs the chapter lists when they should be used for SQL Server and what types of files work best on different RAIDs. I think the short answer is that if you can afford RAID 10 then this should work for most of the database storage. Mr. Cherry recommends that high write files like the log file and the tempdb should be on a RAID 10. The rule of thumb he throws out there is that if you have a database with less than 10% data change in a week then you use RAID 5. He does caution that your mileage may vary.
Sadly, there are no hard-and-fast rules for when to use RAID 5, RAID 6, or RAID 10. Every database is different and requires a different configuration to ensure that it’s performing at its peak. – Chapter 5, SQL Server MVP Deep Dives V2
The last third of the chapter discusses using the storage technology to create snapshots of the disk which can be used to create testing environments. It sounds similar to creating snapshots in SQL Server so it made sense, but I don’t picture myself ever getting that far into the weeds on storage. At least I will be able to follow along when the SAN admin starts talking about creating a VSS vs. a non-VSS enabled snapshot.
He also shares information on aligning the disk partition to the storage blocks, which seems like an issue that has gone away with Windows 2008 so I admit I didn’t spend as much time on this part.
I’m really happy with the information I came away with from this chapter. It helped me see the value in this little project of mine because it is not something I would have taken the time to read, but it is valuable to know.
Chapter Five SQL Server MVP:
Denny Cherry (B|T) is an independent consultant with more than a decade of experience working with platforms such as Microsoft SQL Server, Hyper-V, vSphere, and Enterprise Storage solutions. Denny’s areas of technical expertise include system architecture, performance tuning, replication, and troubleshooting. Denny currently holds several of the Microsoft Certifications related to SQL Server for versions 2000 through 2008, including the Microsoft Certified Master, as well as being a Microsoft MVP for several years. Denny has written several books and dozens of technical articles on SQL Server management and how SQL Server integrates with various other technologies. He is also a founder of SQL Excursions, which is SQL Server training in Napa Valley, CA by himself and (in 2012) guest speaker Thomas Larock.