Optimal RAID Configuration for high performance SQL server

Question

I have a SQL application that continuously logging information to the SQL Server 2008 R2 every minute from multiple location across the network.  It runs 24/7.  I like to find out what is the optimal RAID configuration I should use to avoid any down time,  loss in data as well as high performance
I am also running Windows Server 2008 R2

Answer 1

Irene,

Honestly it depends on the workload of the server and the budget for disk. While it's easy to get carried away in planning, the business ultimately decides how it wants to proceed. Having said that, you first need to figure out your workload. Once you have your average workload recorded the next step or what can be done in parallel is to see what your storage device is and what type of devices it supports. For example, if you have a SAN, do you have to buy disks in packs of 8 and fill a row with the same disks? Do you they all have to be in the same storage pool? What type of disks do they support, in size and speed, solid state? Does you device support hardware raid (for example a san or nas) or does it need to do software raid (in windows which causes a good deal of overhead)?

Once these are answered and you have an estimated amount of budget you can create a setup that will give you the most performance for the money.

A quick overview of common raid levels:

Raid 0: Striping, minimum of 2 disks. Data is striped across all disks in the disk group. Fast writes, fast reads. Not fault tolerant at all, losing a single disk breaks the group and the information can not be salvaged. I would never used this in production. The total amount of usable space is size of disk * number of disks in array.

Raid 1: Mirroring, minimum of 2 disks, all information is mirrored to each other disk in the set. write operations are N per group (this means if you have 2 disks in a mirrored pair, 1 physical write will cause a total of 2 physical write (1 per disk as the information is mirrored)). Group can lose N-1 disks and still function without data loss. Read speeds can be increased possibly by N. Total usable space is the size of a single drive in the array as the entire array is mirrored.

Raid 5: striping with parity, minimum of 3 disks needed. This offer a good balance between usable storage, fault tolerance and performance. Raid 5 is typically used at most places as the generic raid type. This may or may not be what is needed, but provides a great base. Data is written across all but one disk which is used to hold the checksum. Raid 5 can tolerate a single disk failure which is considered then to be in degraded mode. The array acan be rebuilt in most instances by taking out the bad drive and putting a good one in its' place. The array will be rebuilt but will have a sever performance hit while running in degraded and while rebuilding the array as the data must be recovered from the checksum caluclations. Offers good read speeds (n-1) but suffers from slower write speeds.

Raid 10 (or 1+0): mirrored array of stripes, minimum of 4 disks. Generally the most performant raid type that most devices support (other than raid 0 which is not fault tolerant). Data is mirrored in one set and stripped across the first and second set. This offers both the benefit of mirroring and striping for reads speeds and striping for write speeds. At most a single disk can be lost from each mirrored set of stripes, which under certain circumstances could actually be multiple disk failures. While this offers generally the best protection and performance, its weakness is usable drive space which would be the size of a single drive * number of drives / 2. So, for example, if you wanted to run 100 GB of raid 10 you would need at minimum 4 drives of 50 GB which would give you 50 * 4 / 2 = 100 GB. Raid 10 works very well for transaction logs which have high sequential writes and possibly reads.

-Sean

Answer 2

Thanks Sean.  I have not look at the hardware yet.  It sounds like RAID 10 is a good choice if price is not a issue.  My application will involve a lot of sequential write.  I will definitely look at a hardware RAID card.  The machine we might be looking at is Dell R710.

How should I partition the disk or have multiple disks with RAID controllers to get the optimal performance of the system?

Answer 3

Irene,

It all depends, you have to work with what you are given. I took a look at the Dell R710, it's a 2U server with support for up to 8 local drives. Unless this supports extended local storage you're going to get 8 drives if all you use if local. If extreme performance is required from your disk subsystem, you will not achieve it with this. If you are just looking to make the best of the 8 drives you have that's a different story.

With 8 drives, others may have differing opinions... It's really hard to say without testing your application which may perform different depending on how it is implemented (for example you find you have a high tempdb usage, etc).

OS/SQL Binaries - 2 drives/Raid 1

Data/TempDB - 3 drives/Raid 5

Logs - 3 drives/Raid 5