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:

1. Virtualization: using a single physical machine to host multiple virtual machines (VMs) running Microsoft® SQL Server® data management software
2. Multiple Instances: using a single machine to host multiple SQL Server instances,
3. 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: 

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

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.

• SQL Server is currently limited to a maximum of 50 instances per operating system environment (physical or virtual).

• Hyper-V has a limit of 64 VMs per node and a SQL Server instance has a limit of 32,767 databases per instance

next  blog , how to use MAP tool to assess your environment readiness for SQL consolidation  :

http://blogs.technet.com/b/meamcs/archive/2012/09/24/how-to-use-map-tool-microsoft-assessment-and-planning-toolkit.aspx

References: http://msdn.microsoft.com/en-us/library/ee819082.aspx