Running SQL Server on virtual machines (VMs) has become a popular choice for many organizations. However, it’s important to follow best practices to ensure optimal performance and reliability. In this article, we will discuss some key considerations and recommendations for running SQL Server on VMs.

Operating Systems

When selecting an operating system for your SQL Server VM, there isn’t a significant reason to choose one edition over another. Both Windows 2019 Standard and Datacenter editions support 64 sockets and 24TB of RAM. However, it is recommended to use the Windows “Core” installation without the full UI, as SQL Server VMs should focus on serving clients rather than running resource-hogging applications.

Editions of SQL Server

SQL Server offers different editions with varying capabilities and limitations. SQL Server Express is free but has limitations on RAM, CPU, and database size. SQL Server Standard Edition is the most common edition and provides more resources. SQL Server Enterprise Edition is the most expensive but offers advanced features and scalability options.

vCPUs

When it comes to virtual CPUs (vCPUs), it’s important to consider the number of sockets and cores. SQL Server licensing is typically based on the number of cores, so investing in high-powered CPUs can help reduce licensing costs. It’s also important to ensure that all cores are running at full power for optimal performance.

Memory

Assigning memory to a SQL Server VM requires careful consideration. SQL Server does not like it when the amount of memory assigned to the operating system changes, so it’s recommended to avoid using Dynamic Memory. Instead, reserve all the memory required for SQL Server. The “Lock Pages in Memory” setting is not necessary for SQL Servers running on VMs, especially if RDP sessions are limited.

Disk

When it comes to disk configuration, there is no longer a need for separate volumes for different types of SQL Server files. It’s still recommended to have separate logical volumes for the operating system and SQL Server data and log files. The goal is to ensure sufficient IOPS for optimal performance.

Backups

Backing up a SQL Server VM is crucial, but it’s important to understand that VM snapshots or backups are not the same as database backups. Database backups should be performed using native SQL backups or database-aware third-party applications. It’s essential to have effective backups of the SQL Server data to ensure recoverability.

Miscellaneous Questions

Some common questions related to SQL Server VMs include issues with hot adding CPU or memory, fluctuations in Page Life Expectancy (PLE), and buffer cache hit ratio calculations. These issues can be influenced by factors such as the guest OS version, workload characteristics, and configuration settings. It’s important to consult documentation and seek expert advice for specific scenarios.

By following these best practices, you can ensure that your SQL Server VMs are optimized for performance, reliability, and scalability. Remember to consider the specific requirements of your workload and consult official documentation for the most up-to-date recommendations.

Article Last Updated: 2021-08-27