We are excited to share that a new, automated way to configure high availability solutions for SQL Server on Azure Virtual Machines (VMs) is now available using our SQL VM resource provider.
To get started today, follow the instructions in the table below.
High availability architectures are designed to continue to function even when there are database, hardware, or network failures. Azure Virtual Machine instances using Premium Storage for all operating system disks and data disks offers 99.9 percent availability. This SLA is impacted by three scenarios – unplanned hardware maintenance, unexpected downtime, and planned maintenance.
To provide redundancy for your application, we recommend grouping two or more virtual machines in an Availability Set so that during either a planned or unplanned maintenance event, at least one virtual machine is available. Alternatively, to protect from data center failures, two or more VM instances can be deployed across two or more Availability Zones in the same Azure region, this will guarantee to have Virtual Machine Connectivity to at least one instance at least 99.99 percent of the time. For more information, see the “SLA for Virtual Machines.”
These mechanisms ensure high availability of the virtual machine instance. To get the same SLA for SQL Server on Azure VM, you need to configure high availability solutions for SQL Server on Azure VM. Today, we are introducing a new, automated method to configure Always On availability groups (AG) for SQL Server on Azure VMs with SQL VM resource provider (RP) as a simple and reliable alternative to manual configuration.
SQL VM resource provider automates Always On AG setup by orchestrating the provisioning of various Azure resources and connecting them to work together. With SQL VM RP, Always On AG can be configured in three steps as described below.
| Steps | SQL VM RP resource type | Method to deploy | Prerequisites |
| Step 1 – Windows Failover Cluster | SqlVirtualMachineGroup | Automated – ARM template | VMs should be created from SQL Server 2016 or 2017 Marketplace images, should be in the same subnet, and should join to an AD domain. |
| Step 2 – Availability group | N/A | Manual | Step 1 |
| Step 3 – Availability group Listener | SqlVirtualMachineGroup/AvailabilityGroupListener |
3.1 Manual – Create Internal Azure Load Balancer resource 3.2 Automated – ARM Template Create and Configure AG Listener |
3.1 Manual – None 3.2 Automated – Step 2 |
Prerequisites
You should start with deploying SQL VM instances that will host Always On AG replicas from Azure Marketplace SQL Server VM images. Today, SQL VM resource provider supports automated Always On AG only for SQL Server 2016 and SQL Server 2017 Enterprise edition.
Each SQL VM instance should be joined to an Active Directory domain either hosted on an Azure VM or extended from on-premises to Azure via network pairing. VM instances can be joined to the Active Directory domain manually or by running the Azure quick start domain join template.
All SQL VM instances that will host Always On AG replicas should be in the same VNet and the same subnet.
1. Configure a Windows Failover Cluster
Microsoft.SqlVirtualMachine/SqlVirtualMachineGroup resource defines the metadata about the Windows Failover Cluster, including the version and edition, fully qualified domain name, AD accounts to manage the cluster, and the storage account as the cloud witness. Joining the first SQL VM to the SqlVirtualMachineGroup will bootstrap the Windows Failover Cluster Service; and join the VM to the cluster. This step can be automated with an ARM template available in Azure Quick Starts as 101-sql-vm-ag-setup.
2. Configure an Always On AG
As Windows Failover Cluster service will be configured at the first step, an Always On AG can simply be created via SSMS on the primary Always On AG replica. This step needs to be manually performed.
3. Create an Always On AG listener
Always On AG listener requires an Azure Load Balancer (LB). Load Balancer provides a “floating” IP address for the AG listener that allows quicker failover and reconnection. If the SQL VMs a part of the availability group are in the same availability set, then you can use a Basic Load Balancer. Otherwise, you need to use a Standard Load Balancer. The Load Balancer should be in the same VNet as the SQL VM instances. SQL VM RP supports Internal Load Balancer for AG Listener. You should manually create the ILB before provisioning the AG Listener.
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. As the result of this step, any VM within the same VNet can connect to the Always On AG via the AG Listener name. This step can be automated with an ARM template available on the Azure quick starts as 101-sql-vm-aglistener-setup.
Automated Always On AG with SQL VM RP simplifies configuring Always On availability groups by handling infrastructure and network configuration details. It offers a reliable deployment method with right resource dependency settings and internal retry policies. Try deploying automated Always On availability groups with SQL VM RP today to improve high availability for SQL Server on Azure Virtual Machines.
Start taking advantage of these expanded SQL Server Azure Virtual Machine capabilities enabled by our resource provider 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!