1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

SELECT replica_server_name
, d.role_desc
, d.connected_state_desc
--, endpoint_url
--, availability_mode_desc
--, failover_mode_desc
--, session_timeout
--, backup_priority
--, secondary_role_allow_connections_desc AS Secondary_Readable
, Pri_Check.pri_Status
--, Pri_Check.Sec_Status
--, Pri_Check.Sync_Status
, d.operational_state_desc
, d.recovery_health_desc
, d.synchronization_health_desc AS Sync_Status
, d.last_connect_error_description AS ErrorMsg
, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), d.last_connect_error_timestamp) AS ErrorDateTime
FROM sys.availability_replicas r
OUTER APPLY (
SELECT 1 AS IsPrimary
, c.ip_address AS listnerIP
, c.state_desc AS listnerStatus
, a.primary_recovery_health_desc AS pri_Status
, a.secondary_recovery_health_desc AS Sec_Status
, a.synchronization_health_desc AS Sync_Status
FROM sys.dm_hadr_availability_group_states a
JOIN sys.availability_group_listeners B
ON A.group_id = B.group_id
JOIN sys.availability_group_listener_ip_addresses c
ON b.listener_id = c.listener_id
WHERE primary_replica = r.replica_server_name
) Pri_Check
JOIN sys.dm_hadr_availability_replica_states d
ON r.replica_id = d.replica_id
/*
WHERE d.role NOT IN (1,2) -- 1:Primary, 2:Secondary, 0:Resolving
OR d.operational_state <> 2 --0 = Pending failover, 1 = Pending, 2 = Online, 3 = Offline, 4 = Failed, 5 = Failed, no quorum
OR d.recovery_health <> 1 --0:In progress. At least one joined database has a database state other than ONLINE ( database_state is not 0).1- Online. All the joined databases have a database state of ONLINE ( database_state is 0).
OR d.synchronization_health <>2 -- 0 = Not healthy. At least one joined database is in the NOT SYNCHRONIZING state.
-- 1 = Partially healthy. Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing.
-- 2= Healthy. All replicas are in the target synchronization state: synchronous-commit replicas are synchronized, and asynchronous-commit replicas are synchronizing.
OR d.connected_state <> 1 --0 Disconnected. The response of an availability replica to the DISCONNECTED state depends on its role, as follows:
-- On the primary replica, if a secondary replica is disconnected, its secondary databases are marked as NOT SYNCHRONIZED on the primary replica, which waits for the secondary to reconnect.
-- On a secondary replica, upon detecting that it is disconnected, the secondary replica attempts to reconnect to the primary replica.
-- 1 Connected
OR d.last_connect_error_number IS NOT NULL
*/