In this post I’m going to take a closer look at Azure Disk and SQL configuration options in Azure Portal. Microsoft recently added some more configuration options for creating SQL virtual machines using the Azure Resource Manager (ARM) deployment model in the preview portal.
When you create a SQL VM you can specify connectivity options, SQL authentication, automatic patching, automatic backups, key vault integration and storage optimisation. Note this is only for SQL 2014 using ARM and Premium Storage for storage optimisation.
In this post I’m going to focus on the storage optimisation settings, the others are pretty much self explanatory and you can read more about all of the settings here.
Disk Performance
First a detour to talk about VM storage performance, independent of SQL. The type of VM you choose determines how many disks you can connect to a VM, the max IOPS on a disk or VM and whether you can use Premium Storage. You can read the details here but in short size A, D, Dv2, G is limited to 500 IOPS per disk, the number of disks you can have varies greatly from 1 to 64. DS and GS can use Premium Storage with 50 000 and 80 000 max IOPS per VM, the number of disks also varies greatly from 2 to 64. The throughput and IOPS per disk depends on the size of the Premium Storage disk you select, 128GB is 500 IOPS 100MB/s, 512GB is 2300 IOPS 150 MB/s and 1025GB is 5000 IOPS 200MB/s. Azure rounds the size of your disk up to the closest premium disk size, you can read the details here.
SQL Server Specified IOPS
If a single disk doesn’t provide the IOPS you require you can stripe multiple disks together using Storage Spaces to increase performance. This happens automatically when you specify the required IOPS when you create your SQL server VM. In the examples below the max IOPS is limited by the type of VM chosen DS2 or DS14.
Based on the required 51200 IOPS we selected you will see 11 disks added to the DS14 machine in the Storage Manager.
If you run:
Get-VirtualDisk | format-list
from a PowerShell command prompt you will see the virtual disk has 11 columns, meaning data written to the disk will be striped into 11 blocks, each block written to a single underlying disk.
If for some reason you can’t use Premium Storage you can do the same with Standard Storage data disks but you have to do it yourself after the VM is created. When you create the virtual disk make sure the number of columns match the number of disks for maximum performance.
When adding multiple disks to a VM keep in mind the IOPS limit on each disk and also the VM IOPS limit that overrides the disk IOPS limit.
Disk Caching
When you create a disk in Azure you can choose what type of caching you want for the disk.
The data disks (not the OS disk) added to the SQL VM are all set to use read-only cache by default, this might seem counter intuitive to using read/write cache but there is a good reason. SQL uses write-through writes which bypasses cache anyway and goes directly to Azure Storage instead of local cache. Secondly there is no artificial limit imposed on read-only cache thus you can get very good read performance with read-only cache but write cache is limited to 4000 IOPS.
