Server Consolidation and Virtualization


Rod Colledge

This article is excerpted from the upcoming book SQL Server 2008 Administration in Action by Rod Colledge and published by Manning Publications. It
examines the objectives and implications of server consolidation and
virtualization. For the book’s table of contents, Author Forum, and other
resources, go to

The arrival of personal computer networks marked a shift away
from big iron mainframes and dumb terminals to a
decentralized processing model comprised of many (relatively cheap) servers and
lots of personal computers. In many ways, we’ve come full circle, with the
shift back to a centralized model of storage area networks and virtualized
servers running on fewer, more powerful servers.

In recent years, the shift towards server consolidation and
virtualization has gained pace, and in the years ahead, the idea of a
dedicated, non virtualized server may seem quite
odd. From a SQL Server perspective, this has a number of important
ramifications. Before we look at the considerations and differences, let’s
begin by taking a look at the goals of consolidation and virtualization.

Goals of Consolidation & Virtualization

The plummeting cost and increased power of today’s server
components has moved the real cost of modern computing from hardware to people,
processes, power and space. As such, in a never ending quest to minimize
operating costs, businesses have embraced consolidation and virtualization
techniques, the major goals of which are to avoid server sprawl and minimize

Server Sprawl

In general terms, Server Sprawl is
used to describe the uncontrolled growth of servers throughout the enterprise,
making administration very difficult. SQL Server sprawl is a particularly nasty
problem; consider an example of a SQL Server instance running on a PC sitting
under someone’s desk, installed by a DBA who is no longer with the organization
and who never documented its existence. Does this instance need backing up?
Does it contain sensitive data? Do you even know that it exists?

Given the ease with which new SQL Server installations can be
deployed, and the power of today’s commodity PCs, SQL Server sprawl is an all
too common problem. Whilst there are tools for discovering the presence of SQL
Server instances, and new SQL Server features, such as Policy Based Management
that make administration much simpler, the sprawl issue remains a significant
problem, and one of the prime reasons for consolidation and virtualization

Assessment and Planning Toolkit

The Microsoft Assessment and Planning
Toolkit Solution Accelerator is an excellent (free) tool that can be
used in assessing existing infrastructure. From a SQL Server perspective, one
of the great aspects of this tool is its ability to discover
installed SQL Servers on the network, handy for planning upgrades and arresting
out of control sprawl situations

Operating Costs

Each physical server consumes space and power, both of which are
the natural enemies of a data centre. Once full, expanding or building a new
data center is a very costly exercise, and one that makes little sense if the
existing servers are running at 50 % utilization.

As computing power increases and virtualization software
improves, the march towards fewer, more powerful servers hosting multiple
virtual servers and/or more database instances is an industry trend that shows
no sign of stopping. The real question for a DBA is often how
to consolidate/virtualize rather that whether to consolidate/virtualize.
In answering that question, let’s look at each technique in turn, beginning
with consolidation.


Whilst virtualization can be considered a form of consolidation,
in this section, we’ll take consolidation to mean installing multiple SQL
Server instances on the one server, or moving multiple databases to the one
instance. Figure 1 shows an example of consolidating a number of database
instances onto a failover cluster.

Figure 1: Amongst other benefits, SQL Server
consolidation centralizes administration and combats server sprawl

SQL Server’s support of NUMA hardware, the ability to install
multiple instances and cap each one’s memory & CPU, and the introduction of
Resource Governor in SQL Server 2008 all contribute to the ability to
effectively consolidate a large number of databases and/or database instances
on the one server.

Just as creating a new virtual server is very simple, installing
a new SQL Server instance on an existing server is also simple, as is migrating
a database from one instance to another. But just because these tasks are
simple does not mean they should be done without thought and planning. Let’s
take a look at a number of important consolidation considerations for SQL

Baseline Analysis

From a consolidation perspective, having baseline data at hand
allows for sensible decisions to be made on the placement of instances. For
example, consolidating a number of servers, each of which are bottlenecking on
CPU, on a single CPU core box does not make any sense. In contrast,
consolidating servers that consume very little resources does make sense. Accurate
baseline data is a crucial component in making the right choices as to which
servers and/or databases are consolidated together.

When examining typical usage as part of a consolidation process,
care should be taken to ensure batch processes are considered. For example, 2
SQL Server instances may co-exist on the one server perfectly well until the
end of the month, at which point they both run a large end-of-month batch
process, potentially causing each process to exceed the required execution

Administrative Considerations

Consolidation brings with it an even mix of benefits and
challenges. We’ve just covered the importance of consolidating complementary
instances from a performance perspective. Equally important is considering a
number of other administration aspects:

  • Maintenance Windows;
    How long is each server’s maintenance window, and will they work together on a
    consolidated server?
  • Disk Growth;
    Is there enough disk space (and physical disk isolation) to ensure the database
    growth and backup requirements can be met?


Apart from the ability to affinitize CPUs and cap memory usage,
choosing to install multiple instances has one distinct advantage over placing
multiple databases in the one instance; each instance has its own TempDB
database. Depending on the databases being consolidated, installing multiple
instances allows more than one TempDB database to be available, enabling the
placement of databases with heavy tempdb requirements in the appropriate

Let’s turn our attention now to Virtualization; a specialized form
of consolidation.


Unlike the SQL Server instance/database consolidation techniques
we’ve outlined above, virtualization occurs at a lower level by enabling a
single server to be logically carved up into multiple virtual
machines, or guests, each of which shares the
physical server’s hardware resources but is otherwise separate with their own
operating system and applications.

