A few years ago, SQL server virtualization was considered to be too resource intensive. But now with a new generation of hardware and scalability advances in Hyper-V and vSphere platforms a lot of things have changed. Its now common to virtualize SQL server instances. However, no matter how careful we are, mistakes do creep in and the result is poorly performing SQL Server Virtual Machines.
Below are some of the top five mistakes to avoid when virtualizing SQL server.
Old server re-purposing can prove as a big mistake- Data center managers often try to add life into older servers by using them as virtualization hosts. But in practical older systems don’t have enough processing power which the new systems have these days. Also older systems might also not support second level address translation (SLAT) which adds performance to virtualization enabling the CPU to maintain the mapping between the virtual memory used by the VMs and the physical memory in the virtualization host. If CPU doesn’t perform the memory mapping task, then the hypervisor has to perform the mapping task, taking away all CPU cycles from other VMs.
Over commitment of host processors can prove troublesome- Often we see that network admins over configure more virtual CPUs than the physical cores which are present. This procedure will lead to over commitment of physical CPUs where there will have to time slice processor cycles among many running VMs. For optimum performance, one should try to reserve one physical core for each virtual CPU in the Virtual Machine. This will help ensure the VM has physical processing power if it needs it.
Do not undersize virtual machine RAM- Availability of host RAM can prove as a limiting factor to how many VMs users can simultaneously run on a virtualization host. This includes limiting virtual RAM in SQL server VMs in order to achieve higher VM consolidation ratios. However, SQL server performs far better when it has adequate amounts of RAM, as SQL server will grow its buffer pool in response to growing workloads. Experienced admins also recommend to enable dynamic memory for VMs running SQL server enterprise edition. Enterprise edition which have hot RAM capability enables it to dynamically add memory to a running VM.
Do not use the default virtual hard disk configuration- Generally, the default settings for a virtual machine create a new VM with a single virtual hard disk. By using this configuration the result will be that all the info such as Operating system, SQL server instance, data and logs will be stored in the same virtual hard disk. This will make all production workloads immediately run into I/O contention problems. Instead, the admin should split the OS, data and log files onto separate virtual hard disks that are served by different physical drives or LUNs.
Usage of virtual hard disks must be restricted- Its a known fact that Hyper V creates dynamic virtual disks for new VMs. However, using a fixed virtual hard disk is best choice for virtualized SQL server systems that run a production workload. That’s because dynamic Virtual hard disks use less disk space than fixed virtual disks, but they do not provide the same level of performance. Therefore, dynamic VHDs are a good choice for development and test environments or non-critical production workloads. Fixed virtual hard disks are better for production because dynamic VHDs can experience occasional pauses when the dynamic disk needs to be extended.
By avoiding these virtualization mistakes can help you to ensure that your Virtual SQL server instances will offer utmost performance and can meet your SLAs on a perfect note.
Note- Microsoft & HPE are underwriters of this article.