Why SQL Server Consolidation Matters?

The estimated reading time for this post is 12 minutes

The Evolution of the Microsoft Data Platform

Before we get into SQL Server 2014 I want to start off by showing you how far the Microsoft Data Platform has come in the last decade. Many of you may be still be using SQL Server 2005 or SQL Server 2008 running on Windows Server 2003 or 2008 and may still have the perception that SQL Server is a good Tier 2 and Tier 3 database, but not ready for my mission critical tier 1 applications. Well, both SQL Server and Windows Server have come a long aways when it comes to tackling the largest mission critical applications. With SQL Server 2012 in introduced a comprehensive set of mission critical capabilities across performance with in-memory capabilities for data warehousing in addition to in-memory analytics, security, a zero transaction loss high availability with AlwaysOn. SQL Server 2012 also brought to market one of the most comprehensive BI platform for both the IT implementers and business users with Data Quality Service and Power View in Excel. SQL Server 2014 is all about differentiation and leap frogging the Tier 1 data platform vendors like Oracle and IBM with breakthrough performance via the 3rd release of in-memory technology built-in to the SQL Server, called In-Memory OLTP. With SQL Server 2014 you can uniquely speed transaction, queries and analytics as well as throughput, we’ll talk about this in more detail in just a little bit. SQL Server 2014 also leverage fantastic new capabilities in the Windows Server 2012 and 2012 R2 to provide predictable performance and scale for your tier 1 applications, with technologies like Nic Team, Storage Spaces, SAN like intelligence built right into the OS. Finally SQL Server 2014 taking the hybrid cloud platform introduced in SQL Server 2012 to the next level with new compelling scenarios for your on-premises SQL Server applications, like simplified cloud backup, cost effective disaster recovery for your SQL Server applications on premises. So as you can see SQL Server 2014 is no longer the database for your tier 2 applications, we are providing differentiation across mission critical, BI and hybrid cloud for the largest applications. So let dig into each of these areas and take a look at the innovation we are delivering in this release.

Consolidation 1

Reduce your cost and improve your agility with SQL Server Consolidation

I want to bring everyone on the same page on the definition of consolidation that we will use for this discussion Its about reducing the number of physical servers. Reduce the number of physical servers in the enterprise by rationalizing applications on underutilized servers on fewer server.

Consolidation 2


Reduce your capital expenditure

The first benefit of consolidation that I am going to talk about is Reduction in capital expenditure. But before I do that let us spend a few minutes looking at some of the trends that has lead to this followed by how does that leads to increase in capital expenditure.

sql server consolidation

So how did we get here?

When there was request for a new app, the conventional way of provisioning was get the approval, buy a server, install all the software including OS and then install your application. This lead to a 1:1 mapping. With the growth in application there were growth in server leading to server sprawl.

The other issue was there were no standardized procurements. Hardware were procured when it was needed. These came from different vendors with whatever types, configuration and technologies that were available at that point time. This lead to a heterogeneous environment with different types of hardware. The hardware refresh cycle was not standardized and it resulted in capital expenditure every year.

Capital Expenditure

sql server consolidation

On an average enterprise upgrade their hardware once every 3 years what we call the hardware refresh cycle. During the refresh cycle if you have more number of servers then you have to incur higher capital expenditure upgrading them.

If you have more types of hardware then you want to get the latest hardware of the same type because you are not sure how a different type of hardware will impact an existing application. That means you do not get the bulk discounts and you have to deal with multiple vendors that drive up your cost. Also different types may have different infrastructure, licensing, skill needs. More servers means you may run out of space. You may have to go for new data center with the associated energy, cooling, real estate and other expenses.

Consolidate and Reduce Capital Expenditures

sql server consolidation

If you have less servers then every 3 years when you refresh you have less hardware to refresh bring.

If you have fewer types then the effort and complexities of upgrades are lower. You can get better bulk discount bringing down your Capex. Cost of training, interchangeable use of server parts. The reason SW uses one type of planes. Instead of having capex expense every year depending on the amortization now you can get into a regular rhythm of 3 years.

