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.

We recently published a new method to automate Always On AG deployments on Azure VM with SQL Virtual Machine Resource Provider via Azure quickstart templates. Today, we are proud to share that we have further simplified this automation with Azure SQL VM CLI, the management API for SQL VM resource provider.

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.

az sql vm group create -n <cluster name> -l <region ex:eastus> -g <resource group name> --image-offer <SQL2016-WS2016 or SQL2017-WS2016> --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.windows.net/>’

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:

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

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.

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

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:

1. Install the SQL IaaS extension on the virtual machine.
2. Create a SqlVirtualMachine resource associated with the VM with az sql vm create. An example of this is below:

az sql vm create -n <VM Name> -g <Resource Group Name> -l <region ex:eastus>

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.

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>

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.

az network lb create --name <ILB name> -g <resource group name> --sku Standard --vnet-name <VNet Name> --subnet <subnet name>

That is all to deploy SQL Server on Azure Virtual Machines with Always On AG Configuration. Start taking advantage of these expanded capabilities enabled by SQL VM resource provider and Azure SQL VM CLI today. If you have a question or would like to make a suggestion, you can contact us through UserVoice. We look forward to hearing from you!

Source: Azure Blog Feed