Configure Automated Availability Deployments for SQL Server on Azure VMs

The high availability architectures are designed to be available even during the hardware, database or network failures. Azure VM instances offer 99.9% availability impacted by the SLA in three scenarios:

  • unplanned hardware maintenance
  • unexpected downtime
  • planned maintenance

To maintain redundancy one can group virtual machines into Availability Sets so that when a planned or unplanned event occurs at least one machine is available. Another layer of protection may be achieved by deploying VM instances across two or more Availability Zones in the same Azure region thus guaranteeing the VM connectivity to at least one instance.

The above protection layers relate to VM instances. In order to get the same SLA for SQL Server on Azure VM one needs to configure high availability solutions for SQL Server on Azure VM. Recently Microsoft introduced an automated method to configure Always On availability groups for SQL Server on Azure VMs with SQL VM resource provider as an alternative to the manual configuration.

Always On Availability Groups can be configured with the help of SQL VM resource provider as displayed in the table below:

* Automated – ARM template

** ARM Template Create and Configure AG Listener

Prerequisites:

  • Start with deploying SQL VM instances that will host Always On AG replicas from Azure Marketplace SQL Server VM images. Only SQL Server 2016 and SQL Server 2017 Enterprise edition are supported as of now.
  • Join each SQL VM instance to an active directory domain either hosted on an Azure VM or extended from on-premises to Azure via network pairing. This can be done manually or via running the Azure quick start domain join template.
  • Make sure all SQL VM instances that will host Always On AG replicas are in the same VNet and the same subnet.
1. Windows Failover Cluster Configuration

The metadata about the Windows Failover Cluster is defined by Microsoft.SqlVirtualMachine/SqlVirtualMachineGroup resource. This includes he version and edition, fully qualified domain name, AD accounts to manage the cluster, and the storage account as the cloud witness. Bootstrap the Windows Failover Cluster Service, and join the VM to the cluster by joining the first SQL VM to the SqlVirtualMachineGroup. This step can be automated with an ARM template available in Azure Quick Starts as 101-sql-vm-ag-setup.

2. Always On AG Configuration

Manually create an Always On AG via SSMS on the primary Always On AG replica.

3. Creation of an Always On AG listener

An Azure Load Balancer is required to create an Always On AG listener. A “floating” IP address for the AG listener that is provided by Azure Load Balancer allows quicker failover and reconnection. It is also possible to use a Basic Load Balancer if the SQL VMs being a part of the availability group are in the same availability set, otherwise, a Standard Load Balancer needs to be used.

Provisioning a Microsoft.SqlVirtualMachine/Sql Virtual Machine Groups/AvailabilityGroupListener resource by giving the ILB name, availability group name, cluster name, SQL VM resource ID, and the AG Listener IP address and name creates and configures the AG listener. SQL VM RP handles the network settings, configures the ILB back end pool and health probe, and finally creates the AG Listener with the given IP address and name. The result will be that any VM within the same VNet can connect to the Always On AG via the AG Listener name. It is also possible to automate this step with an ARM template available on the Azure quick starts as 101-sql-vm-aglistener-setup.

The configuration of the Always On availability groups is simplified by automated Always On AG with SQL VM RP as it handles all the infrastructure and network configuration details.

 

Ready to get started? Get in touch or schedule a demo.