Less servers implies you do not have to provision new datacenter for growth. New applications can be accommodated in the existing servers. Optimized use of existing data center will lead to reduction in energy, cooling cost etc.

Reduce your Operational Expenditure

The second benefit of consolidation is reduction in operational expenditure. Like the previous section let us first look at some of the trends that has resulted in where we are today followed by why that leads to increase in operational expenditure.

Low Server Utilization

In the last twenty years or so the use of IT for solving business problems has grown. This had led to growth in the number of database applications. However during this time the hardware or the computing infrastructure has grown faster. As the hardware gets refreshed you get higher performing hardware. However the demands on those hardware has not gone up. This has resulted in underutilized hardwares.

sql server consolidatio

Effort and Complexity of Management

Now, we also saw an increase in the number of database applications for the same time span, much more than # of DBAs. The obvious effect of this is that we have overburdened our administrators.

sql server consolidation

Operational Expenditure

Low hardware utilization implies non optimal use of your investment. You are not utilizing the hardware to the extent it is capable of leading to poor returns on investments

If I may define DBA productivity = number of apps per DBA then the fact that there are more physical servers to manage leading to more environments to manage. This adds overheads and makes the environment more complex leading to lower DBA productivity

With server sprawl some of the servers will be managed whereas others will be unmanaged. The unmanaged servers are not set-up for HA and typically does not involve planning my DBAs. Providing HA on all these servers is very high as many will be on DAS etc. also it is complex to manage the HA on so many environment with varied SLAs

sql server consolidation

Consolidate and Reduce Operational Expenditures

More apps per server implies there is higher demand on the more powerful computing powers available with the latest servers leading to higher utilization and better ROI.

Fewer servers implies less variation in the environment and lesser overheads. That means DBA can manage more apps leading to higher DBA productivity.

Since there are now less servers for the same number of applications many of these servers can be provisioned on SANs. Technologies such as compression helps. Most of the apps are now managed apps and HA is planned considering the fact that multiple applications get affected if there are failures. Thereby cost of HA per apps goes down and most of the applications get some levels of managed HA.

sql server consolidation

Improve your responsiveness to changes in business

Consolidation is also a forward looking movement, aimed at building an infrastructure that is agile and dynamic that can respond instead of reacting with facilities for load balancing and dynamic provisioning and overall standardization of services for greater consistency.

Static Infrastructure

sql server consolidaton

With the legacy infrastructure which I have termed as static when we require a new application we go through the whole nine yard from approval to vendor selection to installation that leads to long lead time which has business impact such as loss of revenue.

Whenever there is growth with an application i.e. more data or more users or more transactions, static infrastructure leads to a poorer experience in terms of throughput and performance. This spike may be transient as well.

Since there so many servers to manage there is/are not established standardized SLAs each application may have their own unpredictable SLAs.

Consolidate and Increase Agility

sql server consolidation

With a consolidated environment and concept of a shared infrastructure, provisioning for new applications are faster and easier. The hardware for most cases already exists. The OS and other software including management infrastructure is already in place. In most cases it is creating a VM from an image, a new instance or a DB within a instance. This is done much faster than what was possible earlier.

Applications may have transient or permanent increase in load. With a consolidated infrastructure additional infrastructure can be creates on demand to take up the extra load or balance the loads.

Forces Driving Consolidation

So how did we get here?

If you look at what is going on in the IT for the last couple of decades, there is a steady increase in the number of DBAs, which is only natural considering the growth of the database industry.

Now, we also saw an increase in the number of database applications for the same time span, much more than # of DBAs. The obvious effect of this is that we have overburdened our administrators.

At the same time, hardware vendors have kept themselves busy upholding Moore’s law of computing, where there has been a significant increase in computing capacity, leading to a sprawl of underutilized hardware.

So this is the mismatch that got us here. So we see consolidation as a movement from such an under provisioned environment to an integral and holistically responsive environment. I intentionally call consolidation as a movement, because its much more than a project, we see this more as a continuous exercise of optimizing your infrastructure to achieve three goals:

  1. Reduce your capital expenditure – which means you upgrade and standardize to fewer hardware thereby saving on space, power and thermal costs
  2. You also want to reduce your operational expenditure – which is to say that you want to improve your hardware utilization efficiency, management efficiency – managing fewer server, and also reducing complexity and cost of HA.
  3. Consolidation is also a forward looking movement, aimed at building an infrastructure that is agile and dynamic that can respond instead of reacting with facilities for load balancing and dynamic provisioning and overall standardization of services for greater consistency.

SQL Server Consolidation Options

sql server consolidation

As you can see, we have a variety of consolidation approaches for SQL Server.

Typically as you move from right to left, as isolation goes up, density goes down and operational costs goes up.

On the two ends of the spectrum are your IT managed environment on one end –

Think of your mission critical IT managed system running 256 logical processors,

and on the other end of the spectrum, we have schema based consolation (which means you consolidate multiple apps to use a single schema), obviously this is very intrusive to the application code paths, and may not be even possible in some cases. However if possible gives you great density.

For this section, we won’t focus on either of these approaches, but lets briefly look at the rest of the options, which are utilized widely. One thing to note as we go through an overview of each of these approaches is that blend approaches exist and are viable (for instance running multiple instances within a VM).

sql server consolidation

Database consolidation is where we take multiple apps each pointing to their own databases running on their own instance/server and move them to a single SQL Server instance. To pursue this option, you want to make sure that these consolidation candidates have common security, manageability and compatibility models. you may have to make changes in your application and scripts/jobs to migrate to the new instance.

Benefits of this approach are lower manageability costs (since we reduce the number of instances) and better resource isolation and allocation facilities through resource governor.

sql server consolidation

In instance level consolidation, we move multiple instance originally running in their own physical server to a single physical server. You want to make sure that the peak resource utilization of all instances don’t exceed the capacity of the new server, in fact its advisable to leave some headroom.

Benefits of this approach are that you get full schema and security isolation, while there maybe issues with namespace and role conflicts. Tools like WSRM can be used to manage instance resource utilization. Often we’ve seen that that system resources are the density limiters.

sql server consolidation

We move multiple database apps each originally running on their own physical server to a virtual machine.

This options is very appealing due to the strong isolation it provides between application. The ability to scoop up your old environment and preserve that in a consolidated environment is also interesting, especially with the ease of capture and migrate, which in itself gives an out of box HA.

However in this approach, we do not reduce the number of OS images to manage and there is a slight increase in the resource usage.

Comparing Consolidation Approaches

sql server consolidation


  • Virtualization: dedicated OS.  Leverage OS level features such as BitLocker, EFS, ACL’s in addition to SQL Server features such as granular permissions, TDE, and auditing.
  • Instance: Can still leverage OS features, need to be careful about which ones.  Make sure isolated by service account.  Still leverage all SQL Server features.
  • Database: Potentially still leverage OS features, all apps share same service account.  Can leverage some SQL Server features.
  • Hard dependencies on system objects such as logins or server roles can be problematic.

Resource Management:

  • Layers of isolation
  • Virtualization: dedicated resources to VM, can also leverage Resource Governor within the instance.
  • Instance: leverage WSRM, CPU affinity, and max server memory settings, can also leverage Resource Governor within the instance.
  • Database: Resource governor
  • Scale with hardware – hot add CPU/memory for Instance/Database
  • Hot add storage for all approaches
  • Keep in mind global resources such as TempDB which may cause contention


  • Virtualization has overhead for both host and guest OS
  • Instance: overhead for each individual instance, 50 instance limit
  • Database: least amount of overhead


  • Very low cost for virtualization through P2V utility and Live Migration capabilities
  • Also possible for Instance/Database if database is self-contained
  • Easy to clone environments and deploy

High availability:

  • Leverage Hyper-V R2 Live Migration capabilities for zero downtime.
  • Be careful using SQL Server Failover Clustering with database migration as failover unit is instance.
  • All three options can leverage DBM, replication, etc.

Case Study – Microsoft IT Consolidation

sql server consolidation





Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:

Leave a Comment

Be the First to Comment!

Notify of