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:
Script messages after execution:
("n" rows affected)
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| replica_server_name | nvarchar(256) | Server name of the instance of SQL Server that is hosting this replica and, for a non-default instance, its instance name. |
| endpoint_url | nvarchar(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_desc | nvarchar(60) | Description of connection_state, one of: DISCONNECTED CONNECTED |
| last_connect_error_description | nvarchar(1024) | Text of the last_connect_error_number message. |
| last_connect_error_number | int | Number of the last connection error. |
| last_connect_error_timestamp | datetime | Date and time timestamp indicating when the last_connect_error_number error occurred. |