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.
In the previous part, I described the topic of SQL Server diagnostics and health checks. The topics of this part include consolidation project lifecycle planning, data center environment prestudy and architecture prestudy.
When to consolidate Microsoft SQL Server
It is inevitable that the 5-year-old server is not performing as well, as new servers. Also, the older the server gets, the more vulnerable it will be to hardware related problems. Production environment server stability is inversely correlated with the age of the current deployment.
Another concrete reason for renewing the server infrastructure is the SQL Server licensing. After the current licensing period ends, it is a good time for a SQL Server version upgrade, hardware renewal and SQL server consolidation.
It is recommended to identify any servers that can be left out of the consolidation project scope, for example servers that are:
- relatively new servers; or
- near to the end of their planned life cycle.
Another consolidation constraint for a certain standalone server or server cluster may be
- the amount of estimated consolidation work; and
- its external dependencies.
Sometimes there are a few servers with such an old version of SQL Server with some legacy applications running that there is no sense or possibility to upgrade to the current SQL Server version.
According to my experience, existing SQL Server infrastructures are typically not as consolidated as they should be. If no consolidation is applied for the next SQL Server licensing period, it can easily lead into extra costs of up to 60% for that period. It would be ideal to start the consolidation planning at least one year before the actual end of the licensing period, but often lesser time like 3 to 6 months is just enough to collect some continuous monitoring data, and to proceed consolidation requirements specification and capacity planning for environments with up to 100 source servers.
Data center environment prestudy
In this phase, we get an overall understanding of the existing production environment. It is important to have enough knowledge of the actual production environment, and whether it is going to change in any way.
The main tasks are:
- data center environment analysis
- network and domain model analysis
- hardware analysis
- SQL Server architecture and storage analysis
- licensing, service model and SLA analysis
- upgrade path analysis
These tasks cover issues such as the logical architecture in the data center, and which kind of networks, servers, and hardware it’s running. It is good to know the data systems, clients and data movement between them because this could actually affect how the existing instances are stacked on the new servers. The domain model and how the user rights are managed is important as well. Knowing your existing hardware is very important because it makes it possible to benchmark existing servers and compare them to planned ones.
The upgrade path for SQL Server versions and editions is very important because it may generate some recessive extra work and testing effort for the actual consolidation implementation. It is actually often one of the recognized risks for the success of the overall consolidation project. Upgrading SQL Server version may also bring in some potential performance issues because the engine is optimized in a different way for each version of the SQL Server. Communication with third parties is important in order to recognize which instances have to be and are able to be upgraded, and which SQL Server versions are still under Microsoft support.
Another task is to find out who owns the servers, other hardware, and licenses, and which kind of hosting service model you have. This makes the budget of the project and saving potential easier to manage and understand.
Architecture prestudy
It is important to comprehend the criticality level for each server and SQL Server instance. In addition to this, to make the target system architecture planning and capacity calculation more effective, there must be a clear understanding of service level agreements for uptime, CPU usage, and such, for outlier management of the monitoring performance counter time series data.
There is no better way to plan a new target architecture than learning the pros and cons from the old one. The SQL Server architecture and storage analysis is a review of the current implementation model of SQL Server installations: the Always On clusters, Active – Active, Active – Passive, and Standalone server configurations, and which storage tiers are being used with them.
It is important to streamline storage tiers on SQL Server instance basis in order to make overall instance performing steadily, not to forget having the best storage tier available for tempdb usage. Remember to find out which kind of SQL Server maintenance job routines and backup policies you have, to understand the wholeness of SQL Server resource consumption over time.
The next part will be about the requirements specification phase called "Non-functional requirements and Risk analysis”.
Jani K. Savolainen
Founder & CTO
DB Pro Oy
