Extending SQL Server 2014 AlwaysOn Resource Groups with Storage Spaces on Microsoft Azure
The new Azure Preview Portal makes it super-easy to configure a highly available SQL Server 2014 AlwaysOn Availability Group cluster with a new Azure Resource Group Template. After completing 4 fields of information and clicking a single Create button, a complete cluster environment including an Azure Storage Account, Virtual Network, Active Directory Domain Controllers and SQL Server 2014 AlwaysOn cluster will be automatically configured. Of course, if you wish to customize any of the options, you're given the ability to do that too - but that's a few extra clicks! ;-)
When the cluster provisioning process is completed, your new cluster will look like this:
SQL Server 2014 AlwaysOn Resource Group Template
Great! But how do I add more storage?
When the SQL Server 2014 AlwaysOn cluster is built using the Resource Group template above, each SQL Server VM instance will be provisioned with two additional data disks that can each grow up to 1TB in total size: one data disk for database files, and one data disk for transaction logs.
In many scenarios, you may wish to add additional data disks to each SQL Server VM instance, for extra capacity and/or throughput. To improve throughput, one best practice is to use Storage Spaces to create simple volumes that are striped across each data disk. However, when attempting to create a new Storage Spaces pool, you may encounter the warning displayed in the output below.
Clustered Storage Spaces Warning
After the SQL Server VM instances are clustered during the initial provisioning, the servers expect that all new storage added to these VMs will be shared storage that is available to all cluster nodes. Since the newly added storage is not shared across cluster nodes, Server Manager displays a warning that prevents you from proceeding with the necessary configuration tasks.
SQL Server AlwaysOn Availability Groups use "shared nothing" storage in the cluster, so we can sidestep this issue and be quickly on our way with a just a bit of PowerShell magic.
Disable Automatic Clustering of Storage
Using PowerShell, we can disable automatic clustering of newly added storage with just a couple cmdlets: Get-StorageSubsystem and Set-StorageSubsystem.
# Disable automatic clustering of newly added storage
Get-StorageSubsystem -FriendlyName "Clustered Storage Spaces*" | Set-StorageSubSystem -AutomaticClusteringEnabled $False
Identify New Data Disks
Next, we can use the Get-PhysicalDisk cmdlet to identify our newly added data disks on each SQL Server VM instance.
# Identify newly added virtual hard disks
$dataDisks = Get-PhysicalDisk -CanPool $True
Create New Storage Space
Using the disks that we identified above, we can then run the New-StoragePool, New-VirtualDisk, Initialize-Disk and New-Partition cmdlets on each VM to create a new Storage Space that is not configured as a clustered resource.
# Create new Storage Space
New-StoragePool -FriendlyName "SQLDataPool" -StorageSubsystemFriendlyName "Clustered Storage Spaces*" -PhysicalDisks $dataDisks |
New-VirtualDisk -FriendlyName "SQLDataDisk" -UseMaximumSize -NumberOfColumns $dataDisks.Count -ResiliencySettingName "Simple" -ProvisioningType Fixed -Interleave 65536 |
Initialize-Disk -Confirm:$False -PassThru |
New-Partition -DriveLetter H -UseMaximumSize
Note that these cmdlets are piped together into a single PowerShell execution pipeline.
Format as New Volume
After our Storage Space is created, we'll use the Format-Volume cmdlet to format the Storage Space as a new NTFS volume that's suitable for locating our SQL Server database files and/or transaction logs.
# Format as a New Volume
Get-Partition -DriveLetter H |
Format-Volume -FileSystem NTFS -NewFileSystemLabel "SQLDataVol" -AllocationUnitSize 65536 -Confirm:$False
It's all about the results!
In my Azure subscription, I created a Storage Space as a Simple (aka. Striped) volume using 5 data disks located on Azure blob storage. After completing these steps, I used IOMeter to confirm the performance of my new storage configuration.
Storage Space with 5 virtual hard disks on Azure VM
Note that these virtual hard disks were located on Azure blob storage to provide an expected maximum throughput of ~500 IOPS per data disk, for an aggregate maximum throughput of approximately 2,500 IOPS across my 5-disk Storage Space. Faster throughput (over 50,000 IOPS) can be achieved by leveraging Storage Spaces with our new Azure Premium Storage offering, which delivers persistent storage backed by SSD storage devices.
Complete the steps in this article on both SQL Server VM instances within your AlwaysOn cluster, and you'll be ready to store new SQL Server database files and/or transaction logs that you can add to your Availability Group for high availability.