Notes From A SQL 2017 Azure Install – Using A Load Balancer As An Availability Group Listener

Posted on by

When setting up SQL Server Availability Groups on Azure virtual machines, one of the requirements is that you use a load balancer for your Availability Group listener.  The load balancer stores the IP address for the listener, and then you configure the cluster to use the IP address from the load balancer for the availability group listener.

Now, I’ll be the first to admit that my networking skills are pretty basic, so the thought of setting up a load balancer for an Availability Group cluster was a little intimidating.  Thankfully, Microsoft has a step by step tutorial called “Configure a load balancer for an Always On availability group in Azure” that takes you through the setup and configuration.  In my case that tutorial got me 99{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3} of the way, below are my notes on the problems I ran into.

Notes:

  • As stated in the tutorial, you need to already have your SQL Server Availability Group built before trying to set up the load balancer listener
  • Make sure your SQL Servers are running under the correct service accounts with the proper permissions (I chose to use Group Managed Service Accounts). If not you will run into issues when adding and configuring the IP address in the Failover Cluster Manager
  • After completing the tutorial, only my primary Availability Group server was able to connect to the listener, none of the other servers could.  If I failed over to another server and it became primary, that server could then connect to the listener, but the previous (now secondary) server could not.  I fixed this by:
    1. Creating an inbound rule in the Windows firewall on each SQL Server that allows the port listed in the load balancer health probe
    2. Adding an entry in the Azure NSG to allow traffic from the “Tag” source of AzureLoadBalancer, specifying the port listed in the load balancer health probe

This tutorial by Microsoft is great, it covers everything you need to get your load balancer listener up and running.  In fact, even thought I ran into a few problems, most of the solutions were listed in the comments of the tutorial!

 

This post is part of an ongoing series of blog posts related to my Azure SQL Server 2017 Migration.

 

(Visited 1,003 times, 1 visits today)