SQL Consolidation Planning and recommended practices

What is SQL server consolidation

it can be defined simply as reduce the number of physical SQL servers, by migrating/moving different SQL databases running on different servers into one high performance server machine.

Early in the process of a consolidation project, you will create a profile to help identify which applications are good candidates for consolidation. Then you can identify the applications that fit this profile.

Some general traits that make an application a good candidate for consolidation are:

low machine resource utilization
Moderate performance requirements
little active development
low maintenance costs.

Consolidation strategies

SQL server consolidation can be achieved mainly through one of the below strategies:

Virtualization: using a single physical machine to host multiple virtual machines (VMs) running Microsoft® SQL Server® data management software
Multiple Instances: using a single machine to host multiple SQL Server instances,
Multiple databases: using a single instance of SQL Server to host multiple databases

Each of these strategies has different advantages and disadvantages related to security and compliance requirements, high availability and disaster recovery requirements, resource management benefits, level of consolidation density, and manageability tradeoffs

Consolidation projects are typically started to achieve specific goals such as creating room for new servers or reducing operating expenditure. These goals can be broadly grouped into the following categories:

Lack of space in the data center: reducing the physical space required to host applications
Reducing costs and improving efficiency: hardware will run closer to capacity, reducing inefficiencies and allowing for fewer machines
Standardization and centralization:Place various databases in a centrally managed system, better audit control .
IT agility: Moving application into newer hardware, these application can take advantage of the newer technology and improved performance of these machines.
Green IT: computers and fewer idle machines result in lower power consumption and a reduced need for cooling

Consolidation Strategy Check list

when choosing one of the consolidations strategies each of which has its cons and pros, in the below checklist im trying to list the main concerns to take care of , these concerns can be captured from different views as the following :

Security
High availability and disaster recovery
Resource management
Density
Manageability

answering the below checklist will help you to decide the best strategy for your environment:

Category	Check item	Yes	No
Security	Share SQL service account for all applications (dbs)	Database	Instance, VM
Security	Do you need to isolate system admins (e.g. sa)	Instance , VM	Database
Security	Do you need to isolate local windows admin account	VM	Instance, database
Security	Do you seek isolation on SQL binaries’ and patching for each application DB ?	Instance, VM	Database
Resource management	Full software & hardware resources isolation , Hard limits on CPU and memory usage set per application	VM, Instance-partially	Database
High availability	Applications (DBs) can be moved to different HW without windows restart or downtime	VM	Instance, database
Resource management	Isolation of tempdb, one tempDb for all applications (dbs)	Database	Instance , VM
Resource management	Isolation of server level objects (credentials, linked servers, msdb, SQL Server Agent jobs, and so on)	VM, Instance	Database
Density	Best performance when same hardware is provided	database	VM, Instance
Manageability	Reduces number of physical servers to maintain	Instance, database	VM
Manageability	Reduces number of Windows installations to maintain	Instance, database	VM
Manageability	Reduces number of SQL Server instances to maintain	database	VM, Instance
Manageability	Are you seeking reduced management overhead and licensing cost	database	VM, Instance

Recommendation when planning for SQL consolidation

Use the MAP tool to assess the current environment and plan for consolidation. in my next post im going to talk in details how to use the MAP tool and analyze its results, you can download the tool form here.

Performance considerations
- If multiple applications are consolidated as databases and these have dependencies on tempdb, I/O bottlenecks on tempdb can cause performance issues, assess your disks IOPS and allocate dedicated physical disks to tempDB.
- Virtualization: we generally recommend using a fixed size virtual hard disk (VHD) or a pass-through disk because dynamic VHDs can cause additional I/O overhead.
- Virtualization: Use SLAT processors incase of virtualization , the VMs will perform better especially when increasing number of VMs .
- Virtualization: If an application has very strict security requirements, it is an ideal candidate for a virtualized approach to consolidation because the virtual machine has almost the same security isolation options as if the application had a dedicated physical host.
- Virtualization: One virtual processor is recommended to be mapped to one physical processor.
- Instance-level and database-level consolidation options provide direct access to the consolidated server’s physical hardware, which may help scalability by providing support for hot-add CPU and memory

SQL Server provides the max server memory and CPU affinity mask settings to set limits on how much memory and how many logical processors the SQL Server instance can use..
Database-level consolidation provides the lowest overhead, because all other resources are shared with the other databases on the single instance.
Plan for Network bandwidth thoroughly: Another factor to consider when planning for consolidation is the impact on the application’s network and I/O latency, because both the network and storage resources become shared as part of consolidation.

CPU sizing
- Newer processors may reduce the need for the application to use as many processors as it previously had.
- look at all applications that significantly under-utilize CPU, pick the one that utilizes the most processors, and take that number of processors as a base.
- You should always leave room for peak performance or application usage growth. Targeting approximately 50 percent utilization is a good starting point.

Density: which is the number of databases that can be placed together in one instance or server, In terms of Performance , database can demonstrate the best throughput and fastest response time when hardware is baselined

Consolidation method	Number of applications	Throughput	Response time	Host system CPU utilization
Baseline (old hardware)	1	100%	100%	6%
Virtualization	24	+0.8%	80%	24%
Instance	24	+0.6%	58%	20%
Database	24	+0.9%	53%	16%
Virtualization	40	+0.6%	95%	45%
Instance	40	+1.1%	73%	37%
Database	40	+1.3%	55%	34%

Table : sample Density results based on throughput (higher is better) and response time (lower is better) across options

Scalability
- Its important to check what is the scalability room of current hardware (scale up RAM , CPU , available PCI Network slots , Storage)

Security
- Instance-level consolidation provides an additional layer of protection, because the binaries and the SQL Server logins are separate, but the instances still share the same Windows accounts and operating system configuration. At the instance level, we recommend that you use different service accounts for each instance to reduce security risks

High Availability
- All three approaches can leverage the various high-availability features built into SQL Server such as failover clustering, database mirroring, and replication
- SQL Server failover clustering is the high availability solution, database-level consolidation may not be the best choice, because failover will occur at the instance level. If you have applications that are consolidated at the database level, these applications will need to rely on health monitoring based on the entire instance failing over. However Virtualization or dedicated hardware may be the best choices in this scenario.

Limitations and Boundaries

Virtualization Host requires hardware-assisted virtualization support (Intel VT or AMD-V) .
Virtualization Host requires hardware data execution prevention (DEP, also called Intel XD bit and AMD NX bit).
HW resources in virtualized environment will be allocated to the VM regardless of whether or not they are fully utilized. In addition, the guest operating system of the VM itself will consume some overhead of the allocated resources, and the host operating system will also require an additional allocation of resources although these are generally relatively small.