Mastering Microsoft SQL Server Consolidation Planning - Part 6

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.

Non-functional requirements in consolidation planning

The most essential non-functional requirements of consolidation planning are:

  • performance
  • scalability
  • availability
  • maintainability
  • security

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:

  • buffer cache hit ratio
  • page life expectancy
  • CPU waits
  • disk waits
  • deadlocks etc.

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:

  • are dedicated and do not need to scale
  • will be able to scale up
  • will be able to scale out

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.

Get our guide to SQL Server consolidation planning

Risk analysis during the consolidation planning project

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:

  • high-level risks
  • medium-level risks and
  • low-level risks.

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:

  • prevents the project from being finished
  • increases project costs significantly (e.g. by 100%)
  • causes a significant project delay (e.g. of 100%)
  • causes a serious negative impact on the target system’s functionality

A good example of a medium-level risk is one that:

  • Increases project costs by 50%
  • causes a severe project delay of 50%
  • causes a negative impact on the target system’s functionality

A good example of a low-level risk is one that

  • increases project costs by 25%
  • cause a project delay of 25%
  • causes some negative impact on the target system’s functionality

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:

  • there is no data / metadata about all the servers and instances available
  • there is no clear picture of current service and / or licensing costs
  • there are no source hardware specifications available
  • capacity planning is done with less monitoring data than actually needed
  • there is uncertainty about certain servers / instances / databases and how fast they will grow in the future due the possible business environment changes
  • hosting company (if there is one) allows only certain series and models of host servers to be used
  • no proper DBMS diagnostics is done before the consolidation project
  • same DBMS instances are using very differently performing SAN tiers
  • bad maintenance plans

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”.

Access the next part here.

How to master the consolidation planning of a SQL Server platform 

Jani K. Savolainen
Founder & CTO
DB Pro Oy