Server Consolidation and Virtualization

March 24, 2009

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 24x7 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