This chapter is from the book
This chapter is from the book
This chapter is from the book
Summary
Throughout this chapter, we have provided architecture examples based on real-world projects that you can adapt for your purposes. We’ve tried to explain all the relevant considerations and best practices you need to worry about when architecting your environment for high-performance and critical SQL Server databases. We covered the key aspects of SQL Server storage architecture for all environments as well as the differences you need to understand when architecting storage specifically for virtual SQL Server databases, such as the IO Blender Effect and the way IO queues work across hosts on the same data store.
We provided guidance on important database storage design principles and a top-down approach covering SQL Server Database and Guest OS design, Virtual Machine Storage design, VMware vSphere Storage Design, and then down to the physical storage layers, including RAID and using server-side flash acceleration technology to increase performance and provide greater return on investment. We concluded the chapter by covering off one of the biggest IT trends and its impact on SQL Server: hyperconvergence and scale-out, shared-nothing architectures.
Let’s briefly recap the key SQL design principles:
- Your database is just an extension of your storage. Make sure you optimize all the IO paths from your database to storage as much as possible and allow for parallel IO execution.
- Performance is more than just the underlying storage devices. SQL Buffer Cache has a direct impact on read IO, whereas virtual IO controller device queues and LUN, HBA, and Storage Processor queues can all impact performance and concurrency of IO before anything touches a physical storage device.
- Size for performance before capacity. If you size for performance, capacity will generally take care of itself. Much of this is due to the overheads associated with RAID storage needed to provide enterprise-grade data protection and resiliency. Use flash storage and automatic tiering to balance the performance and capacity requirements to get a more cost-effective solution overall.
- Virtualize, but without compromise. This involves reducing risk by assessing current performance, designing for performance even during failure scenarios, validating your design and its achievable performance, and ensuring storage quality of service, such as Storage IO Control. These all contribute to a successful SQL virtualization project. Make sure project stakeholders understand what performance to expect by having SLAs aligned to achievable IOPS per TB.
- Keep it standard and simple. Whatever design decisions you make for your environment, keep them as consistent as possible and have defined standards. Design for as few options as possible in your service catalog that cover the majority of system requirements. Only deviate from defaults where required.
We have covered storage performance in depth, as it is one of the most critical resources for a SQL Database. The next chapter will drill into how SQL memory allocation impacts the performance of your database, and how SQL and memory might change in the future.