What is the best raid type for SQL Server in terms of performance and reliability?
3 Answers
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, correct but it's SQL Server so Linux has nothing to do with it. Feb 23, 2010 at 12:19
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.
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.