Consolidating databases sql server
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.
The modified data has been written to the NVMe drives of the caching tier, and at some point Storage Spaces Direct will destage it to the SAS SSDs.
Before destaging occurs, the workload may need SQL Server to read that data back into system memory; after all, we’re talking about the workload’s most active data. Assume we’ll consolidate ten workloads, each workload has a 400GB database, that’s 4,000GB total.
Optimally, SQL Server would have enough system memory available to keep your entire database in memory for the highest possible performance.
When consolidating SQL Server workloads, that’s unlikely to be your situation.
From experience you know that when you add more virtualized applications to the server, the server quickly hits a storage performance bottleneck that effectively limits the number of workloads that server can support.
Getting more SSDs will give you more controllers and increase bandwidth.