This content is part of the Essential Guide: Hardware for SQL Server: Optimizing performance
Manage Learn to apply best practices and optimize your operations.

SQL Server consolidation strategies and best practices

Several consolidation options are available for managers and DBAs who are looking to cut costs. Learn about some of these strategies and when to use them along with details on the benefits of SQL Server consolidation with Windows 2008.

In the current economic climate, many IT departments have been asked to do more with less in an effort to reduce costs. This is especially true in large organizations with SQL Server infrastructures, where IT staffs often find themselves running many underutilized SQL Server installations. An underutilized SQL Server can result in increased hardware and management costs, lack of standardization and other challenges associated with business continuity.

Fortunately, by using SQL Server 2008 on Windows Server 2008, organizations can successfully address the issues associated with underutilization by consolidating SQL Server instances and databases onto fewer physical or virtual servers.

One of the first steps in SQL Server consolidation is to identify installations running on underutilized hardware. For example, SQL Server instances and databases using less than 30% of their hardware's processor, memory and disk space are excellent candidates for consolidation.

With numerous options available, organizations can choose a consolidation approach that aligns with their business model and day-to-day operations. Strategies associated with SQL Server consolidation include:

  • consolidating multiple databases on a single SQL Server instance
  • consolidating SQL Server instances on fewer hosts
  • consolidating SQL Server via virtualization

Consolidating multiple databases on a single SQL Server instance

Databases residing on underutilized hardware can be consolidated onto a single SQL Server 2008 instance running on Windows Server 2008. This strategy is also referred to as instance consolidation.

The main benefit of this approach is the drastic reduction in total cost of ownership (TOC). Through consolidation, organizations have an opportunity to decommission legacy SQL Server instances and physical servers, leading to a decrease in management and financial obligations. It is worth mentioning, however, that consolidating too many databases onto a single instance can bring about performance degradation and resource contention. Therefore, organizations should size their workloads and hardware appropriately.

Consolidating SQL Server instances on fewer hosts

Some organizations might be prevented from consolidating all of their databases onto a single SQL Server instance. Below are examples of situations where organizations may need to maintain more than one instance:

  • There is only one tempdb database for every SQL Server instance. If too many databases reside within a single instance, then the tempdb database may be a single point of contention for all temporary workloads.
  • Regulatory compliances habitually identify databases that must be isolated from other databases and managed by a dedicated team. An additional instance is warranted in this situation.
  • Autonomy and workload isolation is another reason for having multiple instances. In this situation, the focus is on each SQL Server instance being managed independently for any number of reasons, such as security or performance. For example, a service pack can be applied to instance01 without negatively affecting the databases residing on instance02.
  • Some organizations need to maintain separate global settings, such as authentication modes, audit standards and server collation settings for each SQL Server instance. Since these settings are managed at the instance level, a single instance won't suffice and additional instances are required.
  • Databases and instances have different service-level agreement (SLA) requirements. Organizations, therefore can avoid potential SLA conflicts by steering away from consolidation or configure an instance based on similar SLAs.

Those situations make it clear that a single instance is not always possible. But even in situations like those, it is still common for organizations to consolidate to fewer SQL Server hosts and experience a reduction in TCO.

Consolidating SQL Server via virtualization

By now, many of you are familiar with Hyper-V, Microsoft's virtualization technology that is out of the box with Windows Server 2008. Organizations can leverage Hyper-V to consolidate many physical SQL Server installations on separate virtual machines that are running on a single host. Virtualization offers many benefits for organizations. It can significantly reduce TCO and the number of physical servers within the infrastructure, and with fewer physical servers, organizations require fewer licenses.

More on SQL Server consolidation and virtualization

Read about the pros and cons of virtualizing SQL Server environments

Learn why you should think twice about the Windows Azure SQL Database

Find out when SQL Server virtualization is just not a good idea

Through virtualization, organizations can also achieve complete operating system isolation and gain the potential to host multiple editions of SQL Server while running both 32- and 64-bit versions within a single host. In addition, physical SQL Servers experiencing the end of their hardware life can easily be virtualized by using a physical-to-virtual migration tool.

Benefits of consolidating with SQL Server 2008 on Windows Server 2008

Both SQL Server 2008 and Windows Server 2008 offer improved scalability, manageability, security and performance when consolidating SQL Server instances and databases. With Enterprise Edition -- specifically x64 -- the two products support up to 50 instances and 16 nodes within a failover cluster. This allows organizations to further scale the amount of instances and databases being consolidated, while also achieving high availability for all instances and databases within the cluster.

In the past, runaway workloads wrecked havoc by negatively affecting the performance of all databases within a consolidated environment. Resource issues such as runaway queries can now be managed by implementing resource limits and priorities with Resource Governor, a technology packaged with SQL Server 2008.

Likewise, policy-based management can help organizations manage SQL Server instances. It usually results in reduced management costs while ensuring that all instances and databases adhere to a set of corporate standards. Finally, Windows System Resource Manager (WSRM), a feature in Windows Server 2008, can be leveraged to partition and manage consolidated resources, which is a useful tool when consolidating. Extras like these out-of-the-box technologies simplify business operations in a consolidated environment.

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 Administration

Dig Deeper on Microsoft SQL Server 2008