SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


RAID 5 and SQL Server


RAID 5 and SQL Server

Author
Message
Tony Davis
Tony Davis
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: Administrators
Points: 1057 Visits: 1161
Comments posted to this topic are about the item RAID 5 and SQL Server
SQL_ME_RICH
SQL_ME_RICH
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1254 Visits: 1596
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
Evil Kraig F
Evil Kraig F
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10723 Visits: 7660
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
Ivan Argentinski
Ivan Argentinski
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 38
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 w00t 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.
Minvalla
Minvalla
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.
Ivan Arjentinski
Ivan Arjentinski
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 43
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?
SQL_ME_RICH
SQL_ME_RICH
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1254 Visits: 1596
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.
S. Kusen
S. Kusen
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1836 Visits: 1117
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
johnr_johnson
johnr_johnson
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 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.
peter-757102
peter-757102
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1069 Visits: 2559
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search