Issues with SQL listener in SQL 2019 Clusterless Availability Group setup

Question

we are trying to set up SQL 2019 Clusterless Availability Group env. We have the sql listerner with IP created by windows team in DNS. I am able to add the listener in AAG. But I am unable to connect to the sql server using the listener. I am able to connect using the primary replica sql instance name. Also unable to ping the listener as well. Trying to figure out what's missing here. Any ideas please? Thx

Answer 1

There could be several reasons why you are unable to connect to the SQL listener in your Clusterless Availability Group setup. Here are a few things you can check:

1. Ensure that the SQL listener IP address is correctly registered in DNS and can be resolved by client machines.
2. Check if the Windows firewall is blocking traffic to the SQL listener IP address. Ensure that the necessary ports are open for SQL Server communication.
3. Verify that the SQL Server service account has permissions to read and write DNS records.
4. Confirm that the SQL listener is configured with the correct IP address and port number.
5. Check the connectivity between the client machine and the SQL listener by pinging the listener IP address.
6. Ensure that the SQL Server service is running on all the replicas in the availability group.
7. Check the error logs on the primary and secondary replicas for any errors related to the listener or connectivity issues.
8. Try connecting to the SQL listener from a different client machine to see if the issue is specific to a particular client.
9. Check if the SQL listener is configured for MultiSubnetFailover. If so, ensure that the necessary registry keys are set on the client machine. I hope these suggestions help you to identify the issue and resolve it.

Answer 2

More exactly how did you set up the listener? I'm not an expert in this but I read https://www.sqlshack.com/read-scale-availability-group-in-a-clusterless-availability-group/, and as I understand it the listener is simply the IP-address for the current primary, and you will need to update it in case of a failover, and that applies to DNS as well.

Answer 3

Hi @SQLDev ,

Clusterless Availability Group is not a high-availability setup. There is no infrastructure to monitor and coordinate failure detection and automatic failover. In an availability Group, SQL Listener contains the IP address and port number of the primary replica. If you do a force failover, you need to drop and create the SQL listener to have an IP address and port of the new primary replica. You might also need to register SQL Listener again in the DNS to point out the correct IP address.

In addition to the link Erland post, please read this MS document:

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/read-scale-availability-groups?view=sql-server-ver16

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it.