Server Consolidation and Virtualization
March 24, 2009
by 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 books table of contents, Author Forum, and other resources, go to http://manning.com/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.
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.
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
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.
SQL Server's support of NUMA hardware, the ability to install multiple instances and cap each ones 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.
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.
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:
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.
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:
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.
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 http://www.sqlCrunch.com/server
Other considerations for SQL Server virtualization include:
Virtualization and Consolidation techniques are here to stay; it's vitally important that the pros, cons, and considerations of each technique are well understood.