2

I have two sites: SiteA and SiteB. Both sites are running SQL Server 2017 Enterprise v14.0.3048.4 on Windows 2012. Both sites are linked with a dedicated VPN connection. We have a series of availability groups created: AG1-9 at each site with the intention of synchronizing data from siteA to siteB using distributed availability groups. All of the AG listeners are configured to listen on 1433. The SQL 2017 instances are using 5023 as the hadr endpoint and the SQL 2012 instance on the same cluster is using 5022. The SQL instance listens on another port.

SiteA is a colo that we manage. There is a 2 node cluster with SQL Server installed as a failover cluster instance (FCI) for fault tolerance.

SiteB uses 2 EC2 instances in AWS. The AGs are configured for synchronous commit.

When the distributed availability group is created, originally siteA's Clustered SQL Name was used in the endpoint_url (tcp://sqlname:5023) and for siteB the availability group listener name was used in the endpoint_url (tcp://listener:5023).

This worked fine.

The plan is to expand siteA so there is a secondary replica in the local AG for data redundancy and to support local read-only copies. In order to support this, siteA's endpoint_url in the distributed AG would need to point to the listener (tcp://listener:5023) similarly to how siteB is configured.

This was configured and working as of about 2 weeks ago. Now a change was needed so 2 of the distributed AGs needed to be recreated, and now the communication is broken. Whenever siteB tries to communicate back to siteA there is a timeout error:

2019-07-22 08:16:32.790 spid126s A connection timeout has occurred while attempting to establish a connection to availability replica 'SiteA_AG5' with id [906D00AA-8A78-5532-47F1-2167C5C4A154]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.

A telnet to the listener over port 5023 fails

telnet SiteA_Listener5 5023

Telnets to SiteB's listeners work fine. This only seems to affect telnets to the listeners that exist inside the FCI. Telnets to SiteA's Clustered SQL Name over 5023 succeed.

Is a failover clustered instance with AG listeners listening on 1433 supposed to be able to receive requests from incoming connections over 5023?

If this does not work, then adding a read only replica to our FCI will not be possible.

SiteA Config:

Nodes: SiteA_Node1 SiteA_Node2

FCI SQL Name: SiteA_SQLFCI (dynamic sql port)

AG Listeners: SiteA_AG1, SiteA_AG2, SiteA_AG3, etc... (port 1433)

SiteB Config:

Nodes: SiteB_Node1 SiteB_Node2 (dynamic sql port)

AG Listeners: SiteB_AG1, SiteB_AG2, SiteB_AG3, etc... (port 1433)

telnet SiteA_SQLFCI 5023 (works)

telnet SiteB_Node1 5023 (works)

telnet SiteA_AG1 5023 (fails)

telnet SiteB_AG1 5023 (works)

The really weird part is that I found out that I am able to run this command

telnet SiteA_AG1 5022 (works)

the problem with that is the AG listener exists in the SQL 2017 instance which is using 5023 as the HADR port, not 5022. The SQL 2012 instance is using 5022.

I'm really confused as to what's going on here. any help is appreciated

  • just to add to this. the existing stuff that was setup ~2 weeks ago is still using the AG listener name and even after a service restart it reconnects even though the AG listeners only respond over 5022 via telnet which is even more confusing to me. I checked the configs like 3x – Alf47 Jul 25 at 13:00

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.