Running SQL Server on Hyper-V

Driven in no small part by a flagging economy, virtualization has taken the IT industry by storm. Virtualization has enabled businesses to sidestep traditional OS and hardware limitations by allowing multiple servers to run together on a single hardware platform. For organizations running Windows Server 2008, Hyper-V is an especially compelling option as Hyper-V is included as an installable role. Let's look at some of the driving forces that are motivating businesses to embrace virtualization—even for holdout installations such as SQL Server—and what you need to know should you decide to run SQL Server on Hyper-V.

Driving Forces Behind Virtualization

One of the most common reasons why organizations have taken advantage of virtualization is to consolidate servers. (Although you can use SQL Server's multiple instances feature to consolidate SQL Server systems, it isn't widely used for that purpose. For more information about this feature, see "Virtualization vs. Multiple Instances.") Organizations can use server consolidation to combine the workloads of multiple physical servers onto a single powerful server, where each workload runs in a separate virtual machine. This increases server utilization and ROI, which is especially important because today's powerful multicore servers are typically underutilized. Server consolidation enables organizations to take advantage of this unused computing potential. It also improves manageability by reducing the number of server systems that need to be managed.

Another driving force behind virtualization is enhancing business continuity. Because virtualization abstracts the server OS from the underlying hardware, it enables more flexibility for backup and disaster recovery. For instance, a server failure in a tradition SQL Server hardware-based installation typically requires a secondary backup system. Some organizations use failover clustering for server-level hardware protection, but failover clustering is typically limited to the most important servers. A hardware failure for most servers means bringing in a replacement server, reimaging the system, and restoring the latest backup—a process that can take several hours at the very minimum. Virtualization technology can speed up the recovery process by allowing an organization to bring a copy of the SQL Server virtual machine (VM) online on another virtual server—a process that takes seconds instead of hours. In addition, features such as Quick Migration and Live Migration (which will be available in Windows Server 2008 R2) enable businesses to address planned downtime scenarios with little or no interruption of services to the end user.

Another popular use for virtualization is using it for development servers. You can also use virtualization for staging servers used to test system updates and application changes before deploying them into production.

For all its potential benefits, many organizations running SQL Server have been slow to adopt virtualization for their database servers. The trade-off for the benefits of virtualization is performance. Many businesses are concerned that running SQL Server on a VM won't provide the performance that their users require. But this was more the case in the early days of virtualization. Today's second generation hypervisor-based virtualization products like Microsoft's Hyper-V and VMware's ESX Server have been successfully used in many production virtualization implementations.

In addition, many people are under the mistaken impression that Microsoft doesn't support running SQL Server in a virtual environment. This used to be true for SQL Server 2005 and the older Virtual Server 2005 product. However, that all changed with the release of the Hyper-V virtualization platform and SQL Server 2008. Microsoft supports SQL Server running under Hyper-V, ESX Server, and other virtualization platforms that are certified through Microsoft's Server Virtualization Validation Program (SVVP). To learn more about Microsoft's support policy for running SQL Server in a virtualized environment, see the Microsoft article "Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment".

Finally, the licensing for running SQL Server on a VM can be confusing. However, when using the right Windows Server and SQL Server editions, there can be significant licensing advantages when using virtualization. For more information about licensing, see the "Virtualization Licensing for Windows Server and SQL Server" sidebar.

Optimizing VM I/O

Performance is the number one concern for running SQL Server on a VM. The bottleneck of most database servers is I/O and not processing power. This is especially true for SQL Server running on a VM. Taking steps to improve the I/O throughput is one of the most important factors when setting up SQL Server VMs. The first issue is choosing the right type of Virtual Hard Disk (VHD) for your SQL Server installation. Hyper-V supports the following types of VHDs:

