SQL Server Virtualization and Availability

Virtualization can make your IT Infrastructure more efficient and flexible. One of the most common uses for virtualization is in the area of server consolidation. Virtualization lets you run multiple server instances on a single hardware platform, making more efficient use of your existing servers. This results in higher hardware resource utilization, lower power requirements, and fewer operational requirements. However, virtualization has other benefits beyond server consolidation—it can also improve the availability of your servers. In this article you'll learn about some of the ways that virtualization can be used to improve the availability of the SQL Server systems in the areas of disaster recovery, planned downtime, and dynamic resource management. I'll focus on the options that are available through the Microsoft technology stack, but similar options are available from VMware.

Backing Up Virtual Machines for Disaster Recovery

One of the most basic ways that virtualization can improve SQL Server availability is in the area of disaster recovery. Virtualization abstracts the server OS from the underlying hardware. This separation provides a lot of flexibility in how you can back up and restore a server in the event of a hardware failure or site outage.

There are two basic options for backing up Hyper-V virtual machines (VMs). You can perform the backup at the Hyper-V virtualization host level or the Windows Server guest OS level. (It's important to remember that these system backup aren't any kind of substitute for your regular database backups.) Backing up a VM at the guest level is essentially the same as performing a normal backup of a physical server. Windows Server backup is able to use Volume Shadow Copy Services to take live backups of SQL Server. You select to restore either at the volume level or at the individual file level.

You can back up at the guest level, but virtualization also lets you perform VM backups at the host level. Windows Server's VSS enables you to back up running VMs with no downtime. One of the big advantages of backing up at the virtualization host level is the fact that the saved VMs can be quickly restored to another Hyper-V host. There's no need for bare metal restores or reinstalls of the OS or SQL Server or its databases—the VM backup contains all of these. SQL Server and its databases can be available as quickly as the VM restore can be completed and attached to another Hyper-V host.

To take advantage of online host backups, the VM host OS needs to be VSS aware and the Hyper-V Integration Services must be installed in the VM. Windows Server 2008 R2, Server 2008, Server 2003 R2, and Server 2003 all support VSS backups. VMs running guest OSs that don't have support for VSS Backups, such as Windows 2000 Server or VMs without Integration Services installed, will save their state when the VSS snapshot is taken. The state will be restored after the snapshot is complete.

Hyper-V is VSS aware, but VSS compatibility for Hyper-V isn't enabled by default. For Windows Server Backup to be able to back up a Hyper-V VM using VSS, you need to add the registry key HKLMSoftwareMicrosoftWindows NTCurrentVersionWindowsServerBackupApplication Support\{66841CD4-6DED-4F4B-8F17-FD23F8DDC3DE\}.

After adding the key, you need to create the following string value under it.

Name: Application IdentifierType: REG_SZValue: Hyper-V

For more information about enabling Hyper-V and Windows Server Backup integration, refer to the Microsoft article "How to Back Up Hyper-V Virtual Machines from the Parent Partition on a Windows Server 2008-Based Computer by Using Windows Server Backup."

For disaster recovery purposes, you'd typically set up a process to move the VM backup to a disaster recovery site. You can do this manually or by using File Replication Services, Distributed File Services Replication, or even scripted FTP transfers. For more information about using DFS for disaster recovery, refer to "Disaster Recovery with DFS."

To restore a VM, simply run Windows Server Backup and select the Recover option from the Actions Pane. When performing the restore, select Application Restore then choose Hyper-V. This will display the VMs that can be restored.

As an alternative to building a homegrown solution for disaster recovery, there are also several add-on products that facilitate disaster recovery for Hyper-V VMs. Examples include Microsoft's Data Protection Manager 2010, AppAssure Replay Replication, CA ArcServe Replication, Double-Take, EMC Symmetrix Remote Data Facility, R1Soft's Continuous Data Protection, and SteelEye DataKeeper for Hyper-V.

Using Live Migration to Reduce Planned Downtime

Live Migration, the equivalent of VMware's VMotion, fills an important hole in Microsoft's Hyper-V virtualization platform. It lets you move VMs between Hyper-V hosts with no downtime. Live Migration lets you perform planned maintenance on Hyper-V hosts without incurring any end user downtime from the applications that use the database servers on those VMs. Using Live Migration, you can move all the VMs from the physical Hyper-V host that needs maintenance to another Hyper-V host. You can perform whatever hardware or software maintenance that the host requires, then migrate the database server VMs back to the original server when you're done.

Live Migration can work with any version of SQL Server, but it requires Windows Server 2008 R2 or Hyper-V Server 2008 R2 as the virtualization host. In addition, the Hyper-V servers must be nodes in a Windows Failover Cluster. Clusters can have up to 16 nodes and Live Migration can take place between any of the nodes in the cluster. You can see an overview of how Live Migration works in Figure 1.

