03 June 2014

AlwaysOn Availability Group Listeners with Azure Internal Load Balancer

Microsoft in October 2013 released guidelines for configuring AlwaysOn Availability Group listeners:
http://msdn.microsoft.com/en-us/library/dn425027.aspx

They have also now in May 2014 released the concept of an internal load balancer (ILB):
http://msdn.microsoft.com/en-US/library/dn690125.aspx

If you notice the methodologies for creating the public listener endpoints and adding endpoints to the internal load balancer are very similar as both utilize the Add-AzureEndpoint PowerShell cmdlet.

The goal is now to use this model that is highlighted as being enabled by ILB:



However with the availability group listener handling the routing to the primary or read only secondaries, as appropriate. This avoids the public egress and ingress hop of the initial solution.

To do this you will first need to establish a new regional virtual network or you will get an error when trying to create the internal load balancer. (http://blog.kloud.com.au/2014/05/20/bad-request-internal-load-balancer-usage-not-allowed-for-this-deployment/) There are apparently plans to migrate all the affinity group based virtual networks to the regional format eventually but if you want to set up an internal load balencer, this is the only way at the moment.

This does mean that currently you would have to migrate any existing instances over to the regional network. Thankfully, in my current use case I am primarily creating new VMs.
Additionally, we will be using static IP addresses in the virtual network. To ensure there are not any conflicts with dynamic assignment, the recommended network configuration is to keep the dynamic and static instances in separate subnets. Thus it would be advisable to create a new subnet block for the static assignments. You will also need a separate subnet for the ILB to be created in since the listener can NOT be in the same subnet as the database instances.

The prerequisites to achieve high availability through the next steps are:
  1. Azure PowerShell module (version 0.8.2 or higher)
  2. Azure management certificate with subscription information imported into PowerShell
  3. A new regional virtual network
  4. At least three subnets:
    • Private-Static
    • Private-Dynamic
    • AlwaysOn-Static
  5. At least one Affinity Group
  6. Two Active Directory Domain Controller instances:
    • In the affinity group
    • In the same cloud service
    • In an availability set
    • With static private IP addresses
    • Configured as the DNS servers for the Virtual Network
  7. Two Azure VM's in an availability set with static internal IP addresses running Windows Server 2012+ and SQL Server 2012+ Enterprise and configured to enable AlwaysOn Availability Groups:
    http://msdn.microsoft.com/en-us/library/jj870963.aspx
    Note, if setting up the clusters from scratch this script can save you some time:
    http://gallery.technet.microsoft.com/scriptcenter/Create-WSFC-Cluster-for-7c207d3a
  8. All Windows Update patches should be installed on the SQL servers
  9. Currently you must have at least one publicly accessible endpoint for the instances that are going to be a part of the internal load balancer configuration. I would recommend creating one with a locked down ACL.
  10. A file-share witness needs to be created if you want to avoid having to run a 3rd VM instance to achieve quorum. The most likely candidates to host it are the AD servers, however there are reliability issues if that AD server happens to be unavailable. I am intrigued by the idea of using the new Azure File Service for this purpose (http://msdn.microsoft.com/en-us/library/azure/dn167006.aspx) however it is still currently in Preview at the time of this writing.
Executing a script similar to this:

will create the load balanced endpoint. The syntax is almost identical to the original listener configuration documentation. The IP address specified for the internal load balancer in the script is the IP address that will be used for all inbound traffic to the database instances. This IP address must be from a different subnet than the databases and the cluster. 
The remainder of the configuration is the same starting from Step 3 as it would be for a public load balanced set, as is documented in the original listener configuration document. The only difference really, is replacing the public IP from the cloud service with the ILB IP. This of course also means that any resources that need to access the listener must be on the same virtual network.

Note: The Azure Web Management GUI currently will not allow you to make network changes to the instances in the same cloud service with the internal load balancer.