For test and development servers, dynamic VHDs are usually best because they reduce the disk space that's required. For SQL Server systems running production workloads, fixed or pass-through VHDs are best. They provide better performance because they won't experience the performance impact that occurs when a dynamic VHD is expanded. Microsoft's SQL Server Customer Advisory Team (SQLCAT) did some in-depth testing of SQL Server 2008 running on Hyper-V. SQLCAT provided several performance recommendations, one of which was using pass-through disks for best performance under Hyper-V. To read the complete SQLCAT test results, go to the SQLCAT Whitepapers website.

Hyper-V's New Virtual Machine Wizard and New Virtual Hard Disk Wizard don't let you create pass-through disks. To create a pass-through VHD, open the Hyper-V Manager. Right-click the VM that you want to modify to use pass-through VHDs, then select the virtual hard drive to modify. Select the Physical hard disk radio button, then choose the physical disk that you want to use as the pass-through disk. The physical disk can be a local drive or it can be on a SAN. The drive must be in an offline state to be in the drop-down list. You can see an example of how to configure a Hyper-V VM to use pass-through VHDs in Figure 1.

Figure 1: Configuring a pass-through VHD in Hyper-V

The actual I/O performance that you get is highly dependent on the underlying storage subsystem that you're using, but all things being equal, pass-through VHDs provide the best performance. However, they don't offer the flexibility of fixed VHDs and aren't as portable between systems. Fixed VHDs also offer very good performance and are a better solution for most SQL Server workloads.

The type of storage you use for your VHDs will make a big difference. Using a multipath-capable SAN for your VHD storage will provide the best performance. SANs are highly scalable and let you spread the I/O over multiple drive spindles. Complete information for configuring SQL Server to use a SAN is beyond the scope of this article. For more information about properly configuring SQL Server to use a SAN for I/O, see "SQL Server on a SAN" and "What's the Best Way to Carve Up a SAN?".

Assigning Virtual Processors

Next to I/O, the virtual processor is probably the next most important VM performance factor. Assigning the number of virtual processors for a SQL Server VM is a relatively straightforward task for systems with four CPU cores or less. Hyper-V supports up to four virtual CPUs per VM. To assign multiple virtual processors to your VM, open the Hyper-V Manager. Right-click the VM, select Settings, then click Processor. In the Number of logical processors drop-down list, select the number of virtual processors to be used by the VM, as Figure 2 shows.

Figure 2: Assigning virtual CPUs

Note that you can assign only as many virtual processors to a given VM as there are physical cores in the systems. For instance, if you have a quad-core system, you can't assign more than four virtual CPUs per VM.

In a server consolidation environment, it's especially important to ensure that the virtualization host has adequate processing power to handle all the active VMs. To calculate the required processing power needed by the Hyper-V host, you can use the formula

CPU Cores x CPU Speed x CPU Utilization = Total CPU

where CPU cores is the number of CPUs in the physical server to be virtualized, CPU Speed is the CPU's speed, and CPU Utilization is the average utilization for that CPU. You need to perform this calculation for all the servers that will be running on the Hyper-V host. Then, you need to add the Total CPU values together to get an overall estimate of the CPU requirements for all the VMs, using the formula

Sum(Total CPU) = Overall CPU

Although specific requirements will vary, a good rule of thumb is to reserve 25 percent of the host's processing power. So, you can determine the host's virtualization capability using the formula

Overall CPU x 25%

Ideally, you should strive for a 1-to-1 mapping between virtual CPUs and physical cores, although most server consolidation environments have a higher ratio. One thing to bear in mind is that adding virtual CPUs to your VMs won't result in linear performance improvements. In other words, adding a second virtual CPU won't double the performance of your VM. You're far more likely to see more modest gains. Some experts cite improvements of 10 to 15 percent by adding virtual CPUs.

Calculating VM Memory

To size the memory for a SQL Server VM, simply create the VM with the same amount of memory allocated to that VM on the Hyper-V host. If the host is memory-constrained, this is a good time to add more memory. Hyper-V supports up to 64GB per VM, and the host can support up to 1TB of physical memory. To take advantage of memory beyond 4GB, the VM's guest OS must be 64-bit. It's important to note that the combined RAM requirements of the active VMs can't be more than the amount of physical RAM in the Hyper-V host. For instance, a 32GB Hyper-V host couldn't support five 8GB VMs.

