General SQL Server File/Device Placement Recommendations
As you grow in experience with SQL Server, you might have learned the painful ramifications of placing SQL Server files on the wrong type of disk subsystem or have overloaded one disk with several competing disk write scenarios. For these reasons, it is imperative that you adopt and implement a solid placement and disk configuration plan that will not only yield high availability but also allow you to scale your applications. Figure 10.11 generalizes the type of RAID level configurations that you should target for all of your highly available system. It takes the approach of getting the highest fault tolerance without sacrificing performance. This covers both online transaction processing applications (OLTP), decision support systems (DSS—read only), and the other SQL Server databases that must be treated with care. Especially important is that of TempDB. You want to isolate this away from everything on the planet. It can be the most active database in your SQL Server instance.
Figure 10.11. SQL Server and clustering file/drive fault tolerance recommendations.
From a file placement point of view, you should adopt a strategy of isolating volatility away from each other (across controllers, or I/O channels if at all possible). Figure 10.12 illustrates this concept of spreading out the disk activity across as many devices as possible. In particular, note that transaction log files are isolated away from a particular database data files, TempDB is isolated to a drive by itself (for performance reasons and to maintain system availability), master database and MSDB are also isolated away from application databases, as is the quorum drive for MSCS. In addition, application database data files of one application that are very active can be placed with transaction log files of a different application that are not very active. The overall effect of this type of strategy is great throughput.
Figure 10.12. SQL Server database data and transaction log file placement recommendations.
