SQL Server - Connectivity issues

Compatible from SQL Server 2012

Return actual state of local replica connectivity to other replicas in Availability Group. Can be useful for fast check or in some automation or inventory solution.

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
	INNER JOIN sys.availability_replicas r ON rs.replica_id = r.replica_id
WHERE rs.is_local = 1

Results

Script results screen:

Results screen

Script messages after execution:

("n" rows affected)

Script results detailed description:

Column nameData typeDescription
replica_server_namenvarchar(256)Server name of the instance of SQL Server that is hosting this replica and, for a non-default instance, its instance name.
endpoint_urlnvarchar(128)String representation of the user-specified database mirroring endpoint that is used by connections between primary and secondary replicas for data synchronization.
connected_state_descnvarchar(60)Description of connection_state, one of: DISCONNECTED CONNECTED
last_connect_error_descriptionnvarchar(1024)Text of the last_connect_error_number message.
last_connect_error_numberintNumber of the last connection error.
last_connect_error_timestampdatetimeDate and time timestamp indicating when the last_connect_error_number error occurred.

Leave a Reply

Your email address will not be published. Required fields are marked *