To calculate the Hyper-V host memory requirements, you first need to add about 32MB of memory for virtualization overhead to each VM's RAM, then aggregate all the VMs' RAM. In addition, you need to reserve memory for the host. As a general rule of thumb, reserving 512MB of RAM for the Hyper-V host is adequate. The hypervisor itself requires about 300MB. So, you can use the following formula to calculate host memory requirements:

Sum(VM RAM + 32MB) + 512MB + 300MB

You can find a handy Excel spreadsheet for calculating Hyper-V host memory requirements at the Hyper-V RAM Calculator.xls web page. A useful tip to remember regarding memory is that adding memory to the VM can help boost its I/O performance by enabling SQL Server to use the additional memory for caching.

Using Hyper-V Synthetic Network Devices

Another configuration setting that can have a big impact on server performance under Hyper-V is making sure the SQL Server VM uses Hyper-V's new synthetic network devices. Synthetic network devices take full advantage of the Hyper-V high-performance VM bus architecture. For SQL Server virtualization, it's important that the VM uses the new synthetic network adapter. The legacy network drivers are provided in Hyper-V for compatibility with older OSs, but they're processed by threads in the parent partition and don't provide the same level of performance as the newer synthetic network devices.

Drivers for the new synthetic network devices are part of Windows Server 2008 and Windows Server 2008 R2. If you use them as guest OSs in your VMs, the synthetic device drivers will be present. However, these synthetic device drivers aren't part of Windows Server 2003 or earlier server OSs. If you're running Windows Server 2003 as your guest OS, you need to install the Hyper-V Integration Components. The Integration Components include the synthetic network device drivers as well as enhanced mouse and video support and host time-synchronization. You can ensure that you're using the synthetic devices by connecting to the VM, then opening Device Manager. Expand the Network adapters node and check for the value Microsoft VMBus Network Adapter, like you see in Figure 3.

Figure 3: Checking for the synthetic network device drivers

To install the Integration Components, open the Hyper-V Manager, right-click the VM, and select Connect. In the Virtual Machine Console that appears, select Action, Insert Integration Services Setup Disk. Then just click Next throughout the wizard screens.

Calculating Available Network Adapter Capacity

One of the easiest performance factors to overlook when performing server consolidation is your available network adapter capacity. When you consolidate multiple physical SQL Server systems on a single server, you're essentially taking all the network traffic that was previously directed to multiple servers and their NICs and funneling them through fewer NICs on the Hyper-V hosts. To calculate the network capacity required, you can use the formula

VM NICs x NIC Speed x NIC Utilization = NIC Requirements

where VM NICs are the number of virtual machines that will be active on the host, NIC Speed is the speed of the NICs, and NIC Utilization is the average network utilization of those NICs.

To calculate the host's total network capacity, take the number of NICs in the host and multiply that number by their speed, as is shown in the formula

Number of NICs x NIC speed = Total NIC Capacity

If you divide the Total NIC Capacity by the NIC Requirements, you'll have an idea of how many Hyper-V host network adapters you'll need. Ideally, you should allocate a separate host NIC for each VM. Another recommended practice is to reserve an additional network adapter for Hyper-V host management. For added security, it's a good idea to put the management NIC on a separate network and not with the NICs used by the production VMs.

If you're familiar with ESX Server, you might be wondering about NIC teaming. NIC teaming lets you bind multiple host network adapters together. Although Hyper-V doesn't support NIC teaming, you can implement it if you buy the right NICs.

Virtually SQL

Virtualization is a mature technology that's ready for prime time. While the Hyper-V platform can provide production-level performance for SQL Server, you still need to be sure to correctly size your VMs as well as take advantage of Hyper-V features such as multiple virtual processors, 64-bit guest support, and synthetic network devices.