Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

RAID 5 and SQL Server Expand / Collapse
Author
Message
Posted Saturday, January 8, 2011 11:45 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Yesterday @ 5:25 AM
Points: 569, Visits: 1,018
Comments posted to this topic are about the item RAID 5 and SQL Server
Post #1044901
Posted Saturday, January 8, 2011 6:56 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:41 PM
Points: 486, Visits: 1,359
Hi Tony -

The timing of this article is both funny, and informative. I am literally in the process right now of finishing up the design of a new database for my company. We went with similar hardware to a previously configuration of theirs (Dell PowerEdge R710). I beefed it out to spec on the 6-drive bay version (wasn't bad given that there wasn't suppose to be a budget for this project). I loaded it with 6-146gb drives, RAID 5 with a 12mb cache controller, and 8gb of RAM (wanted to start with 16, but cut it back due to going with only the 6-bay drive config).

Now - I had not even heard of this kind of venomous talk about RAID 5 and Databases before. My background has been in I.T. for over 20 years, and RAID 5 was always a very popular and steady platform, for the most part. I'm only newly born to SQL Server (since about 2004 as more of an application analyst), and this is my first titled role as a DBA, but I have to tell you - I would not have gone above RAID 5 for many reasons (not the least of which is the trade off of disk space and the cost for it), but to know that there was all this disparity (no pun intended) over the manner in which the controller writes to the disk is nearly laughable if not questionable. I have never heard of such things being that bad in the past, but then again - I wasn't big on Oracle!

I'll let you know how my first endeavor goes. It's an OLTP system that is being readied for role out next week!

Best wishes to you, and keep the articles coming!

Rich Yarger
St. Louis Park, MN
SQL Server DBA
Post #1044931
Posted Saturday, January 8, 2011 11:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 6,249, Visits: 7,404
The write penalty for RAID 5 is painful, there is little argument there. However, unless you're dealing with incredibly high transactions, the write-cache built into most sans these days alleviates a lot of your pressure.

RAID 5 has become more and more viable as different companies have worked out ways to reduce the parity pain. It's also still popular for reporting systems.

In general, I'd agree. There's a cost tradeoff for RAID in SANs, and the fact that rarely does a system get a purely dedicated spindleset in a SAN means you're not really working from a hardware optimization standpoint *anyway*.

