This blogpost continues my series of blogposts regarding advanced capacity planning techniques on data migration. In my previous blogpost I wrote about right sizing and virtualizing SQL Servers. This blogpost is about Microsoft SQL Server workload instance level consolidation in different capacity planning scenarios such as:
- On-premises (new set of physical servers or virtual machines)
- On-premises to Azure IaaS (set of new virtual machines)
- On-premises to Azure PaaS (Managed Instances vs. set of new Managed Instance pools (Public Preview))
First, if you are not yet aware of new Azure feature called SQL Managed Instance Pool, which is currently in public preview, please check this brief article. Basically, it is all about consolidating smaller SQL Server instances into a Managed Instance pool. SQL Governor software already supports patented capacity planning on top of Azure Managed Instance Pools.
What is an Azure SQL Managed Instance pool? - Azure SQL Managed Instance | Microsoft Learn
Business needs
Drivers for consolidating SQL Server instances are such as:
- Data sprawl (too many servers, low level of workload consolidation)
- Lots of small SQL Server instances
- Need for SQL estate cost savings.
- Business / data strategy -driven transition to the cloud
Microsoft SQL Server estate TCO and Time-To-Solution
When comparing TCO and Time-To-Solution in between right sizing SQL Servers and consolidating SQL Server Instances, TCO is typically significantly less on instance consolidation, when targeting workloads against bigger Azure Managed Instance pools, VMs or physical servers. Instance level consolidation eliminates efficiently data sprawl, decreases amount of DBA work and brings in significant savings in rack space, server hardware and SQL Server licensing costs up to 50-60% compared to the situation before the workload consolidation. Time-To-Solution on instance level consolidation is less efficient than on right sizing SQL Servers, but on the other hand, benefits are great: Less physical space, less patching, and typically significantly less overall costs than by right-sizing.
Company politics
Company politics can be more challenging on instance level consolidation than with other data migration methods. This is because the DBAs typically tend to think, that having multiple instances running on same SQL Server is more challenging to address the variety of different workload types and security constraints. With this said, SQL Governor software has internationally patented method to eliminate and alleviate such bottlenecks what comes to performance and availability of consolidated SQL Servers. A senior level DBA using SQL Governor software should have no problem in cherry-picking such SQL Server instances, that are good candidates for instance level consolidation. Typical characteristics for such SQL Servers are that their workloads can be mixed what comes to workload types and security demands.
Note! You don’t have to consolidate all your SQL Servers – instead; select only those that apply to this purpose.
Data center and data platform architecture
In terms of technical architecture, instance level consolidation does not bring in any other parameters compared to the server right-sizing, except that there can be multiple SQL Server instances running on same SQL Server derived from multiple source SQL Servers, and the fact that on Azure transitions we can use PaaS instead of IaaS (=Managed Instances and Managed Instance Pools).
About Microsoft SQL Server Licensing
Because the number of CPU cores will be significantly less than the original number due to the harmonized and consolidated SQL Server workloads on fewer SQL Servers, also the overall licensing costs will be typically significantly less.
Are non-functional requirements met?
From a non-functional perspective, consolidating your SQL Server instances gives you harmonized, predictable performance and availability, when capacity planning is done properly with a software such as SQL Governor.
Consolidating SQL Server instances – how it is done?
Instance level SQL Server consolidation is basically a telemetry data trend- and CPU benchmark data -extrapolated mathematics applied with bin packing problem. So, it is basically a permutation calculation, which gives an answer to the following question: “What is the most optimal and harmonized consolidated workload now and in the predicted future for a set of instances over the optimal number of SQL Servers / Managed Instances with certain hardware setup / configuration?” All this is highly automated, internationally patented method and a process in the SQL Governor software.
Excerpt from SQL Governor: Instance consolidation details – particular target server is selected (storage extrapolation).
Excerpt from SQL Governor: Instance consolidation details – particular target server is selected (CPU extrapolation).
Excerpt from SQL Governor: Optimized instance consolidation results over tens of source SQL Servers.
Are you planning the right size and consolidate your SQL Servers? Want to get rid of the data sprawl on your SQL estate? If yes, contact me!
Jani K. Savolainen
CEO & Chairman,
SQL Governor
