Environment

-> I was tasked to setup Always on Availability group on a newly provisioned database server.

-> Everything went fine from the “New Availability Group Wizard”, except the last step which was to “Join the Database to Availability Group”. This last step was executing indefinitely.

-> Checked for blockings and could not see anything either on JBSAG1\JBSAG2.

->Checked SQL Server errorlog,

JBSAG1
_{^{Nothing Interesting on SQL Server errorlog.}}

JBSAG2
_{^{2022-02-25 05:58:01.310 spid52 Starting up database ‘JBDB’.
2022-02-25 05:58:01.320 spid52 The database ‘JBDB’ is marked RESTORING and is in a state that does not allow recovery to be run.
2022-02-25 05:58:01.320 spid52 State information for database ‘JBDB’ – Hardened Lsn: ‘(38:114032:1)’ Commit LSN: ‘(0:0:0)’ Commit Time: ‘Feb 25 2022 5:25AM’
2022-02-25 05:58:01.330 spid52 State information for database ‘JBDB’ – Hardened Lsn: ‘(38:114032:1)’ Commit LSN: ‘(0:0:0)’ Commit Time: ‘Feb 25 2022 5:25AM’
2022-02-25 05:58:01.330 spid52 Starting up database ‘JBDB’.
2022-02-25 05:58:01.340 spid52 The database ‘JBDB’ is marked RESTORING and is in a state that does not allow recovery to be run.}}

-> Executed below query on both JBSAG1 and JBSAG2,

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

JBSAG1

JBSAG2

-> Below is the 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.)’.

-> It is clear that that there is a connection issue where JBSAG2 is not able to connect to JBSAG1.

-> Since Always On Connection happens via Endpoint port 5022. Checked if a telnet connection to 5022 works for each of these servers and as expected it failed,

telnet JBSAG1 5022 -- Execute on JBSAG2
telnet JBSAG2 5022 -- Execute on JBSAG1

-> You can request your platforms/Network team to open port 5022 on these servers. Since I got this issue on a LAB environment, I will do it my self on both JBSAG1 and JBSAG2,

Inbound Rules

Outbound Rules

-> Once the above actions are done both on JBSAG1 and JBSAG2. The wizard completed as expected.

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.