Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,050,840 hits
  • Select GETDATE()

    April 2018
    M T W T F S S

Help: SQL Server Always On Availability Group Listener not working in Azure SQL VMs (Using Internal Load Balancer – ILB). How to troubleshoot?

Posted by blakhani on April 17, 2018

As many of you might know that currently I have been supporting SQL Server in Azure Virtual Machines. Most of the concepts remains same but one of the common issue which was reported by many customer is about Always On listener connectivity. In this blog we would talk about the possible causes of SQL Server Always On Availability Group Listener not working in Azure SQL VMs.

The most common issue is that the listener got created successfully but it connects only from the server which is primary replica. Note that this issue will not occur in on-premise because there is no ILB needed.

Possible Cause # 1

Many of our customers have come to Microsoft via Azure support ticket, after following below article. (This is the most common issue)

Configure a load balancer for an Always On availability group in Azure

Is there something wrong with the article? No, there is nothing wrong there. Its mostly a oversight when customer miss running below script.

$ClusterNetworkName = "<MyClusterNetworkName>" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
$IPResourceName = "<IPResourceName>" # the IP Address resource name
$ILBIP = "<n.n.n.n>" # the IP Address of the Internal Load Balancer (ILB). This is the static IP address for the load balancer you configured in the Azure portal.
[int]$ProbePort = <nnnnn>

Import-Module FailoverClusters

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

The parameters are explained in the article which I mentioned in the beginning. This is the script which maps probe port and IP which you have defined in the portal. Here is the PowerShell script which I use to figure out above (Thanks to my team member Ghufran)

#--  Probe Check Script:
Get-ClusterResource |`
Where-Object {$_.ResourceType.Name -like "IP Address"} |`
Get-ClusterParameter |`
Where-Object {($_.Name -like "Network") -or ($_.Name -like "Address") -or ($_.Name -like "ProbePort") -or ($_.Name -like "SubnetMask")}

Above script shows us the output which can be used to fill in first PowerShell script.

Possible Cause # 2

Second possible cause if that Load Balancing rules are not configured correctly. This information is also mentioned in the article.


Most of the time two values are set incorrectly “Session persistence” and/or “Floating IP (direct server return)”. If we choose any value other than what mentioned in above image, you would get connectivity issue from passive/other nodes.

Possible Cause # 3

In few customer cases I have seen that they run PowerShell for “Cluster IP Address” also. There is no real need to

Possible Cause # 4

In few cases, we have also seen that probe port was not opened in firewall. You should enable 1433 (SQL Server listening Port), 5022 (Always On Endpoint port), 59999 (Probe Port). If you are using any non-default port then take care of them in firewall.

Possible Cause # 5

If you are using NSG in Azure then make sure ports mentioned in #4 is are open.

This is the list I have compiled based on support cases reported. I will keep adding more items as and when they are reported.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisement

    4 Responses to “Help: SQL Server Always On Availability Group Listener not working in Azure SQL VMs (Using Internal Load Balancer – ILB). How to troubleshoot?”

    1. Ajmeer said

      Thanks a lot Balamukund/Gufran for detailed.

    2. However unrelated to above, but I m seeing multiple checkpoints during dB creation.

      So pls let me know what’s the use of checkpoint at time of dB creation pls

    3. Once this was my favorite site for learning. But now it’s obsolete

    4. Mohamed said

      Thanks a lot , possible cause 2 fixed my issue

    Leave a Reply

    Fill in your details below or click an icon to log in: Logo

    You are commenting using your account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    Connecting to %s

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    %d bloggers like this: