Hard drive configuration options for an off-box SQL Server
Data throughput of the SQL Server is a key consideration for Workflow performance. The way that you configure your disk drives in SQL Server has a key influence on throughput. The hard drive speed also has an influence on throughput. It is recommended to use high performance hard disks. For example, you can use 10k rpm to 15k rpm SAS drives in a striped array.
For the best performance, make sure that the operating system, SQL data file, TempDB database, and the log file each have a dedicated volume, and associated controller channel. The data file requires both high read-write performance and redundancy. RAID 10 and RAID 0+1 are good configurations for the data file. RAID 0+1 has similar throughput as RAID 10, but its configuration helps simplify additional storage growth. RAID level 5 is not ideal for the database performance because it requires additional Read/Write activities for parity.
The TempDB database needs high read-write performance, but redundancy is not necessary. The TempDB database acts as a temporary working area for many processes. The TempDB database requires very high speed; however, it is not used for storage, and it is cleared regularly.
The transaction log also requires high disk throughput for optimal system performance. It should be hosted on a RAID 10.
In all of these options, the key factor is the end result that resides on separate physical disk and drive controller hardware. The best performance and maximum efficiencies are when that configuration rule is applied. Care should be taken if SAN or NAS storage is used to assure performance and efficiency. SAN and NAS storage arrays are often carved into logical volumes. These logical volumes are for optimizing space usage, and allowing multiple servers and applications to access (share) the same physical devices. This causes disk contention and slow performance. If you plan to use SAN or NAS, it is best to have this requirement discussed and planned for during implementation. Include the storage administrators in the planning.
Table: Example of an off-box SQL Server disk configuration