Best Practice to Follow Disk Configuration

RAID Level 10 is recommended for the storage of database files. RAID 5 can be used for backups and certain type of database files. The Database Architecture assigned to the project will analyze the needs of the application and determine the required disk configuration as necessary. All production data should be stored on the SAN. Production data should not be stored on local arrays.

The operating system and database files should be stored on separate logical disks and partitions. The database data and log files should also be stored on separate disks as appropriate. 

Allocation Unit Size:

When formatting the partition that will be used for SQL Server data files, you should use a 64-kilobyte (KB) allocation unit size for data, logs, and the tempdb database.

Binaries:

Install the SQL binaries to the D Drive or allocated drive other than C.

Backup

The Backup volume should allow for 3 local database backups plus 10-20% Growth for each instance.  

User Data

Configure individual Data drive (with Drive letter E in Windows.)  Configure additional Data drives with Drive letter F, G,H etc. skipping previously reserved Drive letters and M (cluster MSDTC) and Q (cluster Quorum).

Logs

Configure individual Log drive (with Drive letter L in Windows.) Always place log files on RAID 1+0 (or RAID 1) disks. This provides: better protection from hardware failure, and better write performance.  Create SQLLogs folder on Log Drive

TempDB

• Configure individual tempdb drive (with Drive letter T in Windows) tempdb on dedicated array.  For the TEMPDB database, create 1 data file per CPU.
• Make sure to move TEMPDB to adequate storage and pre-size after installing SQL Server.
• Performance may benefit if TEMPDB is placed on RAID 1+0 (dependent on TEMPDB usage).

If tempdb will be used heavily, put it on its own separate array. In addition, pre-size tempdb to a size that will meet your server’s needs without having the need to autogrow. Divide the tempdb database into multiple files so that the number of files is equal to 50% to 100% of the number of CPU cores in your servers. Each physical file must be the same size.

Cluster

Configure individual cluster groups for MSDTC and the cluster Quorum.  Drive letter M (cluster MSDTC) and drive letter Q (cluster Quorum).

Permissions

• Add the AD Service User Account to the Root path with Full Control of Data(s), and List Folder Contents Permissions for Data, Log and TempDB Drives.
• If required create SQLData folder on Data and TempDB Drives
• Add the AD Service Account with Full Control of SQLData folder on Data and TempDB Drives
• Add the AD Service Account with Full Control of SQLLogs folder on Log Drive.

Below is the Sample Logical Drive Volume allocations ( it can be different in your environments )

Drive	Role	Description
C	OS
D	SQL Server Binaries
E	System Databases	Master, model, msdb
F	User Database Files
G	User Database Files
L	User Log Files
M	MSDTC	Disabled unless required for clustered services or other distributed transactions.
H	Backup
Q	Quorom	For clusters only
T	TempDB