Intro

My notes on managing your own SQL server on your own Azure VMs

Documentation

• Checklist: Best practices for SQL Server on Azure VMs

Tips and Tidbits

• Enable SQL Assessment for SQL Server on Azure VMs and your SQL Server will be evaluated against known best practices and results shown on the SQL VM management page of the Azure portal.

• While running SQL Server on Azure Virtual Machines, continue using the same database performance tuning options that are applicable to SQL Server in on-premises server environments.

• Use VM sizes with 4 or more vCPUs like the E4ds_v5 or higher.

• Use memory optimized virtual machine sizes for the best performance of SQL Server workloads.

• The Edsv5 series, the M-, and the Mv2- series offer the optimal memory-to-vCore ratio required for OLTP workloads.

• OLTP or Online Transaction Processing is a type of data processing that consists of executing a number of transactions occurring concurrently—online banking, shopping, order entry, or sending text messages.

• Place data, log, and tempdb files on separate drives.

  • For the data drive, only use premium P30 and P40 disks to ensure the availability of cache support

  • For the log drive plan for capacity and test performance versus cost while evaluating the premium P30 - P80 disks.

  • Set host caching to read-only for data file disks.

  • Set host caching to none for log file disks.

    • Do not enable read/write caching on disks that contain SQL Server files.

    • Always stop the SQL Server service before changing the cache settings of your disk.

• Read more on disk caching: https://docs.microsoft.com/en-us/azure/virtual-machines/premium-storage-performance#disk-caching

•