Avoiding Roadblocks: SQL Server Consolidation

Cost Savings, Efficiency Driving Consolidation Efforts

Gridstore recently conducted a survey of over 125 Enterprise IT professionals a number of detailed questions surrounding the issue of SQL Server consolidation, including the business challenges driving their changes.

While cost reduction was the #1 response to what is driving the desire for SQL consolidation, this was tempered by the top three issues concerning respondents when it came to consolidation — Performance (74%), High Availability (51%) and Manageability (40%).

SQL Server consolidation is being driven by the need to reduce cost in over a third of cases — 34% — followed by a drive to improve efficiencies at 25% and a need to standardize at 21%.

IT agility also plays a small but significant role in driving consolidation, stated by 10% of the sample as the key business driver.

This is consistent in what we see in the field and marketplace; cost efficiency and new features enabling better performance, availability and management are driving our clients in the same direction.

sql server consolidationBut is SQL Server consolidation as straightforward as it sounds?

SQL Server consolidation involves analyzing the servers in your enterprise and grouping databases onto a smaller number of servers without compromising the performance of any applications using SQL resources.  Consolidation tends to be a high-profile activity that can be politically charged; outages can undermine the entire project. As a result, IT must closely monitor consolidated servers and be proactive about preventing downtime as much as possible.

This process will be different in every IT shop.  And with those differences come scenarios that impact each consolidation effort.  Here are some of the key challenges that, if not properly addressed, may take the shine off your consolidation efforts.

License change may not be what you expect

By consolidating multiple SQL Server databases onto a single SQL Server the CAL requirement may increase. As you decommission SQL Servers you may be able to cannibalize some of these CALs, but you will probably have to upgrade to a per-processor license. Consolidating also typically requires increased hardware resources. For instance, you may have to use dual processors, or a quad or eight-way box (with four or eight processors respectively).

If license savings is the core driver of your consolidation project, be sure to understand this impact.  It’s not to say there won’t be license savings- or hardware savings for that matter- but it is a factor where more detail is better.

Database Migration is still a migration

blog-database migrationDatabase migrations are necessary when consolidating SQL Server instances and databases onto fewer hosts.   Have you documented the databases, applications, connection strings, correlated SQL Server SSIS packages or security identifiers that will be involved in the consolidation process?  Do each of the audiences impacted by the change know what’s going on?  Be sure to go through the same steps you would in any other migration process here- a deep dive into what’s there, prioritization of data being moved, and impact on users.

Like any other migration, the end state will bring new features, capabilities and management entities.  SQL Server virtualization with Hyper-V has become an increasingly popular alternative for consolidating SQL Server instances and databases.  Same with failover clustering, which is typically recommended when consolidating many instances and databases onto a single system so that high availability can be achieved for all databases.

Although Hyper-V and failover clustering are fairly straightforward to learn, it’s important to understand all of the nuances of implementing, managing and supporting a Hyper-V infrastructure and failover cluster within an enterprise environment.

Hardware impacts cost/benefit analysis

New hardware is necessary in most scenarios when it comes to moving underutilized SQL Server instances and databases to a new consolidated platform. Since consolidated servers host larger SQL Server workloads, IT departments want these servers to scale and be more powerful than the hardware associated with underused SQL Server instances.

For example, an organization’s new consolidated system may include four quad-cores, 64 GB of RAM, 64-bit technology and the Enterprise Edition of SQL Server 2012 R2 or Windows Server 2012 R2.  Over time, the business will see significant savings- but in the short term, hardware can be a significant capital expense.

Runaway applications are even more dangerous

Consolidating to a single SQL Server may open the door to runaway applications, which eat up CPU and degrade other applications’ performance. Application or ad-hoc queries, temp table or cursor, may also cause tempdb to prevent other applications from using the SQL Server they share.  Validating applications and testing their performance characteristics becomes critical to protecting them from rogue applications.

Although there are strategies to optimize tempDB from a performance perspective, there is still the possibility of performance degradation within an instance — especially if the tempDB database is a single point of contention for all temporary workloads. IT departments that are not fully aware of their tempDB resource requirements may hesitate to consolidate.

PCI and Compliance rules could impact architecture

In many cases, regulatory compliance provides rules and regulations for how data is stored and managed within an organization. For example, mission critical databases hosting sensitive data are typically required to remain autonomous for security and management reasons. These types of databases are isolated from other databases and are not candidates for SQL Server consolidation.


There are factors IT should be aware of when consolidating SQL Server instances and databases on fewer systems. Fortunately, with proper planning, training and by understanding the potential roadblocks of SQL Server consolidations, organizations can still reduce the total cost of ownership associated with managing and operating their SQL Server infrastructures.