Use PowerShell or Az CLI to configure a multi-subnet availability group for SQL Server on Azure VM

Article
07/12/2024

Tip

There are many methods to deploy an availability group. Simplify your deployment and eliminate the need for an Azure Load Balancer or distributed network name (DNN) for your Always On availability group by creating your SQL Server virtual machines (VMs) in multiple subnets within the same Azure virtual network. If you've already created your availability group in a single subnet, you can migrate it to a multi-subnet environment.

This article describes how to use PowerShell or the Azure CLI to deploy a Windows failover cluster, add SQL Server VMs to the cluster, and listener for an Always On availability group within multiple subnets.

Deployment of the availability group is still done manually through SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL).

While this article uses PowerShell and the Az CLI to configure the availability group environment, it's also possible to do so from the Azure portal, or Manually as well.

Prerequisites

To configure an Always On availability group, you must have the following prerequisites:

An Azure subscription.
A resource group with a domain controller.
One or more domain-joined VMs in Azure running SQL Server 2016 (or later) Enterprise edition in separate subnets in the same virtual network. The VMs are in either the same availability set or different availability zones that have been registered with the SQL IaaS Agent extension.
The latest version of PowerShell or the Azure CLI.
Two available (not used by any entity) IP addresses, in the same subnet as each virtual machine. Each virtual machine will have an IP Address for the availability group listener.
Windows Server Core is not a supported operating system for the PowerShell commands referenced in this article as there's a dependency on RSAT, which is not included in Core installations of Windows.

Permissions

You need the following account permissions to configure the Always On availability group by using the Azure CLI:

An existing domain user account that has Create Computer Object permission in the domain. For example, a domain admin account typically has sufficient permission (for example: account@domain.com). This account should also be part of the local administrator group on each VM to create the cluster.
The domain user account that controls SQL Server.

Create a storage account

The cluster needs a storage account to act as the cloud witness. You can use any existing storage account, or you can create a new storage account. If you want to use an existing storage account, skip ahead to the next section.

The following code snippet creates the storage account:

Azure CLI
PowerShell

# Create the storage account
# example: az storage account create -n 'cloudwitness' -g SQLVM-RG -l 'China North 3' `
#  --sku Standard_LRS --kind StorageV2 --access-tier Hot --https-only true

az storage account create -n <name> -g <resource group name> -l <region> `
  --sku Standard_LRS --kind StorageV2 --access-tier Hot --https-only true

Tip

You might see the error az sql: 'vm' is not in the 'az sql' command group if you're using an outdated version of the Azure CLI. Download the latest version of Azure CLI to get past this error.

# Create the storage account
# example: New-AzStorageAccount -ResourceGroupName SQLVM-RG -Name cloudwitness `
#    -SkuName Standard_LRS -Location China North 3 -Kind StorageV2 `
#    -AccessTier Hot -EnableHttpsTrafficOnly $true

New-AzStorageAccount -ResourceGroupName <resource group name> -Name <name> `
    -SkuName Standard_LRS -Location <region> -Kind StorageV2 `
    -AccessTier Hot -EnableHttpsTrafficOnly $true

Define cluster metadata

The Azure CLI az sql vm group command group manages the metadata of the Windows Server Failover Cluster (WSFC) service that hosts the availability group. Cluster metadata includes the Active Directory domain, cluster accounts, storage accounts to be used as the cloud witness, and SQL Server version. Use az sql vm group create to define the metadata for WSFC so that when the first SQL Server VM is added, the cluster is created as defined.

The following code snippet defines the metadata for the cluster:

Azure CLI
PowerShell

# Define the cluster metadata
# example: az sql vm group create -n Cluster -l 'China North 3' -g SQLVM-RG `
#  --image-offer sql2019-ws2019 --image-sku Enterprise --domain-fqdn domain.com `
#  --operator-acc vmadmin@domain.com --bootstrap-acc vmadmin@domain.com --service-acc sqlservice@domain.com `
#  --sa-key '4Z4/i1Dn8/bpbseyWX' `
#  --storage-account 'https://cloudwitness.blob.core.chinacloudapi.cn/'
#  --cluster-subnet-type 'MultiSubnet'