However, when you start dealing with huge systems that you do need hardware optimizations, you're looking at a significant cost. Enterprise Disks aren't cheap, no matter if they're *cheaper*. This isn't cheap. A combination of RAID5 for less transient data and RAID 01 (my preference, a striped series of mirrors, it has more stability) for the high volume data (filegroups are your friend...) is workable. I've seen workarounds from multiple RAID5 setups which where then AGAIN RAID5'd (don't ask) to any other number of ways to try to deal with RAID5 issues.

In the end, it's cost vs. throughput. There is a price for everything. SQL Server is a mid-cost product, and thus, a lot of times we end up with mid-cost solutions for all but the largest endeavours. We've learned to cope.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1044944
Posted Sunday, January 9, 2011 12:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 8:22 AM
Points: 2, Visits: 35
I'm used to using RAID 10 almost always for DB setups. But I think that RAID 5 will gain significance once again when SSD disks begin to be more widely used for DB.

There are several reasons to prefer RAID 5 in such setups:
- SSDs are generally fast enough, even on writes, to compensate for the parity
- SSDs are expensive per GB (currently - around $2/GB for MLC) and having more usable GB is good

Currently I'm considering how to advice a customer for their new DB server and I'm debating with myself whether to advocate for RAID 5 or even 6.

It will be most interesting for me to see the opinion of the other members on this.
Post #1044952
Posted Sunday, January 9, 2011 1:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 10, 2011 8:03 AM
Points: 1, Visits: 9
I think of the disk design more by raid channels matching purpose and separating loads across them. An oltp system I built for a large indie record label a few years ago had 7 channels:

Raid 1 - operating system and apps, main swapfile
Raid 10 - Sql data
Raid 10 - Sql Index
Raid 1 - Logging
Raid 5 - Backups to disk

- plus 2 for the application data / reports etc both Raid 10

Also if specific tables in the database get a hammering they can be partitioned out onto other raid channels.
Post #1044954
Posted Sunday, January 9, 2011 6:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, March 8, 2014 1:51 PM
Points: 2, Visits: 34
Depending on the load, I would add also separate disk/array for the tempdb.

RAID5 for backups is totally ok, but I think an important question is whether RAID5 is usable for the main DB storage? Perhaps maybe the same question can be asked for the log?!?

Another question that pops in my head is whether SSDs will change the game and if yes - how? Perhaps their speed will lead to simpler disk designs?
Post #1044974
Posted Sunday, January 9, 2011 9:34 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:41 PM
Points: 486, Visits: 1,359
I'm going with RAID 5 for Filegroups / .NDF's, and something smaller for the OS and installed instance / .MDF. The .LDF will be on yet another disk that is controlled of the mainboard - probably (most likely some form of EIDE or SATA if available to my spec).

This is a great discussion and is giving me cause for thought on going with RAID 10 on the main array if I can manage it this late in the game for my current project.
Post #1045006
Posted Sunday, January 9, 2011 2:43 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:52 PM
Points: 968, Visits: 964
As you mention, the SAN companies have combated the the issue with larger caches, and to my knowledge, my company solely uses RAID 5 in all SAN arrays. We differentiate tier 1 with tier 2 and tier 3 with the size of cache on the arrays and the RPM's of the disk. We consider our tier 1 to be EMC Symmetrix with 15k disks and the larger write cache. We have XP HP Arrays that have mixed disks.

Ultimately, from a DBA team perspective, for better or worse, our SAN configuration is a black hole because there are so many different arrays and brands in play (EMC Sym/ Clariion, NetApp, etc). We haven't seen very many problems with RAID 5 and that is what our SAN admins continually tell us is how they carve up the arrays.

They pre-provision the arrays when they are built, so it's difficult for us to even request a RAID 10 LUN from them because they simply "don't do it".

Best regards,
Steve
Post #1045056
Posted Sunday, January 9, 2011 4:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 26, 2014 8:53 AM
Points: 143, Visits: 58
I recently spec'd two new Dell PowerEdge R710 servers for a small data warehouse. One for Analysis Services and the other for SQL Server. The choices for storage were constrained by capacity, performance and direct attach storage disk connections. I ended up choosing RAID 5 for both because I had to meet performance and storage requirements but was limited by 8 DAS connections. The AS system has an 8 x 146GB 15k rpm drive array and the SQL Server system has an 8 x 600GB 10k rpm drive array. Choosing a RAID 10 array could have been accomplished if I were willing to use slower disks to regain the capacity lost due to RAID 10 versus RAID 5. I'm convinced that RAID 10 is the right choice but can't escape practical realities.
Post #1045076
Posted Monday, January 10, 2011 1:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:47 AM
Points: 326, Visits: 2,215
I think RAID 5 must be burried.

It was once introduced when magnetic disks where still very expensive and capacity limited, while at the same time there was a need for redundancy. Nowadays, disks are cheap and BIG. So big even that when a raid array is repairing there is a sizable chance of an random error occurring and all data will be lost.

Add to that the complexity of a the system and with it the sensitivity to errors and larger downtimes. And you will conclude that RAID5 is expensive and unreliable compared to other solutions such as RAID 10.

And really most database servers are fairly small and do not have fully time dedicated admins. This is especially true for such an accesible product as SQL Server. Why Microsoft claims that RAID 5 is popular with its customers is beyond me. They must be polling the large customers maybe, not the hundreds of thousands smaller ones.

At my company we had two times now a disk in a RAID 5 broke and the array could not restore itself and had to use backups to continue working on another server. An identical issue with RAID 10, never cause any issues or significant downtime.

Is there really ANY reason to go for RAID 5 today?

I think not.

As for the previously mentioned speed argument...think also about the limited use of a SSD to compensate the use of slower but larger magnetic disks. They are quickly becoming afordable and the performance shatters that of spinning media!
Post #1045158
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse