Environment

-> Database server JBSAG1 and JBSAG2 are on primary datacentre located in East US.

-> Database server JBSAG3 is on secondary datacentre in Korea Central.

-> Database servers JBSAG1, JBSAG2 and JBSAG3 are part of failover cluster without shared storage.

-> Availability group status on Synchronous secondary JBSAG2 shows as “Not Synchronizing”,

-> I checked the SQL Server error log on JBSAG1 and JBSAG2,

JBSAG1

_{2021-02-25 15:05:28.760 spid45s A connection timeout has occurred while attempting to establish a connection to availability replica ‘JBSAG2’ with id [531FB0D2-4930-4E75-B1FE-2875DCCB9FFB]. 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.
2021-02-25 15:06:07.730 spid24s A connection for availability group ‘JBSWIKI’ from availability replica ‘JBSAG1’ with id [1B5043DB-0107-4299-A2BA-9D156E056542] to ‘JBSAG2’ with id [531FB0D2-4930-4E75-B1FE-2875DCCB9FFB] has been successfully established. This is an informational message only. No user action is required.
2021-02-25 15:06:07.730 spid45s Always On Availability Groups connection with secondary database established for primary database ‘JBDB’ on the availability replica ‘JBSAG2’ with Replica ID: {531fb0d2-4930-4e75-b1fe-2875dccb9ffb}. This is an informational message only. No user action is required.
2021-02-25 15:06:31.850 spid45s Always On Availability Groups connection with secondary database terminated for primary database ‘JBDB’ on the availability replica ‘JBSAG2’ with Replica ID: {531fb0d2-4930-4e75-b1fe-2875dccb9ffb}. This is an informational message only. No user action is required.
2021-02-25 15:06:33.350 spid73s DbMgrPartnerCommitPolicy::SetSyncState: 531FB0D2-4930-4E75-B1FE-2875DCCB9FFB:1
2021-02-25 15:06:41.870 spid24s A connection timeout has occurred while attempting to establish a connection to availability replica ‘JBSAG2’ with id [531FB0D2-4930-4E75-B1FE-2875DCCB9FFB]. 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.}

JBSAG2

_{2021-02-25 15:06:31.860 spid26s A connection timeout has occurred on a previously established connection to availability replica ‘JBSAG1’ with id [1B5043DB-0107-4299-A2BA-9D156E056542]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
2021-02-25 15:06:31.860 spid26s Always On Availability Groups connection with primary database terminated for secondary database ‘JBDB’ on the availability replica ‘JBSAG1’ with Replica ID: {1b5043db-0107-4299-a2ba-9d156e056542}. This is an informational message only. No user action is required.
2021-02-25 15:06:41.860 spid26s A connection timeout has occurred while attempting to establish a connection to availability replica ‘JBSAG1’ with id [1B5043DB-0107-4299-A2BA-9D156E056542]. 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.}

-> Lets execute below query on Primary replica JBSAG1 and check if we are able to get some details,

select r.replica_server_name, r.endpoint_url,
rs.connected_state_desc, rs.last_connect_error_description,
rs.last_connect_error_number, rs.last_connect_error_timestamp
from sys.dm_hadr_availability_replica_states rs join sys.availability_replicas r
on rs.replica_id=r.replica_id

-> Below error can be found for replica JBSAG2,

DNS lookup failed with error: ‘11001(No such host is known.)’.

-> If we look at the endpoint_url in above output, we are able to see a “space” for JBSAG2 endpoint_url “TCP://JBSAG2.JBS.COM :5022”,

-> Use below query to correct the endpoint URL,

USE MASTER
GO
ALTER AVAILABILITY GROUP [JBSwiki] 
MODIFY REPLICA ON 'JBSAG2' WITH (ENDPOINT_URL = 'TCP://JBSAG2.JBS.COM:5022');

-> Everything seems fine after above change,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.