Agile Snowball


Azure SQL Server VM Best Practices

By Richard Thombs on 4/17/2015

While trying to migrate our database server from a dedicated server to a pair of Azure VMs, we did a lot of research about how to best configure the VMs and SQL Server.

Update: All of these concerns have been rendered moot by the truly excellent Azure Premium Local Storage.

Storage Account configuration

  1. 500 IOPS limit per virtual disk.
  2. 20,000 IOPS limit per storage account.
  3. Connect as many virtual disks to your machine as possible.
  4. Do not place more than 40 highly utilised virtual disks in a single storage account.
  5. Use locally-redundant storage, not geo-replicated storage.
  6. Disable read and write caching

Virtual Machine configuration

  1. Use a 'D' series machine to take advantage of their faster CPUs, higher levels of memory and their local SSDs.

Windows Server configuration

  1. Stripe all the virtual disks together using Storage Spaces.
  2. Number of columns in the stripe should equal the number of virtual discs.
  3. Interleave should be 65536 (64KB).
  4. Partition should be formatted with a 65536 (64KB) cluster size.
  5. Enable instant file initialisation for the SQL Server user.

SQL Server configuration

  1. Use SQL Server 2014's Buffer Pool Extensions to take advantage of the higher speed local D: drive.
  2. Enable database page compression.
  3. Enable locked pages.
  4. Move system databases to data disk.

Digest of Microsoft best practice posts

  1. Configuring Azure Virtual Machines for Optimal Storage Performance (September 2014)
  • 500 IOPS per disk
  • 20,000 IOPS per storage account
  • 40 VHDs per storage account
  • Locally redundant storage
  • Attach maximum number of data discs for the machine size
  • Data disks should be the maximum size available (1Tb)
  • Use the "simple" storage spaces configuration
  • Number of columns must equal the number of attached discs
  • Interleave should be set to 65536 (64KB)
  • Partition should be formatted with a 64Kb cluster size
  1. New D-Series of Azure VMs with 60% Faster CPUs, More Memory and Local SSD Disks (September 2014)
  • Use 'D' series machines
  • Enable Buffer Pool Extensions
  1. Performance Best Practices for SQL Server in Azure Virtual Machines (February 2015)
  • Use minimum Standard Tier A2 for SQL Server VMs.
  • Keep the storage account and SQL Server VM in the same region.
  • Disable Azure geo-replication on the storage account.
  • Avoid using operating system or temporary disks for database storage or logging.
  • Avoid using Azure data disk caching options (caching policy = None).
  • Stripe multiple Azure data disks to get increased IO throughput.
  • Format with documented allocation sizes.
  • Separate data and log file I/O paths to obtain dedicated IOPs for data and log.
  • Enable database page compression.
  • Enable instant file initialization for data files.
  • Limit or disable autogrow on the database.
  • Disable autoshrink on the database.
  • Move all databases to data disks, including system databases.
  • Move SQL Server error log and trace file directories to data disks.
  • Apply SQL Server performance fixes.
  • Setup default locations.
  • Enable locked pages.
  • Backup directly to blob storage.