A couple of months ago, VMware released a new white paper called “Architecting Microsoft SQL Server on VMware vSphere”. Published in August 2018, it spells out the best practices for running SQL Server on vSphere. Twenty years ago VMware launched and forever changed the compute world.

SQL Server and VMware go together like peanut butter and jelly. Read on to look into the details…

First – you should read the white paper then come back…I’ll wait…

Architecting Microsoft SQL Server on VMware vSphere

I will point out some key points that are practical to implement. The whole paper is worth a read; however, I will explore only a few parts. We won’t talk about extra VMware features such as vMotion. The document contains many citations you can research to go more in depth should you choose.

Right-Sizing Your VM

Understanding your SQL Server workloads will help determine the level of each resource required. Our Service Level Agreements (SLA) will dictate which hardware we run on. OLTP vs OLAP databases can differ in approach. All of this is useful when planning to virtualize SQL Server on VMware.

David Klee has some well written posts that explain the concept of right-sizing your VM. By this we do not mean capacity planning – that is a different subject. Due to the nature of the hypervisor and virtualization uses there are key differences with regard to resource allocation compared with a physical server.

Right-sizing is picking the appropriate level of resources for each guest VM. We want it to handle sustained peaks of utilization without impacting other guests on the host. This is incumbent upon base-lining and measuring performance over a period of time.

Why is Over-Allocating a VM Bad?

Why is is so important to allocate just the right amount of resources for your guest VM running SQL Server? We all know the problem with an undersized system; however, what about an oversized system? What problem could that be? Here are a few common issues to consider:

• Over-allocating CPU causes poor resource utilization across all the guest VMs
• Over-allocating memory unnecessarily increases memory contention and overhead on other guest VMs
• Having more vCPUs assigned to the VM can have an impact on licensing.

ESXi Host Configuration

I wrote about best practices in my SQL Server Environmental Diagnostics Guide – VMware. In it are a lot of reminders about which configurations should be changed from default and which left alone. Let’s break down the following topics:

1. Power Settings
2. CPU Configurations
3. Memory Options
4. Storage Considerations
5. Virtual Networking

Power Settings

The settings you have for power supply can greatly affect system performance. Quoting from the white paper:

As a best practice, update the BIOS/UEFI firmware on the physical server that is running critical systems to the latest version and make sure all the I/O devices have the latest supported firmware version

CPU Configurations

Pay attention the the virtual sockets and virtual cores you specify for the guest VM.

Be careful not to exceed the number of physical cores on the ESXi host to any guest!

Some other tips and reminders:

• Enable hyper-threading
• Create less sockets than cores for each VM – not the other way around!
• Do not use CPU affinity because it limits the hypervisor from efficiently scheduling vCPUs on the physical server

Here is a table from the document showing standard sized VM examples:

Memory Options

The big thing to watch out for with regard to memory allocation is memory ballooning. Basically this is when the hypervisor has to pull memory away from another VM to give it to another who needs it. This will bog down performance! Even a little (any positive value) will be very noticeable!

Remember not to over-commit memory to any guest VM!

You have the option to set a memory reservation. Here is depends on what guests are running on the ESXi host. I tend to not set a memory reservation but there are times when it can be a good thing.

Just exercise caution here and setting a memory reservation can affect the performance of other VMs. Section 3.7.3 explains the balloon driver well.

Storage Considerations

The most common method is to store the virtual machine file system on a shared storage subsystem.

Make sure to use the paravirtual SCSI adapters as your disk controller. For the VM executing SQL Server, be sure to split up your disks for your database files, transaction logs, and tempdb. This will reduce I/O contention.

Virtual Networking

Virtual Networking best practices are in section 3.9.2. It spells out the guidelines for provisioning network to a SQL Server VM. Enable jumbo frames for vSphere – especially when performing a migration using vMotion.

Guest VM Configuration

We talked about a few applicable things above that apply to the guest VM level. For example: always set the power option to high performance and  use a PVSCSI adapter for your disk controller driver.

Many organizations have policies that state every server must have AV scans – even SQL Server. This can be argued if it is a wise choice or wasted operation. However, if you must run anti virus scans on your SQL Server then please white-list the database files and binaries.

Memory allocation to SQL Server is similar to physical. Typically we set the minimum memory allocated to SQL Server to zero and the max at an appropriate value. For tier 1 mission critical systems, VMware suggests setting both min and max memory to the same value. Additionally they recommend using LPIM (locked pages in memory).

For CTFP and MAXDOP the advice is the same as with a physical server. You can read about all the best practices for configuring SQL Server: SQL Server Environmental Diagnostics Guide.

High Availability – SQL Server on VMware

You can utilize any of the HA features available for SQL Server while virtualized.

Here is a table summarizing the choices:

SQL Server has a relatively large install base among RDBMS products. For the past 20 years organizations have been increasingly been using virtualization to reduce cost and more efficiently use their compute resources. VMware is the leader in the field – that is by far the most common hypervisor out in the wild. Therefore, a good DBA needs to know a little about virtualization. Otherwise you are missing out.

Thanks for reading!

If you liked this post then you might also like: SQL Server Environmental Diagnostics Guide – VMware