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.
Nodes: SiteA_Node1 SiteA_Node2
FCI SQL Name: SiteA_SQLFCI (dynamic sql port)
AG Listeners: SiteA_AG1, SiteA_AG2, SiteA_AG3, etc... (port 1433)
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