SQL Server capacity planning is the most essential part of any SQL Server migration project. This is because it is essential part of:

Planning optimal capacity for the SQL Servers, instances and databases
Creating substantial savings in overall SQL Server hardware and licensing costs
Calculating becoming SQL Server data platform costs

Reasons for the capacity planning for SQL Server migration can be such as:

Right-sizing existing SQL Servers
Eliminating the data sprawl and creating substantial savings by SQL Server consolidation
Renewal of the data platform due the outdated SQL Server hardware
Transition to the cloud such as Microsoft Azure
SQL Server version upgrade

Capacity Planning and Consolidation with SQL Governor

Effortless SQL Server migrations

Using individual SQL Server capacity planning scripts for SQL Server migrations is a slow, complicated, and error-prone process. SQL Governor automates and streamlines this process with our internationally patented methods and makes this typically challenging task easy and efficient.

On-premises to cloud

SQL Governor supports SQL Server capacity planning for both on-premises and cloud environments. Physical and virtualized server setups as well as hyperconverged (Nutanix) and public cloud environments (Azure and AWS) are supported.

Save money

SQL Governor calculates the optimal capacity for your SQL Server estate, resulting in remarkable cost savings and a robust target data platform. SQL Governor supports server-, instance- and database-level consolidation in addition to server right-sizing.

SQL Governor software has internationally patented SQL Server right-sizing and consolidation methods. It utilizes machine learning and time series mathematics such as trend extrapolation in predicting future workload seasonality and capacity needs.

Do you want to know more about SQL Governor software? Please book a free demo

What is capacity planning in SQL Server?

SQL Server capacity planning refers to a variety of methods of optimizing SQL Server capacity by measuring various performance counters over time and thereby achieving cost savings and improved performance due the better understanding of the overall capacity needs. Different SQL Server capacity planning methods consist of:

SQL Server right-sizing
Server-level consolidation
Instance-level consolidation
Database-level consolidation

SQL Server right-sizing

In SQL Server right-sizing, the current resource usage of each SQL Server is measured and used to forecast the optimal capacity for planned lifecycle. Most important performance counters in SQL Server right-sizing are:

Number of CPU cores in SQL Server
CPU contention percent
Amount of RAM in SQL Server
Storage IOPS
Storage throughput (MB/s)
Storage latency (ms)
Data file size (GB)
Log file size (GB)

Benefits of the SQL right-sizing in SQL Server capacity planning are:
Simplicity and time-to-solution (true-ups)
Minimal re-architecting and reconfiguration needs
Smaller impact of external dependencies

Database consolidation-1

Database-level consolidation

Database capacity planning is SQL Server is most effective by database-level consolidation, wherein SQL Server databases are reorganized onto new SQL Server instances by typically following one or more of the below mentioned strategies:

Dividing the databases based on their business criticality
Creating new SQL Server instances for different kinds of database workload types
Distributing different database workload types evenly over the SQL Server instances
Isolating problematical / ad hoc -workloads into their own SQL Servers and instances

Benefits of the Database-level consolidation are:

Great potential of hardware and SQL Server licensing savings
Harmonized workloads with better SLA’s

Instance-level consolidation

In instance-level consolidation, SQL Server instances are "stacked" upon one another onto existing or new SQL Servers in a way that minimizes resource peaks and bottlenecks while minimizing the overall number of CPU cores and RAM usage. This leads to less target SQL Servers and therefore effectively eliminates data sprawl in SQL Server capacity planning. This is possible because the great majority of SQL Server workloads are seasonal and highly predictable over time.

Benefits of the Instance-level consolidation are:

Maximum potential of hardware and SQL Server licensing savings
Eliminating data sprawl
Harmonized workloads with better SLA’s

Instance consolidation

Server consolidation

Server-level consolidation

In server-level consolidation, the goal is to find the right physical hardware or cloud virtual machine for hosting servers to be virtualized. The idea is to take in count the same performance counters as in SQL Server right-sizing by determining the optimal VM size for each server to be virtualized.

Benefits of the Server-level consolidation are:

Improved potential of hardware and SQL Server licensing savings
Relatively small re-architecting and reconfiguration needs
Relatively small impact of external dependencies

Different kinds of capacity planning

There are two different kinds of SQL Server capacity planning methods, replay-based capacity planning and benchmark-based capacity planning. SQL Governor is a benchmark-based capacity planning software and it is good to understand the difference in between.

Pros of replay-based capacity planning

More accurate with SQL Server 2008R2 to 2012 – 2014 migrations due the major changes in cardinality estimator

Cons of replay-based capacity planning

