Best Practices for Virtualizing SQL Server

It wasn’t that long ago that many companies thought that you couldn’t virtualize SQL Server because it was too resource intensive. However, with the advances that both Microsoft and VMware have made to Hyper-V and vSphere respectively today’s virtualization platforms are more than capable of running almost every enterprise workload – including SQL Server. This article is a quick guide to the most important settings for successfully virtualizing SQL Server.

You can also watch this video in which I demonstrate some of these key concepts:

 

Some of the most critical issues to consider are:

• Baselining performance – One of the primary considerations when you are virtualizing SQL Server is that you will almost always be running your virtual SQL Server instance in a server consolidation environment. This means that your SQL Server VM will be sharing compute memory, networking and storage resources with the other VMs that are running on that host. The best way to ensure that you have adequate host resource is to create a performance baseline of the workloads that are running on the host — not only the SQL Server instance you intend to virtualization but all of the other VMs running on that host as well.
• Using VM dynamic memory – For most production workloads it’s advisable to enable dynamic memory. Both Microsoft Hyper-V and VMware vSphere support dynamic memory but they call it different things. In Hyper-V it’s called dynamic memory and in vSphere it’s called memory ballooning. Dynamic memory support enables the VM to claim more host memory when memory pressure inside the VM increases. This enables workloads like SQL Server to claim additional memory allowing it to dynamically increase its buffer pool size. The larger buffer pool enables more queries to be satisfied by the cached contents of the buffer pool thereby decreasing the amount of physical I/O that’s required which benefits all of the VMs running on the host.
• Configuring SQL Server storage – When you configure the storage for a virtual SQL Server instance it’s important to remember that the same rules you would have applied to a physical instance apply to a virtual SQL Server instance. This means that you should place your data and log files in separate virtual hard disks where each virtual hard disk is serviced by a different physical HDD or SSD. The same is true for tempdb. A best practice is to ensure that the virtual hard disk that contains tempdb is separated it from your data and log files.
• Ensuring adequate network bandwidth – Properly configuring your compute, memory and storage provides the basic foundation for good VM performance but that can all be for naught if the network can’t deliver that performance to the end users. You need to be sure the virtualization host has enough NICs and bandwidth to accommodate the workload’s client requirements. In a server consolidation environment it’s easy to underestimate your host’s requirements which underlines why performance baselines are so important. In addition, taking advantage of networking technologies like NIC teaming, Multi-path I/O and Single Root I/O Virtualization (SR-IOV) in your VMs can decrease your network latency and enable your SQL Server VM to more effectively use the available bandwidth.

While SQL Server can definitely be a resource intensive workload there’s no doubt that you can successfully virtualize SQL Server by following these basic guidelines.