SQL Server Drive Configurations
How and where you place your database files is a critical part in ensuring the performance and availability of your SQL Server database.
You do not want to under estimate the needs and have a disk IO bottleneck and it is equally important to ensure your disk drives have proper redundancy to provide fault tolerance to meet the business needs.
How you should "slice and dice" your drives and where you place your information really depends on the number of drives you have available and what RAID configuration you use.
RAID (Redundant Array of Independent (or inexpensive) Disks) : A group of two or more physical drives being used together to provide fault tolerance and/or and increase in performance.
Here is a quick rundown of some of the more popular RAID options:
| RAID LEVEL | DESCRIPTION |
| RAID 0 (Striped) | Improved performance through spreading blocks of data across multiple disks. No redundancy, if one disk fails the array is lost. |
| RAID 1 (Mirrored) | Data is written identically to multiple disks. Redundancy exists as long as at least 1 drive in the mirrored set is functioning |
| RAID 5 (Block Level Striping w/Parity) | Stripes at the byte level (with error correction information) and requires that all drives but one be functioning in order for the array to remain active |
| RAID 10 (Striped Mirror) | Mirrored disk sets are striped providing high redundancy as well as high performance |
OK so now that you have some basic RAID knowledge under your belt let's discuss some possible drive configurations depending on the number of physical disks you have available. Please keep in mind that these are just sample configurations and that with more disks that are available the more options you will have on how you utlize them. Your "ideal" configuration might not be the same as my "ideal" configuration so you will need to review these recommendations against the needs of your database environment.
1 Physical Drive - This configuration is definitely where you do not want to be. Not only are you putting everything on 1 drive but you have no fault tolerance whatsoever.
| VOLUME | QUANTITY | RAID | DESCRIPTION |
| C:\ | 1 | None | Everything |
2 Physical Drives - This configuration can provide your most basic redundant disk configuration (a 2 disk RAID 1 mirror). Another option would be to do a 2 disk RAID 0 configuration but if you have 2 disks you should try and get some level of redundancy)
| VOLUME | QUANTITY | RAID | DESCRIPTION |
| C:\ | 2 | 1 | Everything |
3 Physical Drives - This configuration utilizes a 3 disk RAID 5 array to maximize the capacity and read rate which providing data redundancy.
| VOLUME | QUANTITY | RAID | DESCRIPTION |
| C:\ | 3 | 5 | Everything |
4 Physical Drives - There are a few different options but what I like to do usually with 4 available drives is to start breaking apart my operating system from my database files. That being said the way to do that (and still provide redundancy is two RAID 1 arrays)
| VOLUME | QUANTITY | RAID | DESCRIPTION |
| C:\ | 2 | 1 | Operating System, System Databases, Backups |
| F:\ | 2 | 1 | User Database Data Files, User Database Log Files |
5 Physical Drives - Very similar to the 4 drive configuration except your F:\ volume is now going to be configured in a RAID 5 array to maximize the capacity available while still providing redundancy.
| VOLUME | QUANTITY | RAID | DESCRIPTION |
| C:\ | 2 | 1 | Operating System, System Databases, Backups |
| F:\ | 3 | 5 | User Database Data Files, User Database Log Files |
6 Physical Drives - Here is where you start getting more flexibility on how you configure your drives. With 6 physical dries I like to take the opportunity to break out my transaction log files for improved performance.
| VOLUME | QUANTITY | RAID | DESCRIPTION |
| C:\ | 2 | 1 | Operating System, System Databases, Backups |
| F:\ | 2 | 1 | User Database Data Files |
| G:\ | 2 | 1 | User Database Log Files |
7 Physical Drives - Very similar to the 6 drive configuration but we're going to use that extra disk to take advantage of a RAID 5 configuration for our user database data files (F:\ volume)
| VOLUME | QUANTITY | RAID | DESCRIPTION |
| C:\ | 2 | 1 | Operating System, System Databases, Backups |
| F:\ | 3 | 5 | User Database Data Files |
| G:\ | 2 | 1 | User Database Log Files |
8 Physical Drives - Now we have the option of moving our system databases (most importantly TempDB) off of our C:\ volume and putting it on it's own set of drives.
| VOLUME | QUANTITY | RAID | DESCRIPTION |
| C:\ | 2 | 1 | Operating System, Backups |
| F:\ | 2 | 1 | User Database Data Files |
| G:\ | 2 | 1 | User Database Log Files |
| H:\ | 2 | 1 | System Databases (TempDB) |
9 Physical Drives - Very similar to the 8 drive configuration but we're going to use that extra disk again to add to our F:\ volume in a RAID 5 array
| VOLUME | QUANTITY | RAID | DESCRIPTION |
| C:\ | 2 | 1 | Operating System, Backups |
| F:\ | 3 | 5 | User Database Data Files |
| G:\ | 2 | 1 | User Database Log Files |
| H:\ | 2 | 1 | System Databases (TempDB) |
From here with the more drives you add you can do things like:
- Separate out your Backup files onto their own physical disks
- Configure your User Database Log Files drive in RAID 10 arrays for improved performance
- Configure your System Databases (TempDB) drive in RAID 10 arrays for improved performance
- Break out specific tables or indexes from your user databases and place them on their own drives
Check out RAID Level and SQL Server and Comparing Different Implementations of RAID Levels for more information
_____________________________________________________________________________________________