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.
Now, we are going to discuss SQL Server consolidation projects and what are the different project phases.
SQL Server consolidation project
A consolidation project entails transferring a larger environment comprising several SQL servers into a new environment. The initial system consists of a number of applications and servers scattered across various locations. It may include several SQL Server versions with thousands or more databases. High-availability solutions may be in use.
It is often sensible to centralize the services with a single data center and simplify the structures and versions of the SQL Server services. This means reducing the number of servers to an optimal level. Determining the required CPU, disk, and memory capacity is extremely important. Addressing this issue, our SQL Governor® product automates the process and improves its efficiency.
The consolidation process for an environment with several SQL Servers resembles the somewhat less complicated consolidation of a single server but requires iteration and paying attention to consolidation needs and possibilities.
Choosing the consolidation planning strategy
There are three different types of consolidation planning strategies: server-centric-, instance-centric and database-centric. All the strategies typically support server virtualization. In server-centric approach, the idea is to consolidate source servers as virtual machines under the target host server by reducing overall target host server count compared to the count of source servers. In an instance-centric approach, the DBMS instances are the building blocks of the new architecture, whereas a database-centric approach separates databases from their hosting instances.
Our approaches are server-centric and instance-centric consolidation, which are ideal choices for environments with a large amount of host servers, instances and databases. A database-centric approach functions well in smaller environments. However, database-centric approach often demands extra work and may become too complex and time consuming to implement in large production environments. This is because the SQL Server Agent jobs, Services, Maintenance plans, User accounts, Proxies, SSIS packages, Database Mail, Resource Governor, etc. reside on the SQL Server instance level and services like SSRS, SSAS, SSIS, DQS and MDS reside on server level.
Another problematic aspect in a database-centric approach is the growing uncertainty of individual user database performance forecasting. How will a given user database perform in the new environment with other user databases? There is no simple answer because tempdb performance is hard to predict. It interoperates with user databases that never existed under same DBMS instance before. This generates a different kind of overall workload that is not trivial to trace.
Consolidation planning project
SQL Server consolidation planning is an iterative process. It needs input data from various sources such as the architecture, servers, software, licensing, services, versions, and source system monitoring data. All this affects the final design of the consolidation model. It is a good idea to divide the project into two subprojects: the requirements specification and the detailed specification. This lowers the risks in the implementation phase and improves the overall quality of the project delivery with a more optimal TCO.
Migration planning – Requirements specification
The consolidation planning process requirements specification is described below. Some of the phases of the project are done in parallel and some of the phases use feedback information from each other. The purpose of the requirements specification is to gather the high-level requirements for the overall consolidation project by answering the question: what. In our method, what is different from traditional capacity planning, is that already in the end of the requirements specification the actual implementation of capacity calculation is done. I will go through each of these phases later in this guide.
Consolidation planning – Detailed specification
You can find a diagram of the process for producing the detailed specification below. Suffice to say here, the purpose of the detailed specification is to describe how exactly the consolidation implementation will be done. We will get back to these phases later in this online guide.
SQL Governor consolidation planning solution
With our patent pending method and our SQL Governor® software it is possible to improve the consolidation planning process through significant overall cost savings. SQL Governor is a unique, easy-to-use, domain-independent solution for efficient database consolidation capacity planning, monitoring, diagnostics, alerting and capacity management in a Microsoft SQL Server DBMS environment. With SQL Governor and our unique method it is possible to:
- Calculate an optimal server topology for your target architecture based on efficient time series analysis and machine learning.
- Save up to 30-50% in license and server investment costs compared to your existing data platform.
Interested in SQL Governor? Please contact us for more information about the product!
The next part of this guide will be about the requirements specification phase called "monitoring the existing environment”.
Jani K. Savolainen
Founder & CTO
DB Pro Oy
