Database.ca SQL Server Blog

My Blog

Monday, August 19 2013

Keywords: Raid, disk array, i/o sub-system, sql server, best raid choices for SQL Server

Best RAID Choices for SQL Server Data Files, Logs, as well TempDB

If your company is going to acquire a new SQL Server database server...and you are going to be using hard disks and not a SAN...then please continue reading.

 

First of all...I highly recommend you order absolutely the fastest disks your (company's) money can buy...15,000 RPM minimum.
 
Second: Get the most cache possible.
 
Third: Make 100% sure the disk's cache comes with battery backup. Without which you must not use it for a RDBMS server. This way when you reboot the server after a power outage, your cache's previous content would be committed to the disk at boot time.
 
Forth: Get the largest capacity (in GB or TB) your company can afford. It will be worth it. These disks are expensive, and you don't want to fill them up and re-order bigger sizes after 10 months. Buy the larger size now. By my experience, most company's order small conservative size disks...saying that these disks cost too much...only to see them get full in a year or so...
 

 

Some DBAs used to believe that Raid 5 was suitable for most database servers...they used install one Raid 5 and partition the disks and put OS on one disk, data on another, and logs on another etc.
That is an awful architecture. The only objective that could achieve at its best (if well configured) would be relatively quick disaster recovery and peace of mind.

 

Raid, disk array, i/o sub-system, sql server, best raid choices for SQL Server

A true SQL Server DBA understands TempDB...its role and importance inside this RDBMS...and the need to accommodate its data and log files with their own proper spindles respectively, budget allowing of course.
 
TempDB is like a whiteboard in a busy and highly frequented classroom...lots gets written to it...and lots get erased from it...too much Write I/O...this is a highly transactional database and as such would do poor on RAID 5, guaranteed. I have measured this in various systems...and it has performed poorly each time. If you want your SQL Server's performance to shine, you need to understand and respect TempDB! Do not mix it with the other system databases...place it in its own flower pot...exclude it...give it some freedom and air and sunshine...and see it grow!

 

I have purposely omitted Raid 0...as it is too risky and understandably not suitable for a production Database Server. Do not put your OS or any other part of your production SQL Server on a Raid zero disk...if any of the two disks go down...so will the whole server.

 

I have also omitted more complicated Raids other than 5 or 10 : too expensive to purchase...not realistic at all.
 
RAID 10 is complicated and expensive enough...Companies who want a more robust disk sub-system and can afford it...would be looking at a serious redundant SAN or other disk sub systems...but not spending millions for 20 disk drives belonging to one single server's disk sub-system...I have obviously seen this happen...but too few and far in between...
 
As time goes by this will happen less and less since VMs, SANs, SSDs are getting more and more momentum.

 

 

 

 

 

Written by Ramin Haghighat