Simplify Always On availability group deployments on Azure VM with SQL VM CLI

Always On availability groups (AG) provide high availability and disaster recovery capabilities to your SQL Server database, whether on-premises, in the cloud, or a combination of both. Manually deploying an availability group for SQL Server on Azure Virtual Machines (VM) is a complex process that requires understanding of Azure’s infrastructure, but new enhancements have greatly simplified the process.

Deploying an Always On AG configuration for SQL Server on Azure VM is now possible with following simple steps.

Define Windows Failover Cluster metadata

az sql vm group manages the metadata about the Windows Failover Cluster service that will host the Always On AG. Cluster metadata includes the Active Directory (AD) domain, cluster accounts, and the storage account to be used as cloud witness and SQL Server version. Use az sql vm group create to define the Windows Failover Cluster metadata so that when the first VM is added, the cluster will be created as defined. An example command is provided below.

We only support AD domain joined Windows Failover Cluster definition. The FQDN is a must have property and all AG replicas should already be joined to the AD domain before they are added to the cluster.

You can use any existing storage account as a Cloud Witness in the cluster, or you can create a new storage account. An example an Azure CLI command to create the storage account is below:

Add SQL VMs to the Cluster – Adding the first VM will create the cluster

az sql vm add-to-group manages adding AG replicas to the Windows Failover Cluster defined above. The cluster is created when the first VM is added to the group. Installing of cluster role on the VM and creating the cluster with the given name is automated by this command. Following add-to-group calls will add next replica to the Cluster.

You can deploy a new SQL VM instance from Enterprise SQL Server 2016 or 2017 images on Azure Marketplace to use as AG replicas. If you deploy SQL VM from the Azure portal, then it will have the SQL IaaS extension installed and registered with SQL VM RP by default. If you deploy with Azure Power Shell, CLI, or from a non-SQL Server image, you will need to manually follow these steps:

You can add an existing SQL VM to the cluster as AG replicas if these prerequisites are followed.

Create an Availability Group through SSMS

Once all SQL VMs are added to the cluster, you can log in to one of them and setup the availability group through SSMS new Availability Group Wizard. At this point, creating the availability group is very simple as all replicas are already added to the cluster.

Create an Availability Group Listener

The last step in the Always On AG configuration is creating an AG Listener to enable automated connection routing after a failover. You can create an AG Listener with the az sql vm ag-listener create command, as shown below.

AG Listener requires an Internal Load Balancer (ILB) on Azure VMs. If your SQL VMs are in the same availability set, then you can use a Basic ILB, otherwise you need to use a Standard ILB. You can create the ILB via Azure CLI as shown in the example below.