White Papers

Table of contents
3 Dell EMC SC Series: Microsoft SQL Server Best Practices | CML1057
Table of contents
Revisions............................................................................................................................................................................. 2
Acknowledgements ............................................................................................................................................................. 2
Table of contents ................................................................................................................................................................ 3
Executive summary ............................................................................................................................................................. 5
1 SQL Server on SC Series arrays ................................................................................................................................. 6
1.1 Manageability ...................................................................................................................................................... 6
1.2 High availability ................................................................................................................................................... 6
1.3 Performance ....................................................................................................................................................... 6
2 Sizing for SQL Server ................................................................................................................................................... 7
2.1 Key I/O performance metrics .............................................................................................................................. 7
2.1.1 IOPS ................................................................................................................................................................... 7
2.1.2 Throughput ......................................................................................................................................................... 7
2.1.3 Latency ............................................................................................................................................................... 7
2.2 The write penalty ................................................................................................................................................ 7
2.3 Random I/O compared to sequential I/O ............................................................................................................ 8
2.4 OLTP workloads ................................................................................................................................................. 8
2.5 OLAP/DSS workloads ........................................................................................................................................ 9
2.6 Sizing tiers .......................................................................................................................................................... 9
2.7 Using SSDs ........................................................................................................................................................ 9
2.7.1 Traditional SSD configurations ......................................................................................................................... 10
2.7.2 Flash-optimized configurations ......................................................................................................................... 10
3 Validating the storage design ..................................................................................................................................... 11
3.1 Validating the I/O path ...................................................................................................................................... 11
3.2 Validating the disks ........................................................................................................................................... 11
4 Storage setup and configuration ................................................................................................................................ 13
4.1 Single or multiple disk pools ............................................................................................................................. 13
4.2 Creating volumes .............................................................................................................................................. 13
4.2.1 Performance considerations ............................................................................................................................. 13
4.2.2 Flexibility and manageability ............................................................................................................................. 14
4.3 Storage profiles................................................................................................................................................. 14
4.3.1 User database data file considerations ............................................................................................................ 14
4.3.2 User database transaction log file considerations ............................................................................................ 14
4.3.3 Tempdb database file considerations ............................................................................................................... 14
4.3.4 System database file considerations ................................................................................................................ 14