az sql vm group create -n <cluster name> -l <region ex:chinanorth3> -g <resource group name> `
  --image-offer <sql2019-ws2019> --image-sku Enterprise --domain-fqdn <FQDN ex: domain.com> `
  --operator-acc <domain account ex: testop@domain.com> --bootstrap-acc <domain account ex:bootacc@domain.com> `
  --service-acc <service account ex: testservice@domain.com> `
  --sa-key '<PublicKey>' `
  --storage-account '<ex:https://cloudwitness.blob.core.chinacloudapi.cn/>'
  --cluster-subnet-type 'MultiSubnet'

# Define the cluster metadata
# example: $group = New-AzSqlVMGroup -Name Cluster -Location China North 3 ' 
#  -ResourceGroupName SQLVM-RG -Offer sql2019-ws2019
#  -Sku Enterprise -DomainFqdn domain.com -ClusterOperatorAccount vmadmin@domain.com
#  -ClusterBootstrapAccount vmadmin@domain.com  -SqlServiceAccount sqlservice@domain.com 
#  -StorageAccountUrl '<ex:https://cloudwitness.blob.core.chinacloudapi.cn/>' `
#  -StorageAccountPrimaryKey '4Z4/i1Dn8/bpbseyWX'
#  -ClusterSubnetType 'MultiSubnet'

$storageAccountPrimaryKey = ConvertTo-SecureString -String "<PublicKey>" -AsPlainText -Force
$group = New-AzSqlVMGroup -Name <name> -Location <region> 
  -ResourceGroupName <resource group name> -Offer <SQL201?-WS201?> 
  -Sku Enterprise -DomainFqdn <FQDN> -ClusterOperatorAccount <domain account> 
  -ClusterBootstrapAccount <domain account>  -SqlServiceAccount <service account> 
  -StorageAccountUrl '<ex:StorageAccountUrl>' `
  -StorageAccountPrimaryKey $storageAccountPrimaryKey
  -ClusterSubnetType 'MultiSubnet'

Add VMs to the cluster

Adding the first SQL Server VM to the cluster creates the cluster. The az sql vm add-to-group command creates the cluster with the name previously given, installs the cluster role on the SQL Server VMs, and adds them to the cluster. Subsequent uses of the az sql vm add-to-group command add more SQL Server VMs to the newly created cluster.

The following code snippet creates the cluster and adds the first SQL Server VM to it:

Azure CLI
PowerShell

# Add SQL Server VMs to cluster
# example: az sql vm add-to-group -n SQLVM1 -g SQLVM-RG --sqlvm-group Cluster `
#  -b Str0ngAzur3P@ssword! -p Str0ngAzur3P@ssword! -s Str0ngAzur3P@ssword!
# example: az sql vm add-to-group -n SQLVM2 -g SQLVM-RG --sqlvm-group Cluster `
#  -b Str0ngAzur3P@ssword! -p Str0ngAzur3P@ssword! -s Str0ngAzur3P@ssword!

az sql vm add-to-group -n <VM1 Name> -g <Resource Group Name> --sqlvm-group <cluster name> `
  -b <bootstrap account password> -p <operator account password> -s <service account password>
az sql vm add-to-group -n <VM2 Name> -g <Resource Group Name> --sqlvm-group <cluster name> `
  -b <bootstrap account password> -p <operator account password> -s <service account password>

Use this command to add any other SQL Server VMs to the cluster. Modify only the -n parameter for the SQL Server VM name.

# Add SQL Server VMs to cluster
# example: 

# $sqlvm1 = Update-AzSqlVM -Name SQLVM1 -ResourceGroupName SQLVM-RG
#  -SqlVirtualMachineGroupResourceId $group.Id `
#  -WsfcDomainCredentialsClusterBootstrapAccountPassword Str0ngAzur3P@ssword! `
#  -WsfcDomainCredentialsClusterOperatorAccountPassword Str0ngAzur3P@ssword! `
#  -WsfcDomainCredentialsSqlServiceAccountPassword Str0ngAzur3P@ssword! 

# $sqlvm1 = Update-AzSqlVM -Name SQLVM2 -ResourceGroupName SQLVM-RG
#  -SqlVirtualMachineGroupResourceId $group.Id `
#  -WsfcDomainCredentialsClusterBootstrapAccountPassword Str0ngAzur3P@ssword! `
#  -WsfcDomainCredentialsClusterOperatorAccountPassword Str0ngAzur3P@ssword! `
#  -WsfcDomainCredentialsSqlServiceAccountPassword Str0ngAzur3P@ssword! 

