kentoh - Fotolia

Manage Learn to apply best practices and optimize your operations.

How to get the most out of virtual SQL Server with Microsoft Hyper-V

SQL Server is a CPU-intensive technology, which can make it tricky to run in a virtualized environment. Keep your SQL Server virtual machines running smoothly with these tips on using Hyper-V.

The key to building a virtual SQL Server system that runs smoothly is ensuring that the virtualization hypervisor is configured to provide the database with the resources it needs. SQL Server is a CPU-intensive technology, so virtual machines running it need to be able to access sufficient processing resources without causing contention among neighboring VMs.

SQL Server virtualization tends to be complex, and there are a number of factors that go into the process of making it work. That includes controlling CPU allocation and resource utilization, as well as configuring the memory in SQL Server VMs to boost their scalability and avoid shortfalls in the amount of memory that's available to them when needed to run processing jobs. The good news is that Microsoft's Hyper-V virtualization software provides tools to help with the required work, at least in organizations using that technology.

For starters, Hyper-V contains a number of different settings that can be used to control CPU resource allocation on a per VM basis, as shown in Figure 1.

Allocation control settings
Figure 1. Settings in Hyper-V for controlling allocation of virtual-machine CPU resources.

By default, the Hyper-V Manager only allocates a single virtual processor to virtual machines, so it's important to provide SQL Server VMs with a more appropriate number of virtual CPUs based on their workload and your SQL Server licenses. Then, to ensure that a virtual SQL Server receives sufficient CPU resources, you can use the "Virtual machine reserve" setting to select the percentage of the available resources reserved for that particular VM.

There are various ways to prevent your virtual SQL Server from causing CPU resource contention. For instance, you can adjust the "Virtual machine limit" and "Relative weight" settings in the resource control panel to cap CPU resource consumption for a SQL Server VM. Another option is to apply reserves to neighboring virtual machines in an effort to prevent them from becoming starved for resources. That can get tricky, however, since VMs can be live migrated to other hosts.

In addition, Windows Server 2016 introduces host resource protection, a feature specifically designed to stop VMs from taking up too much of the existing hardware resources. Host resource protection is exposed through Hyper-V's Set-VMProcessor cmdlet. It performs host-level monitoring of CPU consumption and throttles processing resources automatically if a VM begins to consume excessive CPU cycles.

IT teams looking to virtualize SQL Server should also pay attention to the memory configuration of virtual machines. You can see the memory configuration options supported by Hyper-V in Figure 2.

Memory configuration options
Figure 2. Hyper-V's memory configuration options for virtual machines.

The hypervisor provides a choice between using dynamic memory and memory that has been statically configured. Dynamic memory is a Hyper-V feature that's designed to increase the efficiency of a system's physical memory by making it a shared resource that can be automatically reallocated between different VMs as needed. However, that makes it dangerous to use with SQL Server, since SQL Server VMs will generally attempt to use as much memory as they can. Instead, it's best to statically assign memory to virtual SQL Servers.

The one exception is for SQL Server implementations running on host systems with a non-uniform memory access (NUMA) architecture, an approach designed to boost scalability by grouping together different sets of processors and portions of system memory into what are known as NUMA nodes.

NUMA-based system configuration
Figure 3. Hyper-V's configuration options for NUMA-based systems.

Processors can access memory assigned to any NUMA node, but it's faster to tap local memory that's in the same node as a CPU than it is to access remote memory in a different one. As a result, a SQL Server VM's virtual topology of NUMA nodes and sockets should be aligned with the physical hardware's topology.

As shown in Figure 3, Hyper-V's NUMA settings are located in a subcontainer beneath the Hyper-V Manager's Processor container. You can use them to align the topologies or to manually set limits on the number of processors and the amount of memory that can be allocated to virtual NUMA nodes and the number of nodes that are allowed on a single socket in a server.

Another best practice for running SQL Server on a Hyper-V virtual machine is to use Microsoft's Generation 2 VMs, which boot up more quickly than and often outperform their Generation 1 counterparts. However, since SQL Server's performance is ultimately tied to storage I/O, it's extremely important to ensure that the virtual hard disk's underlying storage delivers sufficient IOPS to meet the database's requirements.

Another consideration is the possible conflict between two different forms of high availability. When combined with Windows Failover Clustering, Hyper-V virtual machines can be made highly available at the VM level. But SQL Server also supports high availability independently of the hypervisor. If you choose to use SQL Server's high availability capabilities -- which involve setting up a so-called guest cluster -- you need to implement antiaffinity rules to prevent different SQL Server VMs that are part of a single guest cluster from residing on a common physical host. Otherwise, a host-level failure could undermine the reliability of the guest cluster.

Running SQL Server on a VM can be something of a tightrope walk between the database's need for resources and the needs of the rest of the system. Implementing best practices like these can keep your virtual SQL Server system well-balanced.

Next Steps

How to set up and use Hyper-V failover clusters

Hyper-V VM configuration is becoming a bigger deal

Tips to speed up Hyper-V network performance

Dig Deeper on Microsoft SQL Server Consolidation and Virtualization