2.7. Best practice considerations: storage system sizing
This chapter addressed server sizing from a storage performance perspective. Although there are some compute-constrained and network-constrained SQL Server implementations, they are reasonably rare, with the most common hardware problems related to poor storage design.
Classify application workload as OLAP or OLTP and be aware of the difference in I/O patterns and bandwidth requirements between sequential and random I/O.
Measure and implement the required number of disks to support the I/O workload. SQL Server performance is increased by striping data across many disks, thus having multiple spindles in action servicing I/O requests.
SCSI or SAS disks (or Fibre in SAN-based solutions) typically offer higher performance than SATA disks, particularly ones with very high capacity. SATA disks, however, are a cost-effective option for smaller deployments of SQL Server or as online backup disks.
Size storage for performance first, and then consider capacity requirements.
Avoid RAID 0, and only use RAID 5 for applications with little write activity, or where budget is limited and the performance overhead is acceptable.
When comparing and selecting a storage system, consider all aspects, including initial and total cost, DBA control, disaster-recovery options, scale, and performance.
Develop a strong working relationship with the SAN administrator and present storage requirements from a DBA perspective (early in the design process) in order to avoid poorly configured SAN storage.
If possible, create transaction log LUNs on dedicated SAN disks, particularly for databases with high transaction rates.
When performance testing on a SAN, be mindful of how the production system LUNs will be configured, and take into account the load from other applications with LUNs on the same disks as the SQL Server LUNs.
Additional information on the best practices covered in this chapter can be found online at http://www.sqlCrunch.com/storage.
In the next chapter, we'll continue our look at storage, but from a broader, physical server design perspective.