Environment
-> Database in Alwayson availability group changed to “Not Synchronizing”.
-> Checking SQL Server errorlog,
Primary – JBSAG1
2020-07-12 14:44:18.530 spid60s A connection timeout has occurred on a previously established connection to availability replica ‘JBSAG2’ with id [24CD3A43-55C6-4352-AA13-0B8B1283BA4D]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
2020-07-12 14:44:18.530 spid60s Always On Availability Groups connection with secondary database terminated for primary database ‘JBDB’ on the availability replica ‘JBSAG2’ with Replica ID: {24cd3a43-55c6-4352-aa13-0b8b1283ba4d}. This is an informational message only. No user action is required.
2020-07-12 14:44:18.530 spid67s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 24CD3A43-55C6-4352-AA13-0B8B1283BA4D:4
2020-07-12 14:44:18.720 spid67s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 24CD3A43-55C6-4352-AA13-0B8B1283BA4D:1
2020-07-12 14:44:18.720 spid67s DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: 24CD3A43-55C6-4352-AA13-0B8B1283BA4D:1
2020-07-12 14:44:18.720 spid67s DbMgrPartnerCommitPolicy::SetSyncState: 24CD3A43-55C6-4352-AA13-0B8B1283BA4D:1
2020-07-12 14:44:19.240 spid43s A connection timeout has occurred on a previously established connection to availability replica ‘JBSAG3’ with id [79FF3186-DF81-45FB-A24B-966BC6EE56EA]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
2020-07-12 14:44:19.240 spid43s Always On Availability Groups connection with secondary database terminated for primary database ‘JBDB’ on the availability replica ‘JBSAG3’ with Replica ID: {79ff3186-df81-45fb-a24b-966bc6ee56ea}. This is an informational message only. No user action is required.
Synchronous Secondary – JBSAG2
2020-07-12 14:44:18.530 spid37s Always On Availability Groups connection with primary database terminated for secondary database ‘JBDB’ on the availability replica ‘JBSAG1’ with Replica ID: {208c4c88-7468-4656-97df-4087c5443a74}. This is an informational message only. No user action is required.
Asynchronous Secondary – JBSAG3
2020-07-12 14:44:19.330 spid79s Always On Availability Groups connection with primary database terminated for secondary database ‘JBDB’ on the availability replica ‘JBSAG1’ with Replica ID: {208c4c88-7468-4656-97df-4087c5443a74}. This is an informational message only. No user action is required.
2020-07-12 14:44:29.350 spid28s A connection timeout has occurred while attempting to establish a connection to availability replica ‘JBSAG1’ with id [208C4C88-7468-4656-97DF-4087C5443A74]. 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.
-> Executing below query,
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
where rs.is_local=1Primary – JBSAG1
Synchronous Secondary – JBSAG2
Error Message,
Connection attempt failed with error: ‘10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)’.
Asynchronous Secondary – JBSAG3
Error Message,
Connection attempt failed with error: ‘10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)’.
-> The error message refers to a connection issue between the primary and secondaries.
-> Endpoint uses port 5022. Lets check if port 5022 is not blocked using telnet.
‘telnet’ is not recognized as an internal or external command,
operable program or batch file.
-> Execute below command in powershell to enable telnet.
Install-WindowsFeature -name Telnet-Client
-> Telnet is not working for port 5022. It proves that port 5022 is not open.
-> Let us create a firewall rule to open port 5022.
-> Open “Windows Firewall with Advanced Security” and open “Inbound Rules” on JBSAG1, JBSAG2 and JBSAG3.
-> Wait for 5 mins and check the status now by executing below query,
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
where rs.is_local=1
-> After opening port 5022, everything seems to be working fine.
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.