Mastering Microsoft SQL Server Consolidation Planning - Part 8

In this online guide, I will help my readers overcome the challenges in Microsoft SQL Server consolidation planning. You can access the main page of "Mastering SQL Server Consolidation Planning" here.

In the previous part I wrote about the analysis of external dependencies, defining architecture alternatives and setting the metadata. This time I discuss cost model prestudy, calculating capacity distribution and selecting the desired end architecture.

SQL Server platform cost model prestudy

In this business-driven phase of the project we will match all the SQL Server and OS licensing, hardware, service, and capacity costs against competing architecture models. This phase is iterated together with the “Calculating optimal capacity distribution” phase of the project. This iteration may produce one or more target architecture models that we let the project stakeholders to select from.

The best way to do a cost model prestudy is to iterate competing target architecture models and to see the cost differences between them. Often, even when the licensing costs are minimal in some architecture models, the hardware and service costs negate this benefit compared to some other architecture options, so pay attention to all the factors.

Calculating an optimal capacity distribution for SQL Server instances

In this phase, we calculate an optimal capacity distribution of SQL Server source instances and servers over the desired target architecture models. Our SQL Governor® software uses an international patent pending calculation method based on the metadata, benchmark data, and time series data collected in the “Monitoring existing environment” and “Setting the metadata” phases. This phase is iterated together with the “Cost model prestudy” phase.

Our instance-level calculation method uses so-called templates in the capacity planning. Each of these templates is bound to a certain type of target system architecture, server hardware, and some other input parameters. A template can then generate as many target servers that a certain set of source DBMS instances needs for future use. This makes capacity planning more flexible to fulfill different architecture needs. The planning criteria consists of all the key inputs which need to be defined and set in SQL Governor® in order to automatically generate the optimized capacity plan. The planning criteria covers e.g. the following: the targeted lifespan of the new platform, the server hardware specifications for the new platform, threshold limits for target system RAM and CPU time series, their respective target SLA’s, and so forth.

Once the monitoring period is completed and the planning criteria has been set, SQL Governor® automatically generates an optimized capacity plan, which is based on the benchmark and trend-extrapolated time series data. It calculates the saving potential in terms of CPU cores by comparing the number of cores in the source platform and the new platform. SQL Governor® uses advanced time series analysis and machine learning in order to find out the optimal result out of the analysis for the instance-level consolidation. The server-level consolidation is a less automated process but can deliver great savings as well for the customer.

Select the desired SQL Server target architecture

In this phase the project stakeholder evaluates the costs and the pros and cons of the calculated architecture solutions we have presented and selects the best fit for their business case.

This is the final part of our guide "Mastering Microsoft SQL Server consolidation planning”. Thank you for reading!

Access the main page of this guide here. You can also download this guide as an ebook below.

Learn how to master the consolidation planning of SQL Server platforms 

Make sure to also check out our blog series on "Detailed specification of SQL Server migration planning”.

Jani K. Savolainen
Founder & CTO
DB Pro Oy