Share via


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

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.

Click to zoom in ...
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.

Click to zoom in ...
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.