Consolidating databases sql server Non java cybersexchat
The total volume of writes (number of writes * size of writes) may not be particularly large, so you probably won’t need maximum-capacity NVMe SSDs; but you’ll want to maximize the write bandwidth available, to maximize the number of workloads you can support.To get more bandwidth, you can add more SSDs – each SSD has a controller, and the performance of the controller can be one of the limiting factor for bandwidth.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.Each OLTP workload brings its share of small-block random writes.Consolidating many OLTP workloads will bring small-block random writes, so you’ll want NVMe SSDs to absorb all those writes.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.
Using more SSDs – but at lower capacity points – can give you more controllers and bandwidth, as shown in the table below.
That’s easy to imagine today, with SQL Server 2008/R2 reaching End of Support on July 9, 2019 – a large percentage of SQL Server deployments run SQL Server 2008, and something will have to be done with them.
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.
You can see how additional lower-capacity drives – thus more controllers – is how you’d adjust this configuration to provide more bandwidth, if you needed it.
Comparing the cost with a national online seller, the four smaller drives cost about 28% more than the two larger drives, but you get 2X the max bandwidth.
Microsoft’s guidance is to add capacity drives in multiples of the number of cache tier drives, so the minimum is four capacity drives.