Skip to content

70-450: SQL, RAID 0, RAID1

2011 October 21
by Shannon Lowder

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!

5 Responses leave one →
  1. MOMEN permalink
    February 27, 2012


    I am appearing for Microsoft Certification for the first time. I have chosen to write 70-450 for DB Administrator for SQL Server. Can I directly take this test or Do I need to any pre certifications.

    Please advice.


    • February 28, 2012

      You’ll have to pass the 70-432 before you can take the 70-450. The first exam gets you the MCTS (technology specialist), the second gets you a MCITP (IT Professional).

      • MOMEN permalink
        March 12, 2012

        Hi Shannon,

        I have attended 70-450 and passed the exam. Do I still need to take 70-432 exam. Please advice.


        • March 14, 2012

          You were able to take the exam before passing the 432? From what I understood, you had to take the MCTS exam before you could take the MCITP exam. I also understand you have to have BOTH exams for your MCITP certificate to be valid. I’d suggest reaching out to someone at Microsoft eduction to see if that’s right. Let me know what you find, I’d be interested to hear how this works out.

Trackbacks and Pingbacks

  1. 70-450: Calculating Table Size | Shannon Lowder

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS