In this guide, I will help my readers to overcome the challenges in Microsoft SQL Server consolidation planning.
Updated 8.6.2020.
You can read the guide in full or hop in to the part you're most interested in from the contents list below.
Contents of this Guide
Part 1: Why and how to consolidate SQL Servers
Reasons to consolidate, understanding the complex requirements, and problems in traditional consolidation planning methods.
Part 2: SQL Server consolidation projects and phases
SQL Server consolidation projects and what are the different project phases.
Part 3: Monitoring SQL Server environment
How to monitor the existing SQL Server environment.
Part 4: SQL Server diagnostics and health checks
SQL Server diagnostics and health checks.
Part 5: SQL Server lifecycle planning, prestudies on data center and architecture
SQL Server consolidation project lifecycle planning, data center environment prestudy and architecture prestudy.
Part 6: Non-functional requirements and risk analysis
Non-functional requirements and risk analysis in SQL Server consolidation projects.
Part 7: External dependencies, SQL Server architecture and metadata
Analysis of external dependencies, defining architecture alternatives and Setting the metadata.
Part 8: SQL Server cost model prestudy, capacity distribution and architecture selection
SQL Server cost model prestudy, calculating capacity distribution and selecting the desired end architecture.
Part 1: Are you planning to consolidate your Microsoft SQL Server system?
A significant proportion of IT investments in companies is allocated to the replacement, updating and expansion of existing information systems. The most common reasons for system replacement are:
- outdated software
- obsolete server infrastructure
- company merger
- SQL Server licence renewal
- seeking cost savings
MS SQL Server and savings?
The Microsoft SQL Server database platform (database servers with their software) at the back end of business-critical applications is often an extremely important element from the perspective of information system replacement and expansion projects. Optimal design and implementation of larger systems can create savings that are well into the double-digit percentages in comparison with the previous database platform investment. They also create a foundation for good performance and scalability of applications. In contrast, a poorly implemented modernization project can lead to substantial overinvestments or cause performance problems with the system, along with a shorter than planned service life.
In the design of a new environment, it is essential to understand the complex whole composed of the various business requirements, licensing and budget-related requirements, the architecture, the environment’s non-functionality-related special features and capacity requirements. This understanding forms the basis for selecting the combination that can best serve the business.
Problems in traditional SQL Server consolidation planning methods
Traditional data platform capacity planning contains a lot of time consuming manual work and is often based on rough estimates. In larger SQL Server environments, consolidation planning often takes tremendous time and binds a great deal of human resources which makes it expensive. Time-to-solution is also hard to predict. It is not rare to see consolidation planning projects containing only a few dozens of source servers but lasting easily six months or more.
It is easy to understand how difficult it is to synchronize SQL Server license renewal with consolidation implementation, especially when deadlines are hard to predict. However, most of the work effort could be shortened from months to even days with an efficient consolidation process and intelligent planning automation.
Another typical caveat is that servers are often replaced by another server in a 1-to-1 manner, which means that there are no consolidation benefits to be gained. The idea of consolidation is to reduce the overall amount of servers by grouping several source SQL Server instances or servers into fewer target host servers than existed in the source system. This typically reduces software license costs and the overall number of CPU cores needed. If consolidation occurs in a traditional consolidation project, it is rarely based on comprehensive monitoring combined with a mathematical planning method. Consolidation planning is seen more as a task for the data platform architect than a mathematical challenge. The truth is: It is both.
In cases where the source system’s data is monitored, it is often only implemented on the server level, which means there is no true understanding of the underlying DBMS instances, resource pools and databases. Instead of long-term source system monitoring only snapshots of peak values are taken. This in turn leads to a situation where capacity is estimated on a peak basis only, meaning that the long-term instance-level usage data cannot be distributed equally between the target servers.
MS SQL – what's next?
All in all, in larger production environments of up to thousands of SQL Servers, without a proper method there are just too many variables in order to manage everything manually. In the next part we will discuss SQL Server consolidation projects and what are the different project phases.
Jani K. Savolainen
Founder & CTO
DB Pro Oy