Restricted time series (one business cycle snapshots, 1 day or max 1 week); missing seasonality
Long-term trends are missing: Hard to understand long-term capacity needs
Complex to configure & use
Not scalable due the amount of work
Needs existing target environment

Pros of benchmark-based capacity planning

Unrestricted time series give insight in seasonality
Long-term trends make accurate long-term predictions
Easy to use
Scalable due the potential level of software automatization
Does not need actual target environment

Cons of benchmark-based capacity planning

Needs server benchmark data being kept up-to-date

SQL Governor Customers

SQL Governor software solution suits well for any business domain. During the last years we have had customers from various business verticals such as:

Software, Healthcare, Finance and Insurance, Legal, Industrial, Media, Transportation, Engineering, Telecom, Energy, Logistics and Retail, Environment

Case example: A-lehdet saved nearly 50% in SQL Server licensing costs thanks to server capacity optimization completed using SQL Governor

The A-lehdet Group is one of the largest media houses in Finland.

When Microsoft made changes to the licensing and pricing of SQL Servers, it was no longer practical for A-lehdet to continue the contract using the old model. In addition, the server equipment was reaching the end of its life cycle.

It was a good time to investigate the option of reducing the number of servers used.

A-lehdet did not have adequate in-depth knowledge of SQL Server databases, and therefore the company decided to involve an external specialist in the project. While other options were also considered, positive previous experiences, strong recommendations, and the unique capacity optimization solution made SQL Governor the logical choice.

Main benefits:

Achieving the planned savings in infrastructure costs
47% savings in licensing costs
The minimum of 30% savings in hardware costs
A server platform that is easier to maintain and more reliable
Efficiency gain from spending less time on routine tasks, and more time on more productive work tasks.

“Achieving the expected financial benefits of the project has started well, and it seems as if the rest of them will be achieved or even exceeded. Improvements in performance and time savings have also been a positive surprise, as they have exceeded expectations.”

– Pekka Kivenjuuri, CIO

Case example: Visma ensures cloud service availability and performance with an eye on future growth.

Visma Solutions provides cloud-based financial administration software. Visma was seeking a better way to right-size the server environment of one of their software to manage business growth and updates. They also wanted to know the best options for replacing physical hardware that was approaching the end of its life-cycle.

They had already trusted SQL Governor for several years to ensure platform life cycle management. SQL Governor had collected plenty of data from the platform, which could be used in the SQL Server migration project, enabling rapid kickoff.

Main benefits:

Migration project ensured the optimal capacity of the database platform
The type and size of new servers required to handle database platform growth were ascertained
The correct server capacity will ensure the availability and performance of services for the end clients
A fact-based plan resulted in clear savings in the acquisition of new servers and licenses

“If we had relied on guesswork to determine the size and performance of the new platform, we would most likely have ordered too many or too large servers. The monetary savings are considerable, as servers in large environments cost thousands – even tens of thousands – of euros. We also saved significantly on licensing costs.”

– Juha-Pekka Ström, Visma Solutions

Frequently Asked Questions

SQL Governor can have unlimited monitoring data, making it possible to forecast not only workloads in total but also the behavioral characteristics of the workloads. With SQL Governor you can easily see how how different trends and different hardware affect the workload predictions of each performance counter.

Threshold levels, technical SLA's, service time constraints and service breaks all work together to make capacity planning a very accurate and deterministic process. SQL Governor has an internationally patented, machine learning-based method to make the most out of consolidation, whether it happens on the database, instance or server level.

SQL Governor also supports most high availability workloads, hardware and virtualization platform renewals as well as transition to the cloud (AWS IaaS, Azure IaaS / PaaS)

The longer the time series, the more accurate the trend extrapolations are. The minimum recommended time series length for accurate trend extrapolation is 3 months of consecutive data. Ideally, SQL Governor runs all the time in production environment and gathers workload data for many years.

However, good results can be had with a full month of data if the following criteria are satisfied:

· The data covers the busiest business period

· The scope of the project is 1-3 years

· The workloads are not growing exceptionally fast

· The trend extrapolation is manually set

For even shorter data collection periods (one or two weeks, preferably covering the change of the month), lower threshold levels should be set along with higher SLAs for adequate results.

If your goal is to maximize savings, you should use instance-level consolidation on every SQL Server instance that can be consolidated. After that, you should use server-level consolidation on all servers that you plan to virtualize on-premises or in the cloud. You can also use server-level consolidation on SSAS, SSIS and SSRS workloads, which are not suitable for instance-level consolidation. Database consolidation is a bit more time-consuming process so you would like to do it only for a less amount of servers.

If you simply want to optimize the number of CPU cores on your virtual servers, you should use server-level right-sizing. This is a very handy to get immediate results, especially in environments with 100+ virtual machines.

SQL Server Capacity planning