Problem solve Get help with specific problems with your technologies, process and projects.

The challenges of SQL Server consolidation

Despite the benefits of SQL Server consolidation, some IT pros are reluctant to make the move. Learn which scenarios could pose a challenge to those looking to consolidate.

Organizations often useconsolidation to drastically reduce operating and capital expenses while increasing the scalability and availability associated with their SQL Server infrastructures. Although it would seem natural for most organizations to welcome consolidation, many DBAs are not taking advantage. This is frequently because they lack a basic understanding of consolidation and even harbor a lot of misconceptions about it. This article examines some of the reasons organizations put off consolidating SQL Server instances and databases.

Database migration

Looking for more SQL Server consolidation info?

Check out our consolidation fast guide for more resources and expert advice.

Database migrations are necessary when consolidating SQL Server instances and databases onto fewer hosts. A database migration involves moving SQL Server databases and all objects associated with the database. Some organizations show a reluctance to conduct a migration of SQL Server data from a source SQL Server instance to a target consolidated SQL Server instance.

Often this reluctance is related to the IT staff's knowledge and familiarity with the databases, applications, connection strings, correlated SQL Server SSIS packages or security identifiers that will be involved in the consolidation process. Ultimately, these organizations choose not to consolidate because they realize the risk of failure increases when they are unable to fully understand, plan, drive and manage a migration of this magnitude.

Hardware procurement and budget concerns

Although consolidation may make a good deal of sense, 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.

In the present state of the economy, however, IT departments may not have the budget to procure new hardware or enterprise licenses for scaling up. 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 2008 or Windows Server 2008. Not a cheap project. These budget challenges are forcing more and more organizations to place their SQL Server consolidation projects on hold. Hopefully, the estimated return on investment (ROI) is a good enough business justification to substantiate consolidation and the expenses associated with it.

Technology learning curve

While many IT departments and DBAs have heard about the benefits of virtualization, there is a reluctance to move forward as they don't feel armed with enough knowledge to take it on.

SQL Server virtualization with Hyper-V has become an increasingly popular alternative for consolidating SQL Server instances and databases. While many IT departments and DBAs have heard about the benefits of virtualization, there is a reluctance to move forward as they don't feel armed with enough knowledge to take it on.

A lot of IT pros are unfamiliar with failover clustering technology as well. Failover clustering 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 departments may be reluctant to move forward until they understand all of the nuances of implementing and managing a Hyper-V infrastructure and failover cluster within an enterprise environment.

Regulatory compliance and database autonomy

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.

TempDB resource contention

The tempDB system database is a shared global resource for all databases residing on a SQL Server instance. TempDB databases store temporary workloads from all databases within an instance. It is worth noting that for each instance of SQL Server there is only one tempDB database.

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.

Resource Governor boundaries

The Resource Governor is a new tool for SQL Server 2008 that manages SQL Server workloads and system resource consumption. You can manage resources associated with consolidated workloads within an instance by specifying limits on CPU and memory consumption. It's a great new tool and should be implemented when consolidating databases to prevent runaway queries from negatively impacting database performance within a SQL Server instance.

Take note that the Resource Governor works at the instance level and is only applicable to elements within the Database Engine, so some DBAs hosting many instances of the Database Engine and SQL Server Analysis Services (SSAS) are concerned that internal elements across a Database Engine and SSAS instances cannot be managed from a holistic perspective.

Indeed, there are factors that DBAs should be aware of when consolidating their SQL Server instances and databases on fewer systems. Fortunately, with proper planning, training and by understanding the potential roadblocks of SQL Server consolidations, organizations and DBAs can still reap the benefits of SQL Server consolidation in order to reduce the total cost of ownership associated with managing and operating their SQL Server infrastructures.


Ross Mistry is a principal consultant at Convergent Computing, a best-selling author and a SQL Server MVP. He installs SQL Server, Active Directory, SharePoint and Exchange Server software within Fortune 500 organizations in the Silicon Valley. His SQL Server and SharePoint specialties include high availability, security, migrations and virtualization. You can follow him on Twitter @RossMistry. 

Shirmattie Seenarine is an independent technical writer with more than 10 years of experience. She has contributed to many books, including Windows Server 2008 Unleashed, Exchange Server 2007 Unleashed, SharePoint Server 2007 Unleashed and SQL Server 2008 Management and Administration.

Dig Deeper on SQL Server Migration Strategies and Planning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.