Problem 1
Developers come to you with a new physical database server that hosts a single database. It’s going to be your responsibility now. Currently the server is running off a single hard drive, the operating system and database files are all on that one drive. You realize you have to provide some fault tolerance for this system. After some monitoring you determine the database has a high level of reads and writes. You also learn many of the waits experienced by end users are coming from the I/O subsystem. You need to provide greater throughput to your discs. This database is not the most critical database in your infrastructure, so funds aren’t going to be unlimited, you’ll have to justify the costs associated with the new RAID solution you come up with. What RAID solution do you suggest?
RAID 5. You want to provide fault tolerance. You want to improve I/O performance, and money is a consideration.
While RAID 10 would give you more fault tolerance, you’d have to request at least one additional drive to set it up. With RAID 10 you’d lose 1/2 the space you put into the RAID, versus RAID 5 you’d only lose 1/3 of the space (given a 3 drive design). Since cost is a consideration, RAID 5 is the better choice.
Also, you get a write penalty with RAID 10 that you wouldn’t get with RAID 5, though you could just as easily argue you get a read boost with RAID 10. Given that reads and writes are fairly equal in the example, we’re going to count the RAID 10 write penalty.
Problem 2
You’re have 6 drives on hand to put into your new database server for data files and log files. This server is going to host a single database that has VERY high writes. You cannot afford to be down due to a single drive failure. What suggestions do you have in regard to RAID design for this server?
Two RAID volumes, Both RAID 5 When you have very high write volume on your database you’re going to want to separate your data files from your log files. By splitting the log files from your data files you can write to the data file at the same time you’re writing to the transaction logs. This speeds up your write performance remarkably!
Given your need to stand up to fault tolerance You can’t use RAID 0, although that could be built to be VERY fast for reads and writes. If a single drive fails, you lose the array.
You could use RAID 1 to stand up to fault tolerance, but there is a write penalty, you can only write to one drive at a time, the other is written to by your RAID controller, and is simply a copy of the data you just wrote to the first drive.
Given you only have 6 drives you could build a 6 drive RAID 10, but you’ll end up sharing I/O between the data and log files. Plus you lose 3 drives worth of space. With two RAID 5s you only lose 2 drives worth of space.
Problem 3
Now, let’s consider three parts of your database server, the OS, the transaction logs, and the data files. We cannot be down due to a single drive failure. The data files should have greater throughput than the OS. You already know transaction logs and data files should not live on the same drive, know the OS should not be on the same drive as your transaction logs or data files. What RAID configuration do you suggest?
Three RAID volumes, two RAID 1, one RAID 5. We can’t allow a single drive failure to bring us down, so that eliminates RAID 0 for any one of the volumes. The only drive we have to worry with performance is the data files, so we’ll use RAID 5 for them.
The OS and logs can go on two separate RAID 1 volumes. That way we won’t go down with a single drive failure.
Summary
Get familiar with what each of the four main RAID solutions give you, and be aware of their costs. Not all of them are measurable in dollars. If you have any questions about RAID, or any other topic you need to learn in order to get ready for the 70-450, let me know! I’m here to help. When we come back to certification, we’re going to do some more optimizing for I/O, we’re going to dig into multiple data files, file groups and more. Let’s squeeze all the performance we can out of your hardware…shall we?