$sqlvm1 = Update-AzSqlVM -ResourceGroupName <Resource Group Name> -Name <VM1 Name> `
   -SqlVirtualMachineGroupResourceId $group.Id `
   -WsfcDomainCredentialsClusterBootstrapAccountPassword <bootstrap account password> `
   -WsfcDomainCredentialsClusterOperatorAccountPassword <operator account password> `
   -WsfcDomainCredentialsSqlServiceAccountPassword <service account password>

$sqlvm2 = Update-AzSqlVM -ResourceGroupName <Resource Group Name> -Name <VM2 Name> `
   -SqlVirtualMachineGroupResourceId $group.Id `
   -WsfcDomainCredentialsClusterBootstrapAccountPassword <bootstrap account password> `
   -WsfcDomainCredentialsClusterOperatorAccountPassword <operator account password> `
   -WsfcDomainCredentialsSqlServiceAccountPassword <service account password>

Configure quorum

Although the disk witness is the most resilient quorum option, it requires an Azure shared disk, which imposes some limitations to the availability group. As such, the cloud witness is the recommended quorum solution for clusters hosting availability groups for SQL Server on Azure VMs.

If you have an even number of votes in the cluster, configure the quorum solution that best suits your business needs. For more information, see Quorum with SQL Server VMs.

Validate cluster

For a failover cluster to be supported by Azure, it must pass cluster validation. Connect to the VM using your preferred method, such as Remote Desktop Protocol (RDP) and validate that your cluster passes validation before proceeding further. Failure to do so leaves your cluster in an unsupported state.

You can validate the cluster using Failover Cluster Manager (FCM) or the following PowerShell command:

Test-Cluster –Node ("<node1>","<node2>") –Include "Inventory", "Network", "System Configuration"

Create availability group

Manually create the availability group as you normally would, by using SQL Server Management Studio, PowerShell, or Transact-SQL.

Important

Do not create a listener at this time because this is done through the Azure CLI in the following sections.

Create listener

After you manually create the availability group, you can create the listener by using az sql vm ag-listener.

The subnet resource ID is the value of /subnets/<subnetname> appended to the resource ID of the virtual network resource. To identify the subnet resource ID:

Go to your resource group in the Azure portal.
Select the virtual network resource.
Select Properties in the Settings pane.
Identify the resource ID for the virtual network and append /subnets/<subnetname> to the end of it to create the subnet resource ID. For example:
- Your virtual network resource ID is: /subscriptions/a1a1-1a11a/resourceGroups/SQLVM-RG/providers/Microsoft.Network/virtualNetworks/SQLVMvNet
- Your subnet name is: default
- Therefore, your subnet resource ID is: /subscriptions/a1a1-1a11a/resourceGroups/SQLVM-RG/providers/Microsoft.Network/virtualNetworks/SQLVMvNet/subnets/default

The following code snippet creates the availability group listener:

Azure CLI
PowerShell

# Create the availability group listener
# example: az sql vm group ag-listener create -n AGListener -g SQLVM-RG `
#  --ag-name SQLAG --group-name Cluster --ip-address 10.0.0.27 `
#  --load-balancer sqlilb --probe-port 59999  `
#  --subnet /subscriptions/a1a1-1a11a/resourceGroups/SQLVM-RG/providers/Microsoft.Network/virtualNetworks/SQLVMvNet/subnets/default `
#  --sqlvms sqlvm1 sqlvm2

az sql vm group ag-listener create -n <listener name> -g <resource group name> `
  --ag-name <availability group name> --group-name <cluster name> --ip-address <ag listener IP address> `
  --load-balancer <lbname> --probe-port <Load Balancer probe port, default 59999>  `
  --subnet <subnet resource id> `
  --sqlvms <names of SQL VM's hosting AG replicas, ex: sqlvm1 sqlvm2>

# example

# $SubnetResource1 = Get-AzVirtualNetworkSubnetConfig -Name "sql-subnet-1" `
#     -VirtualNetwork (Get-AzVirtualNetwork -Name "sqlhavnet" `
#     -ResourceGroupName "SQLVM-RG")

# $SubnetResource2 = Get-AzVirtualNetworkSubnetConfig -Name "sql-subnet-1" `
#     -VirtualNetwork (Get-AzVirtualNetwork -Name "sqlhavnet" `
#     -ResourceGroupName "SQLVM-RG")

