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 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
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.
ABOUT THE AUTHORS
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
This was first published in April 2009