Optimal drive configuration for SQL Server 2008R2
Variants of this question come up semi-regularly:
- SQL Server build
- Help me choose a RAID level combination for a SQL Server 2008 instance
- How should I configure these disks on a SQL Server for a BI configuration?
There are also occasional bun fights about the data/log separation "best practice".
- Placing Transaction Log on Separate Volume [solid state]?
- SQL Server - Separating data, log, and TempDB files on a SAN
Without more detailed analysis of what this server is doing, the same advice applies as given previously.
- RAID 1 for OS
- RAID 10 (6 disk) for data/logs/tempdb
There is rarely any point in a split with so few spindles available. A single array with a larger IOPs capacity will typically soak up the lumps and bumps of your workload better than 2 smaller arrays.
One variant that can be worth testing is putting tempdb on the OS drive. Only do so if you have a representative workload that you can replay repeatedly, to ensure a fair comparison of the configuration. If you go for this arrangement in production make sure tempdb growth is restricted so you don't inadvertently consume all free space on the OS drive.
Given that your OS drives are 7200RPM coasters, I'd be surprised if the tempdb on OS drive config bore any benefit.
It all depends on your workload, but with only 6 drives it does limit your options. If your workload is not heavily dependent on tempdb for things such as sorts, hash tables, and snapshot isolation, then you might be better off using the 6 SAS drives together in RAID 10. However, if you know or have the metrics to prove that tempdb is heavily utilized, then you should keep it separate as you have.
The actual answer depends on your needs but generally "put data and log on different arrays" is of higher importance than "put tempdb on its own array".
Given the number of drives you have available, my starting point would be:
- Two drives, RAID 1 - Operating system, executables, pagefile.
- Four drives, RAID 5 - All data files (alternatively, RAID 1+0)
- Two drives, RAID 1 - All log files
What should do is use SQLIO to test the performance of different drive configurations.