Figure 1: An Overview of Live Migration

Live Migration takes place between two Hyper-V hosts. Essentially, the VM configuration and memory of the source VM is copied to a target Hyper-V host. After the memory is copied to the target Hyper-V host, the VM on the new host can access its VHD files, which are stored in a Cluster Shared Volume (CSV), and the VM can continue to run without interruption. Server 2008 R2's new CSV storage lets both Hyper-V hosts simultaneously access the storage where the VM's Virtual Hard Disk (VHD) files are located.

When you initiate a Live Migration, the following steps occur:

1.     A new VM configuration file is created on the target server.

2.     The source VM's initial memory state is copied to the target.

3.     Changed memory pages on the source VM are tagged and copied to the target.

4.     Step 3 continues until the number of changed pages is small.

5.     The VM is paused on the source node.

6.     The final memory state is copied from the source VM to the target.

7.     The VM is resumed on the target.

8.     An ARP is issued to update the network routing tables.

Live Migration works exactly as advertised. You can be running multiple queries on a SQL Server system that's being live migrated to another Hyper-V host and there'll be no interruption of service—the queries will run to completion with no problems. Hyper-V Live Migration can effectively reduce planned downtime for SQL Server database server hardware and software maintenance. For more detailed step-by-step information about setting up Live Migration, refer to "Hyper-V Live Migration: A Step-by-Step Guide."

Dynamic Resource Management for Enhanced Application Availability

Availability isn't limited to server uptime. True availability must be measured by the end user's ability to access computing resources. If server's responsive time is affected, availability is also affected, because the users can't get the information they need to do their jobs. This can happen even when the database server is up and fully available but demands for system resources overload the server, making it unresponsive. Unacceptable response time is definitely a form of downtime.

Virtualization and Live Migration can work together to provide the foundation for establishing dynamic management of IT resources. Virtualization lets you encapsulate a workload that can be moved between different Hyper-V hosts in the VM. Live Migration is the transport mechanism that can move the VMs between Hyper-V hosts with no downtime. System Center Virtual Machine Manager (VMM) 2008 R2 and System Center Operations Manager (SCOM) 2007 are the key components that are required to automate the process of moving database server VMs between Hyper-V servers. SCOM can monitor system resource levels, such as the CPU and memory utilization, of both Hyper-V hosts and the VMs running on those hosts. VMM 2008 R2's Performance and Resource Optimization (PRO) feature ties SCOM's monitoring capabilities to VMM's management capabilities. SCOM can detect when either Hyper-V hosts or VM guest exceed predetermined operational levels, such as when CPU utilization exceeds 60 percent, and then trigger a VMM PRO Tip. VMM's PRO Tips can automatically initiate a Live Migration, moving one or more VMs to other Hyper-V hosts. Automated VMM PRO Tips let your IT infrastructure dynamically respond to periods of high resource utilization by automatically moving VMs to hosts with greater capacities or lower workloads.

VMM PRO Tips can be generated for a variety of reasons, including the lack of resources on a Hyper-V host or VM, hardware problems such as temperature overages, power supply failure, network congestion, fan failure, or application performance falling below an acceptable threshold. PRO Tips can be responded to either manually or automatically. A PRO Tip can be informational or it can define actions required to correct a given operational condition. Remediation of PRO Tips can include migrating VMs to other hosts and starting VMs. For example, if the load on a web farm rises above a certain level, a PRO Tip can start additional Web server VMs to handle the load.

You can see an example of dynamic resource management using Hyper-V, System Center Operations Manager, and System Center Virtual Machine Manger in Figures 2 and 3. In Figure 3, the warning icons indicate where a PRO Tip has detected that the SQL Server workloads running in a given VM have exceeded the predefined operational limit. Automated implementation of the PRO Tip let VMM live migrate the VM to the next node in the cluster, which had a lower workload—dynamically adjusting to the situation that caused the operational problem and correcting it. Dynamically moving the SQL Server database VM to a Hyper-V host that can deliver better performance provides end users with improved application responsiveness and availability by matching the VM's workload to the Hyper-V host with the available resources.

Figure 2: VMM PRO and Dynamic Resource Management before a PRO Tip is detected

Virtually Better Availability

Virtualization can add a lot of value to an organization, but one of its most important assets is the ability to enhance the availability of workloads like SQL Server that run in the VMs. SQL Server was once considered to be one of those applications that couldn't be virtualized, but today many businesses have successfully virtualized SQL Server, gaining benefits in a number of areas including better resource utilization, easier manageability, and improved availability.