-> JBSAG1 and JBSAG2 are Azure IaaS virtual machines provisioned on East US region.
-> Azure virtual machine JBSAG1 and JBSAG2 are part of Windows cluster JBSWikiClust without shared storage. Azure virtual machine JBSAG1 and JBSAG2 will be configured using Synchronous commit with automatic failover.
-> This environment will have a database called JBSWiki added to availability group JBSAG with Listener JBSAPP.
-> Database Servers JBSAG1 and JBSAG2 will be configured using common Virtual network and subnet configuration.
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.
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.
-> 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.
