Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,028,397 hits
  • Select GETDATE()

    February 2023
    M T W T F S S

Posts Tagged ‘listener’

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
  • Posted in Always On, AlwaysOn, Azure ILB, listener, Microsoft Azure | Tagged: , , , , , | 4 Comments »

    Tips and Tricks : Creating Linked Server to an Availability Group Listener with ReadOnly routing

    Posted by blakhani on September 30, 2014

    In my recent interaction with customer, I was asked if linked server can be created to an availability group listener? My immediate response was “Yes”. Next question was immediately thrown at me that if they can redirect this listener to secondary replica? This time my little delayed response was – Yes. Provided we use proper connection string. Here is the linked server creation script

     EXEC sp_addlinkedserver @server = N'MyLinkedServer'
        ,@provider = N'SQLNCLI'
        ,@srvproduct = N'SqlServer'
        ,@datasrc = N'MyListener'
        ,@provstr = N'ApplicationIntent=ReadOnly'

    …and it was not working as they expected.

    Whenever we want to use routing of read-only connection via listener and connections are not going to secondary replica then below checklist should help.

    Here is the checklist which I have created.

    • Verify that we are connecting to Listener Name.
    • Verify that the Read-Only routing list (READ_ONLY_ROUTING_LIST) is defined.
    • Verify that the Routing URL (READ_ONLY_ROUTING_URL) of each instance has a proper FQDN and port combination.
    • Verify that ApplicationIntent is specified in the connection string.
    • Verify that the Sync_State is SYNCHRONIZED or SYNCHORNIZING for the secondary replica
    • Verify that the secondary replicas are set to allow connections
    • Verity that the initial catalog is provided in connection string.

    If complete checklist is followed, it should resolve most of the routing problems. Most common mistake which I have seen is to miss creating routing URL and routing list. This may be due to the fact that there is no User Interface available and it has to be done by T-SQL.

    Coming back to original question – I asked them to use proper connection string while creating listener. They followed the checklist and they have missed last item the initial catalog. Here is my version of the script.

    USE [master]
    EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer'
        ,@srvproduct = N'SQL'
        ,@provider = N'SQLNCLI11'
        ,@datasrc = N'MyListener'
        ,@provstr = N'Integrated Security=SSPI;Initial Catalog=Production;Data Source=MyListener;ApplicationIntent=ReadOnly'
        ,@catalog = N'Production'

    How would you test whether the connection is working fine or not? That’s simple.

    select * from openquery(MyLinkedServer,'select @@servername')


    If Server name is returned as secondary replica then routing is working perfectly fine. If you are getting primary replica then routing has a problem. The best way to troubleshoot further would be to use SQLCMD and follow the checklist provided earlier in this blog.

    sqlcmd -S MyListener -E -d Production -K ReadOnly

    Hope this helps!

    P.S. Checklist is explained in detail in my book SQL Server 2012 AlwaysOnPaperback, Kindle

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Posted in Tips and Tricks | Tagged: , , , , , | 13 Comments »