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

The changing database consolidation landscape

Changes to Windows, SQL Server and processor design are combining to make virtualization a more attractive goal for database consolidation. Find out what the options are for database consolidation and how to put virtualization to good use.

Service provider takeaway: Service providers interested in using virtualization for SQL Server database consolidation...

should re-examine their options, taking into consideration recent developments around Windows Server, SQL Server and processor design.

Over the past few years, there's been a lot of interest in server consolidation, especially consolidation in virtualized environments. Recent changes to SQL Server, Windows Server and processor design are combining to make virtualization for database environments a better proposition for your customers than in the past.

In consolidation, underutilized servers and/or applications are migrated from the physical machines on which they reside to a single machine that delivers similar if not better performance. Consolidation is attractive since it means fewer physical machines to purchase, house and manage, as well as lower power and cooling costs. And if it's done correctly, your customers' users won't know that their application or server exists in a consolidated environment.

Within the realm of databases, IT managers and service providers considering SQL Server consolidation frequently have a difficult time deciding how to approach the consolidation and what part virtualization technology should play. There are two basic options, as well as a hybrid approach. Consider a case where you want to consolidate three SQL Servers, each hosting 10 databases; the choices are:

  • Consolidate on one physical machine running perhaps two instances of SQL Server. (Two instances make sense to enable process isolation and because of consolidation blockers that prevent, for example, two databases with the same name from residing on the same instance.) Each instance would host 15 databases.
  • Consolidate on one physical machine running the virtualization host environment, which would host two virtual machines running Windows 2003, with an instance of SQL Server on each of these virtual machines. Each instance would host 15 databases.

In some cases, especially in QA or development environments, a hybrid of those two options might make sense. In a hybrid model, each virtual machine could run multiple instances, for example, to test configurations of the product on different versions of SQL Server or to isolate processes even further.

Using virtualization for SQL Server is appealing since it enables rapid deployment of one virtualized instance to another physical machine if that instance doesn't perform well in its original environment. Microsoft licenses SQL Server and Windows by socket and not by core -- so a dual-core machine requires one license. If your customer is running a licensed copy of Windows Server 2003 or 2008 Enterprise Edition, that license will also cover four virtual instances of Windows 2003 or 2008 running on that machine. So they could buy one license and run as many as five copies of it (the base OS plus the four virtual instances) for the price of one license. For Windows Data Center 2003 and 2008, the scenario is even better; a Data Center license allows unlimited copies of Windows Server.

One downside of virtualization is that performance takes a hit since the virtualized hosting software has to manage and share the hardware among each virtualized instance. This performance impact will make some SQL Server workloads unacceptable candidates for virtualization. Another problem is that the virtualized hosted environment is a single point of failure. If your customer's instance of Microsoft Virtual Server or VMware Server -- hosting 20 instances of Windows 2003, each running five instances of SQL Server -- crashes, you will bring down 100 SQL Server instances and all the hosted databases. As part of the planning for SQL Server consolidation in a hosted environment, business continuity plans must be in place to account for this single point of failure. In general, servers that require high-nines availability are not good candidates for virtualization.

If you have servers that require low-nines availability and you run them in virtualized environments, you will need to have a contingency plan to migrate them to another physical machine should that machine have performance problems or need downtime. For Virtual Server and VMware Server, the virtual machine consists of a few files that can be copied from one server to another quickly to reduce downtime dramatically. These files can also exist on other servers, and if the virtual server goes down on one machine, it can be spun up quickly on another machine.

Currently VMware has rich tools for rapid provisioning of a virtual instance from one physical machine to another. Microsoft's tool set for virtualization is not as fully featured as VMware's, but Microsoft and tool vendors could fill this void soon.

The changing landscape


Recent developments have changed the database consolidation and virtualization equation a bit. Here's what's new:

  • SQL Server 2008: SQL Server is a very powerful RDBMS; on the right hardware, it can host several hundred databases on a single instance of SQL Server. (However, when you try to add more databases to that instance, performance will suffer, and you will need to start stacking new databases on another instance.) In SQL Server 2005, the solution to isolating a problematic instance was to either place it on its own physical server or to place it in its own virtualized environment. In SQL Server 2008, the Resource Governor feature allows you to limit how much CPU or memory a process or user can take. Resource Governor does not try to govern or throttle disk access; this is because I/O contention between instances is normally isolated on the hardware level -- i.e., a LUN on the SAN would be dedicated to an instance or some databases using that instance.
  • Windows Server 2008: Windows Server 2008 has an installation mode called Server Core, which produces a minimal installation of the operating system. It has a command prompt -- no GUI. This minimal installation lowers the threat threshold to Trojans, worms and viruses so it will require fewer patches and therefore mean less downtime. Server Core is ideal for running Hyper-V, a Web server or other mission-critical applications that don't need a desktop. Beyond that measure of safety, Windows Server 2008 is designed to host Microsoft's Hyper-V virtual hosting product, which provides much better virtualization performance than the company's Virtual Server 2005. Hyper-V can access hardware resources at a lower level to partially virtualize access to the CPU and other hardware resources (called bare metal access) instead of having to access them through the virtualization software. Hyper-V also has much better emulation performance.
  • Hardware: Processor manufacturers are working to provide new features in their upcoming chips that will reduce the processor bottlenecks that degrade virtualization performance. For example, the recently released "Barcelona" chip from AMD has features designed to support processor requirements of virtualization and can access as much as a terabyte of memory to improve the memory access performance that virtualization depends on.

As you can see, these changes to SQL Server and Windows Server and hardware developments combine to make the virtualization route more appealing to your customers -- by increasing the number of SQL Server instances possible on a single physical machine and enabling fine-grained control of physical resources. When helping customers make database consolidation decisions, these factors should be taken into account.

About the author
Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.


This was last published in March 2008

Dig Deeper on Database software management

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.