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 wrote about consolidation project lifecycle planning, data center environment prestudy and architecture prestudy. The topic of this part is non-functional requirements and risk analysis.
The most essential non-functional requirements of consolidation planning are:
Typically, performance is measured with SLA’s such as “CPU usage should be 80% or lower 99.9% of the time”. Another way to set performance goals is to define certain wait statistics limitations in the target environment. You can also monitor certain performance counters both in the source and target environments and compare the results after consolidation. Examples of such performance counters are:
Proper index maintenance is the backbone of good DBMS performance. Remember to pay attention to this and check the current situation of index maintenance. It should be part of the diagnostics phase.
When it comes to the scalability of the system, it is a good idea to determine which of the servers and cluster:
In addition, it is necessary to define the lifecycle for each target server and DBMS instance.
Availability is very important. The criticality level of each DBMS instance should be determined in order to understand which kinds of target server and High Availability options (Always On Failover Clustering, Always On Availability Groups, virtualization HA options, etc.) are needed.
Consolidation and maintainability go hand in hand: the fewer clusters and servers you have, the easier it is to maintain them. Technical documentation is also crucial. During this phase, there should be a top-level plan to write documentation about system configuration, maintenance plans, backup routines, and disaster recovery.
On the security side it is critical to go through any policies on authentication modes, logins, passwords, users, database encryption and such.
Risk analysis plays an important role in consolidation planning. The earlier we acknowledge the pitfalls of the project, the higher the chance to avoid them – and save time and money. Risk analysis should start in the beginning of the project and should be an iterative process during the whole project lifecycle. Always pay attention to possible risks in anything you work on. Consolidation planning projects are the big budget type of projects you don’t want to mess up.
It is a good idea to divide project risks into several categories, for example:
Every customer and project have its special characteristics, but it is a good idea to define some meaningful explanation for each risk level in co-operation with the customer.
A good example of a high-level risk is one that:
A good example of a medium-level risk is one that:
A good example of a low-level risk is one that
In addition to the description and status of the risk, it is a useful to follow risk status during the project and to define a preliminary description of how to avoid the risk. Of course, some of the risks may be unavoidable.
Some typical risks of a consolidation planning project are:
The next part of this guide will be about the requirements specification phases called "Analysis of external dependencies”, "Defining architecture alternatives "and "Setting the metadata”.
Jani K. Savolainen
Founder & CTO
DB Pro Oy