We have SQL Server 2017 + CU11 Always On set up with one primary and a secondary replica. Suddenly we started facing issues where SQL Server AG listener connection started fluctuating between primary and secondary.

There are no error logs on SQL Server and System events. Also we have setup the AG on single subnet.

Below are the parameters configured for listener.

PS C:\Windows\system32> Get-ClusterResource -Name "SQLListenerName" | Get-ClusterParameter

Object                        Name                          Value                         Type
------                        ----                          -----                         ----
SQLListenerName               Name                          SQLListenerName               String
SQLListenerName               DnsName                       SQLListenerName               String
SQLListenerName               Aliases                                                     String
SQLListenerName               RemapPipeNames                1                             UInt32
**SQLListenerName               HostRecordTTL                 1200**                          UInt32
**SQLListenerName               RegisterAllProvidersIP        0**                             UInt32
SQLListenerName               PublishPTRRecords             0                             UInt32
SQLListenerName               ResourceData                  {1, 0, 0, 0...}               ByteArra
SQLListenerName               StatusNetBIOS                 0                             UInt32
SQLListenerName               StatusDNS                     0                             UInt32
SQLListenerName               StatusKerberos                0             

Additional information: Our servers are on Azure and SQL listener is configured with Azure ILB.

| improve this question | | | | |
  • 2
    You have a single subnet with presumably a listener with a single IPv4 address, I would check the applications' connection string or for DNS issues as I don't believe it would be the listener at play here. – Sean Gallardy - Retired User Dec 25 '18 at 11:27
  • One other thing to look into, is if you have multiple instances on the server, and they are using tcp port 1433. The listener will not be able to work properly if it is also listening on port 1433 in that case, and you might connect to the secondary instead of the primary. More info here: docs.microsoft.com/en-us/sql/database-engine/… – Randi Vertongen Dec 26 '18 at 8:03
  • We are simply trying to connect through SSMS. When we look for properties of connections (Listener), Sometime it shows primary server name and sometime it shows secondary server name even though no failover has happened. Also to cross check the connection, we used powershell command "Invoke-Sqlcmd -ServerInstance SQLListenerName -Query "sELECT @@sERVERNAME". Sometime this also returns the primary server name and sometimes secondary server name. Note: We are trying to access the listener from multiple third party server from same domain. – Abhijeet Mali Dec 26 '18 at 9:08
  • @RandiVertongen We have only one instance. – Abhijeet Mali Dec 26 '18 at 9:13
  • It sounds like you have read only routing enabled, can you confirm and verify? – Sean Gallardy - Retired User Dec 26 '18 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.