Which RAID is best for SQL Server Performance

Given the choice of one RAID array for a server running Microsoft SQL (either 2005 or 2008) which would you choose?
Thanks,.
b1dupreeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
choose any of these , RAID 5 or RAID 10
0
b1dupreeAuthor Commented:
What are the advantages of each?
0
Aneesh RetnakaranDatabase AdministratorCommented:
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

brejkCommented:
Do not use RAID 5 for SQL Server databases (bad random write performance, practically not available databases when one of the disks fails). Choose RAID 10 (if money is not a problem) or RAID 1.

Advantage of RAID 10: performance.
Advantage of RAID 1: price (comparing to RAID 10).
Advantage of both: data redundancy.

Generally the choice depends on your budget and the number of disks you have.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
b1dupreeAuthor Commented:
Thanks.

If I use an online spare how many drives minimum would I need for RAID 10?

If I use an SBS Server 2008 running Exchange am I best to go with RAID 5?
0
Aneesh RetnakaranDatabase AdministratorCommented:
1 drive for windows, sql server installation, windows paging file (Raid 1 )
1 drive for tempdb Raid 1
1 drive for SQL datafile RAID5/RAID 10
1 for log files  RAID 1
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
RAID 5 is the optimal level for both performance and price and We have the following RAID Levels:
RAID 0, 1, 2, 3, 4, 5, 10

For More info about these  levels, refer
http://msdn.microsoft.com/en-us/library/ms190764.aspx
0
b1dupreeAuthor Commented:
brejk:
What's your feeling about numbers of drives for RAID 10 with an online spare?

rrjegan17:
Are you advocating RAID 5 as the best price/performance for SBS with Exchange?
What are your feelings about RAID for SQL?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
RAID 5 is best if the stress lays on massive read operations (compared to writes), coupled with redundancy.
For RAID 10, you can use as many harddisk pairs as you need or wish. I would recommend to set up a   mirror-than-stripe config, meaning you pair harddisks, and build a RAID 0 on top of the pairs. This leads to highest independancy from drive failures, as one harddisk of each mirror pair can fail before the RAID is broken. The conventional setup is to mirror the striped harddisk sets, but as each stripeset fails if one of its harddisks fails, this is a higher security risk - two failures render the RAID useless.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
What is the necessity for RAID in your application either Mirroring or for better performance. If Better Performance is your concern then go for RAID 5 which will help you out. Otherwise go for RAID 1 would help you out.

And again You have two types of RAID, Hardware RAID with disk array or an OS based RAID or Logical RAID obtained using software.

A hardware disk array improves I/O performance because I/O functions, such as striping and mirroring, are handled efficiently in firmware.

Conversely, an operating systembased RAID offers lower cost, but consumes processor cycles.
When cost is an issue and redundancy and high performance are required, RAID-5 volumes are a good solution.

http://msdn.microsoft.com/en-us/library/ms184252(SQL.90).aspx

My suggestion is that you can select either RAID 1 or 5 based upon the above mentioned criteria. Other than that SQL Server doesn't have much criteria to select Best RAID for its operation.
0
Mark WillsTopic AdvisorCommented:
Interesting topic...

A lot of it will depend on your hardware configurations and what the disk backbone / controllers will support.

For disks, ideally get all the same if possible and use the raid configurations to ensure capacity e.g. 146GB 3.5-inch 15K RPM SAS Hard Drive (or maybe the 300GB)

Given a multi-raid array would be inclined to go:

System Disk : Raid 1  min 2 disks, capacity 1 disk
Database : Raid 10 min 4 disks (capacity 2 disks) or can be Raid 5 min 3 disks but is slower on writes - now seeing Raid 6 min 3 disks gaining popularity over R5
Log Files : Raid 1 min 2 disks capacity 1 disk
TempDB : Raid 1 min 2 disks, capacity 1 disk


Raid 1 is your basic mirroring. and is there really for fault tolerance and redundancy.
Raid 10 is both mirroring and striping. Striping is used to get performance by distributing data across multiple disks (spindles) which in turn enables a certain amount of parallelism or concurrency of activity (hence the performance boost).
Raid 5 is like a striping algorithm, but it contains a parity check. That is when the write performance does have an overhead. Raid 6 is an extension to Raid 5 and becoming more popular because of the improved throughput and fault tolerance gains over R5.

Any Raid based on some kind of Striping is typically regarded as the more spindles the better. We do see some (older) controllers that work optimally with 3 disk Raid 5 and then degrade adding more disks. Raid 6 can achieve same performance as raid 5 with one less disk (still needs min 3). Meaning it will perform faster than R5 with same number of disks.

You can gain more performance by seperating out the different types of files. arguably more so than by choosing any one particular type or Raid.

But if we look at the configuration above, then we find we need 10 disks, which is no good for your typical 8 disk enclosure.

So, can start to mix-n-match to decide what files can go on what array, or even the number of spindles in each array, or indeed, the nature and type of database ativity you are likely to encounter.

As a general "run of the mill" there is also nothing really wrong with a multi-raid array looking like :

System : Raid 1 - 2 disks capacity 1  disk
Databases : Raid 10 - 6 disks capacity 3 disks

or

System : Raid 1 - 2 disks capacity 1  disk
Logs and TempDB : Raid 1 - 2 disks capacity 1  disk
Database : Raid 10 - 4 disks capacity 2  disk

So, a lot of it is going to depend on budget and hardware capability, and for SQL Server, gobs of memory - the more the better (but also depends on which editions of op sys and sql server).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.