Configuring Storage Spaces for Azure SQL IaaS

One way to use SQL in Azure is to deploy SQL IaaS by installing SQL on an Azure VM. This post goes over provisioning disk system in Azure optimized for SQL. In this demo we’re using a DS14v2 size VM, but any VM size that supports 16 data disks is OK. To see the VM SKU’s that support 16 or more disks in a given Azure location we can use the PowerShell cmdlet:

Get-AzureRmVMSize -Location 'eastus' | 
    where MaxDataDiskCount -GE 16 | sort MaxDataDiskCount

and we’ll see a list like

We deployed a VM using the Microsoft Gallery image of the latest 2016 server version, using an unmanaged OS disk in a storage account configured as:

• Premium
• LRS
• GPv2
• Hot

Attach 16 disks:

Next we provision and attach 16 one TB unmanaged SSD disks (page blobs) using the following PowerShell code:

$VMName = 'myVMName'
$RGName = 'myResourceGroupName'
0..15 | foreach {
    $VM = Get-AzureRmVM -ResourceGroupName $RGName -Name $VMName
    $DataDiskName = "$VMName-DataDisk-$_"
    $OSDiskUri    = $VM.StorageProfile.OsDisk.Vhd.Uri
    $DataDiskUri  = "$($OSDiskUri | Split-Path)\$DataDiskName.vhd".Replace('\','/')
    $ParameterSet = @{
        VM           = $VM 
        Name         = $DataDiskName 
        Caching      = 'ReadWrite' 
        DiskSizeInGB = 1023 
        Lun          = $_ 
        VhdUri       = $DataDiskUri 
        CreateOption = 'Empty'
    }
    $VM = Add-AzureRmVMDataDisk @ParameterSet
    Update-AzureRmVM -ResourceGroupName $RGName -VM $VM
}

Create Storage Pool:

Next we RDP to the VM and provision a storage pool. In Server Manager under File and Storage Service/Volumes/Storage Pools we should see the 16 disks under the default ‘Primordial’ pool. We can right click on that and create new pool. The important thing here is to select ‘Manual’ disk allocation for each of the 16 disks. This is important since this is an all-SSD pool. The default setting will cause the system to reserve all 16 disks for Journal and we won’t be able to create any virtual disks.

The same task can be performed via PowerShell as follows:

$PoolName = 'SQLPool'
$ParameterSet = @{
    FriendlyName = $PoolName 
    StorageSubSystemFriendlyName = (Get-StorageSubSystem).FriendlyName 
    PhysicalDisks = Get-PhysicalDisk –CanPool $True
}
New-StoragePool @ParameterSet
Get-PhysicalDisk -FriendlyName 'Msft Virtual Disk' | 
    Set-PhysicalDisk -Usage ManualSelect # otherwise all SSD disks will be reserved for journal

Create Virtual disks and volumes:

Finally we create virtual disks and volumes as follows:

This particular configuration uses 8 out of the 16 TB in the pool leaving 8 TB for future virtual disk growth. A Virtual disk can be expanded in Server Manager, to be followed by volume expansion in Disk Management tool.

The virtual disks in this configuration can survive a single disk failure being in a 2-way mirror. Although this is almost not a practical concern given that the 16 disks are triple redundant (each block of each disk is synchronously written to 3 physical underlying disks)

2 way-mirrored virtual disks also enhance read performance since read operations occur against one of the 2 disks in a mirrored space.

In the data/log/temp virtual disks, the Interleave size has been dropped to 64KB down from the default 256KB since SQL writes are 8-32KB. With 8 columns, this makes the data stripe size (64 * 8) 512KB