Azure Virtual Machines for SQL Server Usage

One initial, easy step to moving your SQL Server on-premises workloads to the cloud is using Azure VMs to run your SQL Server workloads in an infrastructure as a service (IaaS) scenario. This removes the burden of purchasing and maintaining your hardware, storage and networking infrastructure, while still giving you a very familiar experience with Windows and SQL Server itself.

You will still have to maintain your operating system, SQL Server and databases just like you would in an on-premises scenario. In exchange for this, your databases and applications will work just the same as they would in an on-premises installation, which makes this an easy way to start using Azure.

One important choice you will still have to make is what type and size of Azure virtual machine you want to use for your existing SQL Server workload. Unlike an on-premises scenario, you are going to have much less control over the actual processor choice in the underlying host machine. With an Azure VM, you pick a particular machine series (such as an Esv3 series) and size in a particular Azure region, and then you get whatever processor Microsoft is using for that series and size in that Azure region.

Azure VM Types and Series

Microsoft currently has eight main types of virtual machines designed for different types of workloads. These include Entry level, General purpose, Compute optimized, Memory optimized, Storage optimized, GPU, High performance compute and Confidential secure enclave.

For high performance OLTP SQL Server workloads, the memory optimized type of Azure VMs is usually the best choice. According to Microsoft, “Memory optimized VM sizes offer a high memory-to-CPU ratio that are great for relational database servers.” This gives you lower core counts, with more memory, which is usually what you want for SQL Server, to minimize your license costs and still have good performance.

You can go even further down this path with Constrained vCPU capable VM sizes, where you can constrain the VM vCPU count (to one half or one quarter of the original VM size) to reduce the cost of SQL Server licensing, while maintaining the same memory, storage, and I/O bandwidth as a non-constrained VM. These constrained Azure VMs have a suffix in the name that indicates the number of active vCPUs in the VM.

For example: a regular Standard_E64s_v3 would have 64 vCPU cores, while a constrained Standard_E64-16s_v3 would have only 16 vCPU cores with otherwise identical specifications. The -16s suffix indicates the number of active vCPU cores.

Esv3-series

This series uses the 2.1 GHz Intel Xeon Platinum 8171M (Skylake-SP) processor, which is a special bespoke processor SKU that is not in the public Intel ARK database. It appears to have identical specifications to the 26C/52T 2.1 GHz Intel Xeon Platinum 8170M (Skylake-SP) processor. The M suffix in the model number means that it supports 1.5TB of RAM per socket rather than 768GB of RAM per socket. The Esv3-series is rated at 160-190 Azure Compute Units (ACU).

Note: In some regions, Microsoft uses the older Intel Xeon E5-2673 v4 (Broadwell) bespoke processor in the Esv3-series. Microsoft’s Mine Tokus has a good article where she ran a series of scaled down TPC-E benchmarks against an E64s_v3 VM that was using the older Broadwell processor.

The Esv3-series starts with a Standard_E2s_v3 with 2 vCPU and 16GB of RAM, and it tops out at a Standard_E64is_v3 with 64 vCPU and 432GB of RAM. There are also large differences in storage capacity and throughput between these extremes. BTW, the “i” in the Standard_E64is_v3 naming means that the instance is isolated to hardware dedicated to a single customer. Most of the regular Esv3-series sizes are also available as constrained VMs, which gives you a lot of flexibility for sizing.

If you closely compare Figures 1 and 2, you will see a much more favorable set of choices for vCPU counts, RAM amounts, and max uncached disk throughput with the constrained VM choices in Figure 2.

Figure 1: Regular Esv3-series Specifications (click to enlarge)

In particular, I like the Standard_E16-4s_v3, Standard_E32-8s_v3, Standard_E48-12s_v3, and Standard_E64-16s_v3 sizes for SQL Server since they have an extremely nice combination of relatively low core counts, high memory capacity, and high disk throughput compared to the other available choices in this series.

These are all constrained offerings that use only one quarter of the available vCPU cores of the regular offering. The other choices in Figure 2 all use one half of the available vCPU cores of the regular offering, with the same memory and disk throughput limits, which would double your license cost with lower RAM and disk throughput than you could otherwise get with a better choice.

Figure 2: Constrained Esv3-series Specifications (click to enlarge)

For example, compare the three possible VM choices shown in Figure 3. By choosing wisely among the constrained choices at a particular vCPU count, you can get between 2X and 4X the memory and disk throughput for the same SQL Server licensing cost.

Keep in mind that the compute cost, which includes OS licensing, remains the same as the original vCPU size that the constrained size was derived from.

Figure 3: Comparative Esv3-series 8 vCPU Specifications (click to enlarge)

Esv4-series

Another choice that is well-suited for both OLTP and DW workloads is the Esv4-series. These VMs use the newer 7nm 2.35GHz AMD EPYC 7452 (Rome) processor. This processor has 32C/64T, a base clock of 2.35GHz, a max boost clock of up to 3.35 GHz, 128MB of L3 cache, 128 PCIe 4.0 lanes, and 2TB of RAM capacity. The Azure VMs in this series start out with the Standard_E2as_v4 with 2 vCPU and 16GB of RAM, and it tops out with the Standard_E96as_v4 with 96 vCPU and 672GB of RAM. The Esv4-series is rated at 230-260 Azure Compute Units (ACU).

The AMD EPYC 7452 processor is actually significantly faster for single-threaded CPU performance than the Intel Xeon Platinum 8171M processor according to my estimated TPC-E score calculations. The AMD EPYC 7452 has an estimated TPC-E score/core of 67.64, while the Intel Xeon Platinum 8171M has an estimated TPC-E score/core of 49.55. I believe this is due to the higher IPC and higher base clock speed of the AMD processor.

Figure 4 shows the relevant specifications for most of the Esv4-series VM sizes. From what I can tell, Microsoft is not yet offering constrained versions of the Esv4-series VMs. Hopefully, they will do that in the future.

Figure 4: Selected Esv4-series Specifications (click to enlarge)

Conclusion

When you use Azure virtual machines for SQL Server workloads, you should spend some time evaluating the different Azure VM series and sizes that are available in your preferred region. Microsoft uses different bespoke processors in different VM series and sometimes within the same VM series. Doing this sort of analysis of your available choices for VM series and sizing should be one of your initial tasks. If possible, try to take advantage of the constrained offerings that Microsoft offers, since they offer excellent value for SQL Server.

After you have selected a VM series and size, I would do some quick CPU and storage benchmarks, such as CPU-Z, Geekbench 5, and CrystalDiskMark 7 as an initial sanity check (before you even install SQL Server). This will let you compare your Azure VMs to each other, and to your legacy on-premises hardware and storage. Tim Radney has a great article about this process (for storage).