I have helped many customers to solve complex issues in their environment by Comprehensive Database Performance Health Check. Sometimes, the issue looks very complex but once the solution is found it seems very easy. In this blog, we would learn about a situation where the listener is missing in SSMS but working fine in failover cluster manager (cluadmin.msc).
While doing checks of their database, they showed me an interesting situation. Here it goes.
My client had 2 nodes Always On Availability Group on SQL Server 2017 and Windows Server 2016. They noticed that;
- In failover cluster manager, we are able to see Network Name resource for Listener.
- In SQL Server Management Studio (SSMS), we were not able to see anything under “Availability Group Listener”. It was empty!
- Below query also doesn’t show any listener in SQL. (0 rows affected)
SELECT * FROM sys.availability_group_listeners GO
SELECT * FROM sys.availability_group_listener_ip_addresses GO
- We were able to connect to the Listener and it was working fine, even after failover also.
When I asked them the history of the listener creation, they informed me that this was created by Windows Admin team. SQLDBA team couldn’t create listener due to an issue which I have written in my previous blog
When I checked availability group resource in cluster manager, I found that it was not having any dependency on the listener. As soon as dependency was added (no downtime needed) we were able to see the listener in #2 and #3 above.
In short, if you are creating the listener via cluster manager, a dependency must be added to the AG resource in Windows Failover Cluster Manager to make the AG dependent upon the listener. If you create it via SQL Server (using SSMS, T-SQL or PowerShell) you should not face this issue.
Have you seen such a situation in your production? Check it now and fix it!
Reference: Pinal Dave (https://blog.sqlauthority.com)