Fix “SQL Availability Replica Disconnected”

If you are using Always On Availability Groups in SQL, you may encounter connectivity issues between nodes if there has been a change in configuration of the nodes. When I encountered this issue, the nodes in question had been transitioned from a commercial firewall application to Windows Defender Firewall, and traffic was not being allowed between the nodes on the port being used for database mirroring.

Thanks to Microsoft for this documentation on relevant troubleshooting:

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-replica-is-disconnected?view=sql-server-ver16

Here is the procedure I used:

• Identify which port is being used for database mirroring:

$server_name = "<SQL SERVER NAME>"
Sqlcmd -S $server_name -E -Q "SELECT type_desc, port FROM sys.tcp_endpoints WHERE type_desc = 'DATABASE_MIRRORING'; "

• While on affected node, attempt connection to remote node via the port identified in step 1:

$computer = "<REMOTE NODE NAME>"
$port = "<PORT #>"  
Test-NetConnection -ComputerName $computer -Port $port

• If connection fails, set explicit allow firewall rules on each node for the port identified in step 1. Do NOT set the explicit allow rule on the public firewall profile.
• Perform step 2 again to confirm proper communication between nodes via the port identified in step 1. The affected node should go into synchronizing (and then synchronized) state once communication is reestablished.