Virtualization platforms, also known as Hypervisors,
are classified as either Type 1 or Type 2. Type 1 Hypervisors, commonly
referred to as Native or Bare-Metal
run directly on top of the server’s hardware. VMware’s ESX
Server and Microsoft’s Hyper-V are recent
examples of Type 1 Hypervisors, an example of which is shown in figure 2.

Figure 2: Hypervisors such as Microsoft’s Hyper-V are
used to virtualize multiple guest servers. Guests share the host’s resources
whilst appearing on the network as normal servers

Type 2 Hypervisors such as VMWare Workstation and Microsoft’s
Virtual Server, run within an existing operating system. For example, a laptop
running Windows Vista, could have VMware Workstation installed in order to host
one or more guest virtual machines running various operating systems such as
Windows Server 2008 or Novell SUSE Linux.

Type 1 Hypervisors are typically used in server environments
where maximum performance of guest virtual machines is the prime concern. In
contrast, Type 2 hypervisors are typically used in development and testing
situations, enabling a laptop, for example, to host many different guest
operating systems for various purposes.

Let’s take a look at some of the pros and cons of virtualization
as compared with the consolidation techniques covered above.

Advantages of Virtualization

Virtualization offers many unique advantages including the

  • Resource
    Flexibility; Unlike a dedicated physical server,
    resources (RAM, CPU and so forth) in a virtual machine can be easily increased
    or decreased, with spare capacity coming from / returned to the host server.
    Further, some virtualization solutions enable virtual machines to dynamically
    move to another physical server, enabling large numbers of virtual machines to
    be balanced across a pool of physical servers,
  • Guest Operating Systems;
    A single physical server can host many different operating systems and/or
    different versions of the one operating system,
  • Convert Physical to Virtual
    Servers; The major virtualization products include tools to
    create a virtual machine based on an existing physical machine. One of the many
    advantages of such tools is to preserve the state of an older legacy server
    which may not be required anymore. If required, the converted virtual server
    can be powered on, without the need to maintain the physical server, and the
    associated power, cooling and space requirements whilst it’s not being used,
  • Portability & Disaster
    Recovery; A virtual machine can be easily copied from one server
    to another, perhaps in a different physical location. Further, there are
    various products which specialize in the real time replication of a virtual
    machine, including its applications and data, from one location to another,
    enabling enhanced disaster recovery options,
  • Snapshot/Rollback
    Capability; A powerful aspect of some virtualization platforms is
    the ability to snapshot a virtual machine for later
    rollback purposes. This feature can be considered as backup/restore at the machine
    level, and enables changes to be made, safe in the knowledge that the snapshot
    can be restored if required. One example of this may be performing an in-place
    upgrade of SQL Server 2005 to 2008. Should the upgrade fail, the snapshot can
    be restored, putting the system back to its pre-upgrade state

Despite these clear advantages, there are a number of issues for
consideration before virtualizing SQL Server environments.

Considerations for Virtualizing SQL Server

Arguably the single biggest issue for consideration when
virtualizing SQL Server is that of support,
particularly for mission critical production systems. It’s not uncommon to hear
of organizations with virtualized SQL Server environments having difficulty
during support incidents due to the presence of a virtualization platform, with
a common request during such incidents to reproduce the problem in a non
virtualized environment. Such a request is usually unrealistic when dealing
with a critical 24×7 production SQL Server environment.

The support issue is commonly cited as the prime reason for
avoiding virtualization in Production environments (and associated Volume/Load Test environments).
Those that take this approach often use virtualization in other less critical
environments such as development and testing.

Virtualization Support Policy

Microsoft recently announced that both SQL Server 2005 and
2008 will be officially supported in Hyper-V environments, as well as those
certified through the Server Virtualization Validation
Program (SVVP). Links and further details available at

Other considerations for SQL Server virtualization include:

  • Scalability;
    The maximum resource limitations per virtual machine (which varies depending on
    the virtualization platform and version) may present an issue for high volume
    applications which require maximum scalability. In such cases, using physical
    servers, with scalability limited only by the hardware and operating system,
    may present a more flexible solution,
  • Performance Overhead;
    Depending on the hypervisor, a commonly cited figure in terms of the
    performance overhead of the virtualization platform is approximately 10 %,
  • Baseline Analysis;
    As with the server consolidation techniques we covered earlier, consideration
    should be given to the profiles of the individual virtual servers running on
    the same machine, for example, placing many CPU intensive virtual machines
    together on an single CPU core host machine,
  • Licensing;
    Licensing is a tricky and complex area, so I’m not going to
    attempt a full coverage of the pros and cons of licensing virtualization other
    than to recommend that before deciding on a server consolidation technique, the
    licensing implications should be well understood,
  • Toolset; One
    of the things that becomes apparent when troubleshooting a performance problem
    on a virtual machine is that in order to get the full picture of what’s
    happening on the server, one needs access to the virtualization toolset in
    order to determine the impact from other virtual machines. Depending on the
    organization, access to such tools may or may not be granted to a DBA

Virtualization and Consolidation techniques are here to
stay; it’s vitally important that the pros, cons, and considerations of each
technique are well understood.

Excerpted from
SQL Server 2008 Administration in Action
Rod Colledge
MEAP Release: May 2008
Softbound print: January 2009 (est.) | 450 pages
ISBN: 193398872X

Latest Articles