The Story Behind Consolidation and Right-Sizing
If you have ever needed to implement a large-scale data platform migration or consolidation project, you are definitely familiar with many of the pain points faced during the process. Firstly, manual data collection is very tricky to arrange and demands lots of work. Secondly, the planning of the capacity is a complex and detailed process – you need to take all the performance counters, their threshold levels and SLA’s, trends, different server benchmarks and such into account. When it comes to the consolidation effort, it is impossible to try to rearrange the SQL Server instances into a most optimal target server setup with manual work. All this takes a lot of time and is prone to human error.
Due to the above, you often end up doing the consolidation work based on rough guesstimates. That on the other hand often leads into situations, where it is significant over or under capacity. Therefore, you can face problems such as performance issues, availability problems, or over investment, during the lifecycle of the server platform.
Getting to Know SQL Governor
I attended the PASS Summit in Seattle as a speaker last year. While browsing the exhibition there, I met a team of SQL experts introducing automated data platform consolidation planning and right-sizing software called SQL Governor. The guys quickly explained how it works and showed me a demo. I understood that this software is an innovation bringing great help and value into SQL Server data platform capacity planning. So, I wanted to learn more about the SQL Governor.
SQL Governor has many features that make SQL Server consolidation planning fast, accurate and easy. The software collects 24/7 performance counter data from all the needed SQL Servers, instances, and databases, with a high level of detail and different aggregations. It executes automated instance level consolidation capacity planning for any number of servers. You can do server-level right-sizing for dedicated servers with a forecast of database level capacity needs, applied in terms of size and IOPS. Also, there are different kinds of heatmap presentations, graphical overview of the capacity forecasts, topology pictures of the target architecture, and so on.
The Question – How?
How does the SQL Governor software solve the automation of consolidation planning?
The software first analyzes the current data platform. It collects data of performance counters of the existing SQL Server instances, such as average and peaking CPU and RAM. The time-series data is then adjusted against given trends and benchmark data of the source servers and target servers.
There are two ways to plan the consolidation: server-level or instance-level. In case of an instance-level consolidation, based on the time-series data, the software finds out the optimal restacking of the existing SQL Server instances for the new data platform, creating a plan of the consolidated schema of lesser, bigger servers. The magic behind these calculations is an efficient machine learning algorithm. In the target platform, the instances that will be located on the same target server are not competing of the same resources at the same time.
In case of a server-level consolidation, you can drag-and-drop the existing source servers to their corresponding target host server and adjust the consolidation parameters into the desired level. Each of these source servers are created as a virtual machine and you can interactively see the forecasted performance counters in form of time-series on target server level. This way you can stack up the virtual machines into the desired level of overall capacity.
Both of these methods result in significant savings in the overall CPU capacity and SQL Server licensing costs, because the sleeping CPU cycles are being minimized on each physical, virtual or Azure IaaS server.
The Final Words – Save The Cost
Basically, the software not only takes away the major pain points I have faced in the capacity planning in migration and consolidation projects, but it also reduces the planning time from months to days! For example, the right-sizing functionality enables the calculation of a consolidated server from many source servers in real-time, and the automated instance-level consolidation can crack the optimal arrangement of the SQL Server instances with significantly less work than if done manually. Also, the forecasts for the database sizes and IOPS consumption become very handy when planning the storage part of the system. One of the features I especially like in the software is the comparison ability of the capacity utilization before and after the migration, and the capacity management for long-term and short-term workloads. It really makes the results of the migration project concrete.
In my opinion, SQL Governor is the software you cannot live without if you are migrating large numbers of SQL Servers, from 10 servers up to even 1000 servers or more. I really recommend you take a deeper look into this product at the SQL Governor website.
Reference: Pinal Dave (https://blog.SQLAuthority.com)
