RAID 5 or RAID 1

We're just in the process of configuring our new DB server and I'm having an argument with the guys who build our servers and they say it doesn't matter what RAID you use for SQL it will work and they are looking at using RAID 5.

 Now my understanding of SQL is limited and I was always told SQL worked best in a mirrored configuration so RAID 1 and I'm trying to prove that this is what the new server should be configured with.

We're going to be using SQL 2005 Standard.

 If anyone could backup or disprove my line then that would be great?

Thanks 

Hi jonchill,

RAID 10 is the reccomendation for SQL databases, it will *work* on any form of RAID, but due to the way that RAID 10 works (a mirrored stripe set) provides greater performance & fault tolerance at the same time.  Ideally you would have two RAID 10 'sets', one for the data files (.mdf's) and the other for the log files (.ldf's).

EDIT: some additional info on RAID 10 -  http://www.acnc.com/04_01_10.html

Thanks for the info, unfortunately the server we've got only has a single disk for OS etc and 3 disks for Data.

So would RAID 5 be the best solution?

Thanks

Hi, no worries :)

Yes, if that is the disk configuration you have to live with, then it will still work, it will be quick for reads & fault tolerant at the same time, I would say the only worry you could have is the single disk for the OS, unless you are using a BMR backup solution for a quick turn-around on a rebuild (or it is not a mission critical server that can be down for a few hours whilst you rebuild it).

I tend to use RAID 1 for all my OS drives, and a minimum of RAID 5 for data, I do have several SQL servers that are running on RAID 5 and they are quite happy running on that configuation.  But, if you have a heavily used mission critical SQL server, I would usually go for RAID 10 if you can.

RAID5 and SQL Server go together like Oil and Water.

can it be done? yes. but the performance WILL NOT be optimal. RAID5 is better suited for applications where you need alot of storage and parity...say, a File server.

 in my opinion, the best RAID levels for a DB server would be RAID10, then RAID1 mirrored pairs with separate pairs for the Logs and Data drives (you want to separate logs and data onto different spindles to prevent any contention).

RAID 5 is not 'the' recommended RAID level, correct for performance reasons only (this has been mentioned already).  But it DOES work perfectly well as long as the server is not suffering exceptionally heavy use. (believe me, I speak from experience) as I said perviously, RAID 10 is the preffered option, but far from the only viable option (please note the 'recommended applications' section, lower right-hand corner of this link http://www.acnc.com/04_01_05.html).  If 'jonchill' has to live with the RAID 5 configuration available in their server, or alternatively merge all 4 drives in to one RAID 10, I would rather run a single OS drive and the remaining 3 in a RAID 5... or do you disagree, if so why (this would/could be valuable information for the rest of us)?  The only other slightly viable option available to him would be to do 2 RAID 1 configurations, but in all honesty, you'll notice a fairly minimal performance difference between the RAID 5 & RAID 1 configurations, bearing in mind the RAID recommendations for DB servers have been around a long time, and in that time RAID card performance, HDD performance, memory & processor performance have multiplied several times over...

RAID 5 is perfectly fine for database servers with light/medium write transaction loads.  Disks and sub-systems today are so fast that the days of giving up 1/2 your capacity for write performance are fading away in most circumstances.

Systems with high OLTP loads should still be built using RAID 1+0 (mirrored strips sets) or RAID 0+1 (striped mirror sets) for the database and transaction logs, though.

If what you have are only 4 drives, you are in a pickle.

Current recommeded best practices are a RAID 1, 5, 6, or 10 for OS and Programs.  You need to protect your OS partition as well as your data partitions.  No OS, no access to your data.

MS SQL recommended versus best practices.

MS Recommendation. 
Separate RAID drives for DATA and Transaction Logs, also dbtemp (very few do the last). Actually, they recommend that each database and transaction log have their own spindle set. This makes disk and san vendors very happy. 

Based on the application needs the Transaction logs should be on the fastest drive spindles you can acquire and should only be used for transaction logs.  This keeps log writing going in sequence and not moving from sector to sector.

DATA. again based on the application and user requirements a RAID 5 drive should work.  RAID 10 for the best performance, as well as 15K RPM Drives.
Many of your decisions come down to performance of SQL, User Experience, and data intgretity.  Server drives are not that expensive, versus a down time.

 What this all means is that you may need to re-think your deployment (and the intelligence of your vendor).  If your server is a typical 6 bay unit (3) RAID 1 partitions may be your safest solution.