Server Consolidation and Virtualization

by

Rod Colledge

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
costs.

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
projects.

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.

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.

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
Server.

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
window.

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?

TempDB

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
instance.

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

Virtualization

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
hypervisors,
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.

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
following:

• 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.

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
EARLY ACCESS EDITION
Rod Colledge
MEAP Release: May 2008
Softbound print: January 2009 (est.) | 450 pages
ISBN: 193398872X