Introduction

Policy NameAvailability Replica Connection State
IssueAvailability replica is disconnected.
CategoryCritical
FacetAvailability replica

 

Description

This policy checks the connection state between availability replicas. The policy is in an unhealthy state when the connection state of the availability replica is DISCONNECTED. The policy is otherwise in a healthy state.

 

Possible Causes

The secondary replica is not connected to the primary replica. The connected state is DISCONNECTED. This issue can be caused by the following:
  • The connection port might be in conflict with another application.
  • The encryption type or algorithm is mismatched.
  • The connection endpoint has been deleted or has not been started.
  • The transport is disconnected.

Possible Solutions

Following are possible solutions for this issue:
  • Check the database mirroring endpoint configuration for the instances of the primary and secondary replica and update the mismatched configuration.
  • Check if the port is conflicting, and if so, change the port number.
How to resolve this Issue:-


  1. Check in participating Availability replica, SQL Server Service account's are same. in my case it was [adven\SQLService]  
  2.  Service accounts are added in login folder and sysadmin privilege is given on all Nodes.
  3. Then execute below command
in  all participating nodes.


USE [master]
GO

/****** Object:  Endpoint [Hadr_endpoint]    Script Date: 10/1/2015 7:25:03 AM ******/
DROP ENDPOINT [Hadr_endpoint]
GO

/****** Object:  Endpoint [Hadr_endpoint]    Script Date: 10/1/2015 7:25:03 AM ******/
CREATE ENDPOINT [Hadr_endpoint] 
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO


ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO [adven\SQLService]

Then try to refresh the availability replica.. 

it should come up.