Consolidating databases sql server
That whitepaper demonstrated that investing in fast flash storage enabled the same host to run 3x more workloads.
Three times more workloads per host means you can avoid buying two additional hosts to run those workloads – that can save several 0Ks in software licensing cost, with a 22x ROI.
Before we order those NVMe drives, let’s consider the “working set”.
Each workload has a set of active data that is frequently accessed, and that workload’s performance depends on whether that active data can be accessed quickly.
Instead, SQL Server will keep as many active pages from as many database workloads in system memory as it can; the remainder of the active pages be “paged in” when needed, from wherever they’re stored. In our all-flash configuration, the database is stored (“persisted”) on the capacity tier drives; except for recently-written data, which you’ll recall goes first to the caching tier drives before being destaged to the capacity tier.
For a single database, consider the portion of that workload’s active data that’s been recently modified.
In one of my previous posts, Test Results for an All-Flash Spaces Direct Configuration, I shared test results from a non-production Storage Spaces Direct configuration and discussed implications for the storage configuration to host virtualized workloads.
Now let’s look at SQL Server OLTP consolidation and some considerations when sizing a production-capable Storage Spaces Direct configuration that reflects Microsoft’s recommendations.
That whitepaper also showed that using fast flash storage in the host not only allowed you to run up to 3x more virtualized workloads on the same host, those workloads would all run 2x faster.
Microsoft’s guidance is to add capacity drives in multiples of the number of cache tier drives, so the minimum is four capacity drives.
Across our 4-node cluster, the eight NVMe SSDs can deliver up to a combined 1,600,000 4KB write IOPS, and even using the smallest-capacity 800GB drive, we get 6.4TB of cache tier capacity.
Consolidating several or many SQL Server workloads onto fewer hosts has been going on for years, with workload density per host increasing as servers and storage have become more capable.
Imagine the next wave of SQL Server OLTP consolidation in your company, to run more workloads on fewer hosts than you use today.