# $AgReplica1 = New-AzSqlVirtualMachineMultiSubnetIPConfigurationObject `
#      -PrivateIPAddressSubnetResourceId $SubnetResource1.Id `
#      -PrivateIPAddressIpaddress '10.38.1.25' `
#      -SqlVirtualMachineInstance $sqlvm1.Id

# $AgReplica2 = New-AzSqlVirtualMachineMultiSubnetIPConfigurationObject `
#      -PrivateIPAddressSubnetResourceId $SubnetResource1.Id `
#      -PrivateIPAddressIpaddress '10.38.2.25' `
#      -SqlVirtualMachineInstance $sqlvm2.Id

# New-AzAvailabilityGroupListener -Name 'AGListenerName' `
#     -ResourceGroupName SQLVM-RG -SqlVMGroupName Cluster `
#     -AvailabilityGroupName 'AGName' `
#     -MultiSubnetIPConfiguration $AgReplica1,$AgReplica2


$SubnetResource1 = Get-AzVirtualNetworkSubnetConfig -Name <subnet1 name> `
    -VirtualNetwork (Get-AzVirtualNetwork -Name <virtual network> `
    -ResourceGroupName <resource group name>)

$SubnetResource2 = Get-AzVirtualNetworkSubnetConfig -Name <subnet2 name> `
    -VirtualNetwork (Get-AzVirtualNetwork -Name <virtual network> `
    -ResourceGroupName <resource group name>)

$AgReplica1 = New-AzSqlVirtualMachineMultiSubnetIPConfigurationObject `
     -PrivateIPAddressSubnetResourceId $SubnetResource1.Id `
     -PrivateIPAddressIpaddress <IP Address> `
     -SqlVirtualMachineInstance $sqlvm1.Id

$AgReplica2 = New-AzSqlVirtualMachineMultiSubnetIPConfigurationObject `
     -PrivateIPAddressSubnetResourceId $SubnetResource1.Id `
     -PrivateIPAddressIpaddress <IP Address> `
     -SqlVirtualMachineInstance $sqlvm2.Id

New-AzAvailabilityGroupListener -Name <listener name> `
    -ResourceGroupName <resource group name> -SqlVMGroupName <cluster name> `
    -AvailabilityGroupName <Availability Group Name> `
    -MultiSubnetIPConfiguration $AgReplica1,$AgReplica2

Modify number of replicas

There's an added layer of complexity when you're deploying an availability group to SQL Server VMs hosted in Azure. The resource provider and the virtual machine group now manage the resources. As such, when you're adding or removing replicas in the availability group, there's an additional step of updating the listener metadata with information about the SQL Server VMs. When you're modifying the number of replicas in the availability group, you must also use the az sql vm group ag-listener update command to update the listener with the metadata of the SQL Server VMs.

Add a replica

To add a new replica to the availability group:

Azure CLI

Add the SQL Server VM to the cluster group:


# Add the SQL Server VM to the cluster group
# example: az sql vm add-to-group -n SQLVM3 -g SQLVM-RG --sqlvm-group Cluster `
# -b Str0ngAzur3P@ssword! -p Str0ngAzur3P@ssword! -s Str0ngAzur3P@ssword!

az sql vm add-to-group -n <VM3 Name> -g <Resource Group Name> --sqlvm-group <cluster name> `
-b <bootstrap account password> -p <operator account password> -s <service account password>

Use SQL Server Management Studio to add the SQL Server instance as a replica within the availability group.

Add the SQL Server VM metadata to the listener:

# Update the listener metadata with the new VM
# example: az sql vm group ag-listener update -n AGListener `
# -g sqlvm-rg --group-name Cluster --sqlvms sqlvm1 sqlvm2 sqlvm3

az sql vm group ag-listener update -n <Listener> `
-g <RG name> --group-name <cluster name> --sqlvms <SQL VMs, along with new SQL VM>

Remove a replica

To remove a replica from the availability group:

Azure CLI

Remove the replica from the availability group by using SQL Server Management Studio.

Remove the SQL Server VM metadata from the listener:

# Update the listener metadata by removing the VM from the SQLVMs list
# example: az sql vm group ag-listener update -n AGListener `
# -g sqlvm-rg --group-name Cluster --sqlvms sqlvm1 sqlvm2

