Let’s get ready for the 70-450.  Hit the official Microsoft page for the 450, and you’ll see the first skills measured on the exam:

Designing a SQL Server Instance and a Database Solution (14 percent)

• Design for CPU, memory, and storage capacity requirements.
• This objective may include but is not limited to: RAID, calculating table size, IO throughput, transaction per second, data compression, non-uniform memory access (NUMA), tempdb capacity

So I’m going to start with RAID,  Redundant Array of Independent Disks per wikipedia.  If you’ve been working with SQL for any time, you probably know that your systems administrator, or whoever built your server installed more than one hard drive into your SQL server.  He or she has implemented one or more different RAID levels on your server.  RAID allows you to config your hard drives in different ways, each one optimized to accomplish a specific goal.  Some RAID levels are optimized for read or write speedm while others are optimized for redundancy (read: safety).  What this exam will test is how well you know the different levels, and which is the best choice for a given situation.

So let’s go through the different RAID levels now.  I’m not covering them all, just RAID 0, 1, 5 and 10.  The odds you’ll ever see the others is pretty low.  But if you ever do see them, hit up Wikipedia to find out how they’re set up, and what they’re optimized to do.

One more thing before we begin, know this: Hardware RAID controllers over Software controllers every time.  Whenever you can use a piece of hardware to control where to read and write data will be faster than having to go through your CPU to figure that out.

RAID 0: Striping

You might hear RAID 0 referred to as striping or striped.  That refers to the fact that your data is split up into blocks and it writes the first block to one disc, then the next block to the next disk, and on and on.  That way no file should have two consecutive blocks on the same disk.

RAID 0 is designed to optimize read and write speed.

Since all the data is split up, you can use more heads to read and write the data at the same time.  If you set up a two disc RAID 0, all your reads and writes should be twice as fast as writing to a single disc of the same type on the same disc controller.  I say should, because your RAID controller has a lot to do with how close to the 2x speed bump you’ll actually see.

The weakness in RAID 0, If you lose a single disc, all data is lost. 

I like RAID 0 for my tempdb drive.  I like RAID 0 for my swap drives, which honestly I haven’t set up a separate disc for swap space in a loooong time.  Basically when I’m dealing with data that I don’t need any redundacy on, I’ll consider RAID 0.  But if you can’t afford any data loss, RAID 0 will not be your friend.

RAID 0 requires at least two disks in order to run.  The more disks you add the faster your read an write operations become, but there’s a trade off, you’re increasing the chance of failure.  The more moving parts you add to a solution, the more chances for failure.  See the last bold text as to why losing a disk in a RAID 0 would be bad.

RAID 1: Mirroring

RAID 1 is a set up where every disk in array is a mirror image (perfect copy) of every other member disk in the array.

RAID 1 optimizes read speed. Since your reads are split across two or more disks, you’ll read data off more quickly.  The trade off is when you write, you then have to write the same data to every disk in the array, in order to maintain that perfect copy.  As where a RAID 0 would write one block to each disk, every block has to be written to every disk.

RAID 1 provides fault tolerance.  Since every disk contains a copy of the data, you can lose a disk and keep on running.  You would need to replace that disc as soon as possible, but you could continue to run.

RAID 1 requires at least two disks. As a result, your storage just became twice as expensive as a single disk option.  But you’re paying for that fault tolerance.

I’ll generally choose RAID 1 for read intense drives.  When I want to speed up the reads, and I need some fault tolerance.  Some low write and high read databases will go onto RAID 1 (such as data warehouses), My OS often goes onto RAID 1 drives.  I never choose RAID 1 for high transaction volume data, I don’t want to trade update speed for security!

Next Time

I’ll cover RAID 5 and 10.  When we want a little more fault tolerance, without as much loss in write speed, we’ll want to talk RAID 5 or 10.  The big difference is money, how much are you willing to spend on your disks?  Get ready for RAID questions on the 70-450!  If you have any RAID scenarios you’d like to go over, let me know.  I’m here to help!