7 Best Practices for Running SQL Server on Hyper-V

Michael Otey
SQL Server Magazine InstantDoc ID #135931

It’s no surprise that there’s a strong trend to virtualize SQL Server instances—and that the fastest growing virtualization platform is Microsoft Hyper-V. Hyper-V is included in Windows Server 2008 and Windows Server 2008 R2, which makes it easy for organizations not standardized on VMware to adopt Hyper-V for SQL Server. Keep in mind these seven important best practices for implementing SQL Server on Hyper-V.

1. Use servers that support Second Level Address Translation SQL Server databases can be very CPU and I/O intensive applications and the current crop of servers provides a feature that significantly improves the performance of virtual machines (VMs). Second Level Address Translation (SLAT) allows the hardware to take over mapping VM memory to physical memory. (Intel calls it Extended Page Tables—EPT—and AMD calls it Rapid Virtualization Indexing—RVI). Hardware mapping is significantly faster than software mapping, reduces the load on the hypervisor, and provides better scalability.

2. Upgrade to Windows Server 2008 R2 SP1 Windows Server 2008 R2 SP1 offers some really important improvements for VMs, and the most important is support for dynamic memory. Dynamic memory lets the Hyper-V server automatically increase and decrease the memory allocated to a Hyper-V VM while that VM is active. This allows for better scalability and more predictable performance for SQL Server instances running in Hyper-V VMs.

3. Use Multi-Path I/O Multi-Path I/O is a feature that’s supported by Windows Server 2008 and Windows Server 2008 R2. It lets you create multiple redundant paths between the Hyper-V hosts and the SAN storage. Multi-Path I/O is supported for both Fibre Channel and iSCSI storage and can increase VM availability and provide improved performance for SQL Server VMs by maximizing I/O throughput.

4. Install Integration Services in the SQL Server VM Hyper-V supports two types of devices in its VMs: synthetic and emulated. Synthetic devices deliver much better performance than emulated devices. However, to use synthetic devices you must install the guest Integration Services components, which, among other things, provide the device drivers that the guest OS needs to use synthetic devices.

5. Reserve 1GB of RAM for the parent partition When you’re in a server consolidation environment and running multiple workloads on a server, be sure to leave about 1GB of RAM for the Windows Server instance running in the parent partition. This ensures that that host server can run the different VM workloads without paging. If the parent partition is forced to page memory, VM performance is degraded.

6. Use Fixed VHDs for VM storage When you build a VM, you get two choices about the type of virtual hard disk (VHD) to use: dynamic or fixed. Dynamic VHDs are great for test and development scenarios because they require only the actual storage space consumed, but they don’t deliver the same performance as fixed VHDs. Fixed VHDs are still best for production. Note that pass-through disks are another option for SQL Server instances requiring the absolute maximum performance. However, pass-through disks aren’t as flexible as VHDs. They tie the VM to specific storage and provide only slightly better performance than fixed VHDs.

7. Use Separate LUNs/VHDs for Guest OS and Database and Log Storage As with a physical server, when you configure a SQL Server VM you gain performance improvements by splitting the guest OS to a separate VHD. You can also put the SQL Server database and log files on separate VHDs. The different VHDs should then be stored on different LUNs to take advantage of multiple disk spindles.