4

What is the best raid type for SQL Server in terms of performance and reliability?

3 Answers 3

6

Hands down if you needs lots of I/O you need to look at raid 10

RAID 1+0 (or 10) is a mirrored data set (RAID 1) which is then striped (RAID 0), hence the "1+0" name. A RAID 1+0 array requires a minimum of four drives: two mirrored drives to hold half of the striped data, plus another two mirrored for the other half of the data. In Linux MD RAID 10 is a non-nested RAID type like RAID 1, that only requires a minimum of two drives, and may give read performance on the level of RAID 0.

1
  • +1, correct but it's SQL Server so Linux has nothing to do with it. Feb 23, 2010 at 12:19
3

You need three volumes, each on a separate array and if possible, each on a separate controller (assuming the IO volume warrants this).

The first volume should be a RAID 10 volume consisting of as many fast 15k spindles as you can afford, this will be where you data device is stored.

The second volume should be a RAID 1 volume (or raid 10 if you need the space) consisting of fast 15k disks, this will be the volume where your logs are stored.

The third volume should be RAID 1 (or raid 0 if you like living dangerously and can afford the downtime) of fast 15k disks where your temp device is stored.

2
  • Great solution. Follows MS suggestions.
    – Dave M
    Feb 23, 2010 at 13:27
  • In this scenario, where is the OS?
    – clweeks
    Jan 16, 2014 at 15:35
2

For Performance:

I'd consider Bestrafe's suggestion of Raid 1+0 first as it's probably a good middle ground (though it would suck if the same disk in each half of the array failed). If cash is slim Raid 1 is a worthwhile alternative. Write performance will be about on par with a single disk and read (should) be better.

If the database is heavily used, particularly for writes, I'd probably steer clear of anything involving a parity stripe (Raid 5 or 6) due to the decreased write performance. That said, if you're only dealing with a handful of requests it'll probably be fine.

For Redundancy:

If write perfomance isn't a huge factor, you might want to consider raid 5 with hot spares or raid 6. If the data is mission critical and low access I'd be giving serious thought to the two disk fault tollerance of raid 6.

You must log in to answer this question.