Company
Careers
Contact

BLOG

Practical Advice – RAID Levels & Drive Setup for SQL Server

EN-Blog-SQL-Server

by Bill Preachuk | 12.12.2012
Categories: Blog

A customer emailed and asked the following question:

"We are getting ready to consolidate all our older databases onto a new system. My question is does SQL 2008 need drives configured to one type of RAID for the databases, another type of RAID for the logs, and another type of RAID for the backups? I’m hoping we can simplify the storage with SQL 2008 so we can better utilize our available space. None of our databases are large or heavily utilized. The biggest would be just a few gigabytes. Most are less than a few hundred megabytes.”

An answer to this question takes into account more than just best practices. It involves budget, redundancy needs, RTO/RPO, how heavily the system is used, the amount of disk needed, etc. As much as I love tech stuff & over-engineering things, in the end it’s all about the customer

Some clues emerge from their statement:

  • “Consolidate onto a new system… better utilize available space”
  • “Hoping we can simplify” 
  • “None of our databases are large or heavily utilized”
Their client profile proves those statements out. Their data is certainly mission critical (no one can afford to lose data), but their requirements do not include having the absolute highest performing system at all costs.

Aside: Before we go any further here are 2 terrific articles that explain RAID levels. The diagrams are especially good. I have a hard time envisioning the difference between RAID 1+0 (10) vs. RAID 0+1, but these web pages explain it very succinctly.

http://www.thegeekstuff.com/2010/08/raid-levels-tutorial/ 
http://www.thegeekstuff.com/2011/10/raid10-vs-raid01/

In a perfect world, we could all use RAID 10 for everything. It is the best performing, most fault-tolerant layout of all. But it is expensive and makes the most sense for mission critical and/or heavily utilized servers.

Here are common guidelines:

  • Place SQL Server Data files and the Log files onto separate disks.
  • Data files can be RAID 5. If they are constantly updated and/or get a large volume of data bytes updated – then RAID 1 or 10 is a good option
  • Log Files work best as RAID 1 or 10.
  • If you physically separate out the tempdb – it should be on RAID 1 or even on unprotected disk. (tempdb gets re-created every time the SQL Server stops and starts).
  • Backup files can be RAID 5. These need to go off-site on a daily basis.
Now, given what the customer said (small databases and low utilization), if they standardized on RAID 5 for all of their storage needs – they’ll be just fine.

In my opinion – the most important details for any customer to remember have nothing to do with RAID levels. They are:

(a) The separation of data and log files
(b) That SQL Server backups are taking place and are rotating off-site. ***

On a side note: Do not EVER EVER let someone talk you out of SQL Server backups. Glenn Berry, in his spectacular free e-book “SQL Server Hardware Choices Made Easy,” phrases this much more eloquently than I ever could…

“RAID is no substitute for SQL Server backups."

Never, never, never let anyone, whether it is a SAN vendor, a server admin from your Operations team, or your boss, talk you into not doing SQL Server backups as appropriate for your Recovery Point Objective (RPO) and Recovery Time Objective (RTO) metrics. I cannot emphasize this point enough! Regardless of any pressure to which you are subjected, you must stand your ground. As the old saying goes, “If you don’t have backups, you don’t have a database.“