I have recently bumped into some differences in opinions on SQL Server consolidation. Some are against it, and some are very much into it. It is important to understand the arguments why some people are against it. Then we can evaluate how realistic the beliefs against SQL Server consolidation really are. And on the other hand, we can talk about the lessons learned from the real-world SQL Server consolidation cases, the ones we have done with our Governor capacity planning software.
Misbelief #1: Do not stack the SQL Server instances, because of vulnerability on performance and availability
Often there is a misbelief that if more than one of the SQL Server instances are put to same server, they start to compete on the same resources and therefore easily lead into out-of-capacity situations. Well, this can be the case in some scenarios, but on the other hand, we know that the great majority of all the SQL Servers have very characteristic behavioral patterns over time when it comes to hardware resource consumption. Very often there are the same CPU peaks, same quiet and busy times during each hour, day, week, month and year. When doing the consolidation, we should learn from the instance level hardware resource consumption which instances to re-stack for each target server so that they are not at all or minimally competing of the same hardware resources at the same time. This actually makes it possible to have a harmonized, stable server level setup with plenty of instances installed on the same target server.
Misbelief #2: Consolidation leads to high server level safety margins, and thus doesn’t generate any savings
In a well-planned consolidation case, it is possible to calculate the overall capacity needs for the life cycle of the new data platform, such as 3 years, by extrapolating the instance level performance counter time series based on a given growth trend and CPU performance ratio over time. Based on these facts, we can set reasonable threshold limits for the target servers, so that even if some arbitrary conflicts between different SQL Server instance workloads would occur, there is a safe buffer for example for peaking CPU workloads. It is possible to do this without the necessity to invest into extra SQL Server licensing, because the newer hardware generation is always faster than the old one – actually so much faster that still great savings may occur in overall licensing costs. This is especially the fact when re-stacking and distributing the instances in an optimal way into the target servers, which I described in the previous chapter.
Misbelief #3: There is more maintenance work in a consolidated data platform
In many aspects of the maintenance of SQL Server environment, this is actually not the case. The good sides in consolidating SQL Servers from many servers into a few in general is that there are significantly fewer servers to maintain. This leads into a simpler administration model. The backup maintenance will come easier and more straightforward as well. What comes to the patching of the platform, it is true that there is a little drawback, because more SQL Server instances are being updated at the same time, but on the other hand patching is always a time-consuming operation. When you design your SQL Server platform with a nice High Availability solution, you can reduce downtime to a minimum in your Windows clusters, which is a small price to pay for getting up to 50% savings potential in SQL Server software licensing, hardware and service costs.
Misbelief #4: Consolidation is tedious and cumbersome work to do
Automation is the buzzword of the day, also in migration. There exists great software, such as dbatools, for efficient SQL Server instance level migration implementation. This makes it very easy to move the instances between the source and target data platform during the consolidation project. What we have learned from customer cases is that when external dependencies are distinctly defined, it is easier to orchestrate the needed application level changes in the migration roadmap. It is often a good idea to split the project into smaller deployment chunks. You can read more about the requirements specification process in SQL Server consolidation project in my previous blogpost.
The great economic benefits from SQL Server consolidation
When consolidating the SQL Servers, it is not rare at all that the number of servers to be maintained drop down to one third of the original number of servers. Thus, we will definitely save good money not only in OS and SQL Server licensing, but in the hardware and platform maintenance costs as well. There is a savings potential in the level of millions for companies that have a lot of assets in the SQL Server licenses. And on top of this, the world will get a little greener due to the reduced carbon footprint!
What we have learned from our customer projects
There is no reason to be afraid of consolidating large-scale SQL Server environments. By addressing all the risks and systematically analyzing the whole source data platform we are able to create a well maintainable target data platform and produce significant savings – without sacrificing the performance or availability!
Want to master SQL Server Consolidation Projects? Download our guide below.
Jani K. Savolainen
Founder & CTO
DB Pro Oy
P.S. You can also read my previous blogpost about Why DBAs need mathematics and machine learning for SQL Server capacity planning.
