Availability Groups and Listeners on Azure Virtual Machines
Availability Groups, released in SQL Server 2012 and enhanced in SQL Server 2014, detect conditions impacting SQL Server availability (e.g. SQL service being down or losing connectivity). When detecting these conditions, the Availability Group fails over a group of databases to a secondary replica. In the context of Azure Infrastructure Services, this significantly increases the availability of these databases during Microsoft Azure’s VM Service Healing (e.g. due to physical hardware failures), platform upgrades, or your own patching of the guest OS or SQL Server.
Client applications connect to the primary replica of an availability group using an Availability Group Listener. The Listener specifies a DNS name that remains the same, irrespective of the number of replicas or where these are located.
For example: Server=tcp:ListenerName,1433;Database=DatabaseName;
To support this in Azure Virtual Machines, the Listener must be assigned the IP address of an Azure Load Balancer. The Load Balancer routes connections to endpoint of the primary replica of the Availability Group.
Internal Availability Group Listeners
Until now, the IP address of the Azure Load Balancer had to be a public IP reachable over the Internet. To restrict access to the listener only to trusted entities, you could configure an access control list for the Load Balancer IP. However, maintaining this list could be cumbersome over time.
To simplify this, you can now configure an Internal Azure Load Balancer. This has an internal IP address reachable only within a Virtual Network. This makes the Listener accessible only to client applications located
- In the same Virtual Network
- In another connected Virtual Network (in the same Azure region or a different Azure region)
- On-premise connected via VPN tunnel
This is depicted in the picture below. An availability group has three replicas, two in Virtual Network 1 and one in Virtual Network 2. The Virtual Networks are connected via a VPN tunnel. The Availability Group has a Listener configured using an Internal Load Balancer. This disallows access outside of the connected Virtual Networks.
To create an Internal Azure Load Balancer execute the Powershell cmdlet Add-AzureInternalLoadBalancer. As depicted below, this cmdlet receives the name of the Load Balancer, the Cloud Service where it’ll be created, and a static IP address in the Virtual Network. This is the internal IP address that should be used for the listener.
Add-AzureInternalLoadBalancer -InternalLoadBalancerName $ILBName -ServiceName $ServiceName -StaticVNetIPAddress $ILBStaticIP
Check our official documentation and start using Internal Availability Groups today!
To learn more about SQL Server in Azure Virtual Machines check our start page.