Number of Drives,Location and Raid levels

When you are setting up your new server, One of the important tasks you have its to pick the right number of disks for correct purposes at the correct RAID level. This article briefly deals with the same.

SQL Server Data files: One needs atleast one dedicated drive for Data files. Ideally, for best performance one needs the disk to be have it Raid 10 as it involves random reads and random writes.

SQL Server Log files: Log files have more sequential reads and writes. To avoid any IO contention, one needs a dedicated drive at Raid 1.

SQL Server Backups: Backups are always to be kept on a drive different from data and log files so that they don't become a single point of failure. ie., When your data/ log file drive gets corrupted then your backups are also lost , if placed on the same drive. As Backups involve high writes RAID 1 / RAID 10 is recommended.

Location of Operating System: Operating system is ideally to be placed in C:\ drive and should always be kept away from data file drive, log file drive and backup drive.

Location of SQL Server executables: SQL Server's executables are similar
to operating system files and hence we are better off keeping them away from Data and log files.

Tempdb location: In general, tempdb involves heavy writes and ideally Raid 1 or Raid 10 is recommended.It is a luxury if you can afford a separate disk for tempdb. If your application heavily uses tempdb then you are strongly recommended to do so.

So, Ideally one atleast needs 3 dedicated disks for SQL Server with one each for Data file , Log files, Backups.Note that this doesn't include C drive required for Operating system + SQL Server's executable.Out of the 3 dedicated SQL Server, one is expected to be on RAID 10. I realize that RAID 10 is the most expensive but all good things in this world are expensive., aren't they :) The most popular raid level, RAID 5 is mostly good for reads and for file servers which don't change much. RAID 5 performs 50% slower than RAID 10.

References: As usual another cool Brad McGhee article from here.