Reading Time: 4 minutes

Some time towards the end of Q4 2015, I was tasked with re-designing an Azure environment which had initially be commissioned to host a custom web solution by an outsourced team. The original design lacked any real resilience or high availability, with the solution itself being, for the most part, a collection of single instance servers. For example, SQL offered no HA via clustering or AAGs; no load-balancing had been implemented on the frontends, etc.

After a number of website outages due to server failures and/or high traffic volumes resulting in application crawl, the organisation approached my team with a request of redesigning the infrastructure.

My design was simple; I would continue to utilise Azure’s Infrastructure as a Service (IaaS) for the medium-term, deploy multiple, load-balanced IIS front-ends (which would eventually host the Sitecore Content Delivery nodes), and a simple, two-node SQL Always-On Availability Group (AAG) back-end with witness server.

The first challenge was Azure itself. Cast your minds back twelve months ago, and the Azure platform was experiencing a storm of changes on nearly a daily basis. Throughout the official Microsoft Azure (Implementing Microsoft Azure Infrastructure Solutions: 20533C) course (which I sat just a month previous to this project), a continuous theme of ‘this is the old portal, and this is the new’ kept recurring and, after even after returning from the course, a number of items had already  been added, relocated, or simply removed. You can see the challenge.

Documentation in regards to how Azure interacts with other Microsoft products was another hurdle. You might say, Azure was starting to fight me all the way…

One area which boasted no documentation at the time, and even had the Microsoft Azure team scratching their heads, was the inability for SQL AAG nodes to communicate with their AAG Listener ‘out of the box’. All nodes were located in the same geographical Azure data center and all were housed within the same Cloud Service. All should be working right? Wrong.

After much communication back and forth with Microsoft, they eventually advised that an Internal Load Balancer was required. Nice, I was now back on track. After putting a simple PowerShell script together, as well as a re-configuration on all SQL AAG replica nodes, communication was up, and the Listener was in the game.

I’ll be honest, I’ve configured countless Listeners in our on-prem environment in the last 12 months alone, and the additional work required to get this working in Azure was a bit of a frustration. So, below is the exact procedure I used, fully documented, and now stored in our IT documentation library.

Configure Azure Internal Load Balancer (ILB) Listener for SQL Always On Availability Groups

1. Launch Windows PowerShell

2. Login to Azure using the appropriate credentials by using the below cmdlet.

# Login to Azure
Add-AzureAccount

3. View all available Subscriptions.

# View all available subscriptions
Get-AzureSubscription

4. Select the relevant Subscription.

# Select the relevant subscription
Select-AzureSubscription Suscription_Name

5. Define variables and create Internal Load Balancer using the below script. Note, this can take around 10 minutes to complete.

# Define variables for new Azure Internal Load Balancer
$ServiceName = "Cloud_Service_Name" # Cloud Service containing AAG nodes
$AGNodes = "SQL_Node_A","SQL_Node_B" # AAG nodes, all hold replica databases
$SubnetName = "Subnet_Name" # Subnet name
$ILBStaticIP = "X.X.X.X" # IP assigned to AAG Listener
$ILBName = "ILB_Name" # ILB name you wish to assign

# Create Azure Internal Load Balancer
Add-AzureInternalLoadBalancer -InternalLoadBalancerName $ILBName -SubnetName $SubnetName -ServiceName $ServiceName -StaticVNetIPAddress $ILBStaticIP

# Configure a load balanced endpoint for each AAG node in $AGNodes using ILB
ForEach ($node in $AGNodes)
{
    Get-AzureVM -ServiceName $ServiceName -Name $node | Add-AzureEndpoint -Name "ListenerEndpoint" -LBSetName "ListenerEndpointLB" -Protocol tcp -LocalPort 1433 -PublicPort 1433 -ProbePort 59999 -ProbeProtocol tcp -ProbeIntervalInSeconds 10 -InternalLoadBalancerName $ILBName -DirectServerReturn $true | Update-AzureVM

}

6. Once the above script has completed, the below output should be displayed.

7. Next, launch the Azure portal and browse to each of the SQL AAG nodes. You’ll now be able to see that each node is included within the new load-balanced endpoint and Internal Load Balancer will have been allocated to each AAG node.

8. Finally, on each of the SQL AAG nodes, launch an elevated Windows PowerShell window, and run the below script.

# Define variables
$ClusterNetworkName = "Cluster_Network_Name" # Cluster Network Name
$IPResourceName = "IP_Address_Resource_Name" # IP Address resource name
$ILBIP = "X.X.X.X" # ILB IP Address (AAG Listener IP)

Import-Module FailoverClusters

Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"="59999";"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}

9. Once complete, ensure the AAG Listener port is still set to 1433 via SQL Server Management Studio. The port seemed to have dropped for me following the above script. If this is the case, simply reset to 1433.

10. After a few minutes, launch SQL Server Management Studio and test Listener connectivity by attempting to connect to the Listener name from both SQL nodes.

For more information regarding SQL AAGs, Azure ILBs, etc., a Microsoft KB is now available at https://docs.microsoft.com/en-us/azure/virtual-machines/virtual-machines-windows-classic-ps-sql-int-listener.