az sql vm group ag-listener update -n <Listener> `
-g <RG name> --group-name <cluster name> --sqlvms <SQL VMs that remain>

Remove the SQL Server VM from the cluster:

# Remove the SQL VM from the cluster
# example: az sql vm remove-from-group --name SQLVM3 --resource-group SQLVM-RG

az sql vm remove-from-group --name <SQL VM name> --resource-group <RG name>

Remove listener

If you later need to remove the availability group listener configured with the Azure CLI, you must go through the SQL IaaS Agent extension. Because the listener is registered through the SQL IaaS Agent extension, just deleting it via SQL Server Management Studio is insufficient.

The best method is to delete it through the SQL IaaS Agent extension by using the following code snippet in the Azure CLI. Doing so removes the availability group listener metadata from the SQL IaaS Agent extension. It also physically deletes the listener from the availability group.

Azure CLI
PowerShell

# Remove the availability group listener
# example: az sql vm group ag-listener delete --group-name Cluster --name AGListener --resource-group SQLVM-RG

az sql vm group ag-listener delete --group-name <cluster name> --name <listener name > --resource-group <resource group name>

# Remove the availability group listener
# example: Remove-AzAvailabilityGroupListener -Name AGListener `
#   -ResourceGroupName SQLVM-RG -SqlVMGroupName Cluster

Remove-AzAvailabilityGroupListener -Name <Listener> `
   -ResourceGroupName <Resource Group Name> -SqlVMGroupName <cluster name>

Remove cluster

Remove all of the nodes from the cluster to destroy it, and then remove the cluster metadata from the SQL IaaS Agent extension. You can do so by using the Azure CLI or PowerShell.

Azure CLI
PowerShell

First, remove all of the SQL Server VMs from the cluster:

# Remove the VM from the cluster metadata
# example: az sql vm remove-from-group --name SQLVM2 --resource-group SQLVM-RG

az sql vm remove-from-group --name <VM1 name>  --resource-group <resource group name>
az sql vm remove-from-group --name <VM2 name>  --resource-group <resource group name>

If these are the only VMs in the cluster, then the cluster is destroyed. If there are any other VMs in the cluster apart from the SQL Server VMs that were removed, the other VMs are not removed and the cluster is not destroyed.

Next, remove the cluster metadata from the SQL IaaS Agent extension:

# Remove the cluster from the SQL VM RP metadata
# example: az sql vm group delete --name Cluster --resource-group SQLVM-RG

az sql vm group delete --name <cluster name> Cluster --resource-group <resource group name>

First, remove all of the SQL Server VMs from the cluster. This will physically remove the nodes from the cluster, and destroy the cluster:

# Remove the SQL VM from the cluster
# example: 
# Update-AzSqlVM -ResourceGroupName SQLVM-RG -Name $sqlvm1 `
#     -SqlVirtualMachineGroupResourceId '' 
# Update-AzSqlVM -ResourceGroupName SQLVM-RG -Name $sqlvm2 `
#     -SqlVirtualMachineGroupResourceId '' 

Update-AzSqlVM -ResourceGroupName <resource group name> -Name <VM1 Name> `
   -SqlVirtualMachineGroupResourceId '' 
Update-AzSqlVM -ResourceGroupName <resource group name> -Name <VM2 Name> `
   -SqlVirtualMachineGroupResourceId ''

If these are the only VMs in the cluster, then the cluster will be destroyed. If there are any other VMs in the cluster apart from the SQL Server VMs that were removed, the other VMs won't be removed and the cluster won't be destroyed.

Next, remove the cluster metadata from the SQL IaaS Agent extension:

# Remove the cluster metadata
# example: Remove-AzSqlVMGroup -ResourceGroupName "SQLVM-RG" -Name "Cluster"

Remove-AzSqlVMGroup -ResourceGroupName "<resource group name>" -Name "<cluster name>"

Next steps

Once the availability group is deployed, consider optimizing the HADR settings for SQL Server on Azure VMs.

To learn more, see:

Use PowerShell or Az CLI to configure a multi-subnet availability group for SQL Server on Azure VM

Prerequisites

Permissions

Create a storage account

Define cluster metadata

Add VMs to the cluster

Configure quorum

Validate cluster

Create availability group

Create listener

Modify number of replicas

Add a replica

Azure CLI

Remove a replica

Azure CLI

Remove listener

Remove cluster

Next steps

Additional resources