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
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133

/***********************************************************************************************
**Object Name: AG_Monitor
**
**Description: Check AG Status
**
**Input Parameters:
**
**Return Value: N/A
**
**Return Result Set: AG Monitoring result
**
**Creator: Simon Cho
**
*************************************************************************************************/

--<ReplicaLevel>

SELECT replica_server_name
	 , d.role_desc
	 , d.connected_state_desc
	 , d.replica_id
	 , d.role_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

--<DBLevel>
SELECT r.replica_server_name
	 , r.replica_id
	 , DB_NAME(DRS.database_id)
	 , DRS.is_primary_replica
	 , drs.synchronization_state
	 , drs.synchronization_state_desc
	 , drs.synchronization_health_desc
	 , drs.database_state_desc
	 , drs.suspend_reason_desc
	 --, drs.is_suspended
	 --, DRS.suspend_reason_desc
	 --, drs.suspend_reason
	 , drs.last_sent_time
	 , drs.last_received_time
	 , drs.log_send_queue_size
	 , drs.log_send_rate
	 , drs.redo_queue_size
	 , drs.redo_rate

	 --, drcs.is_failover_ready
	 --, drcs.is_pending_secondary_suspend
	 --, drcs.recovery_lsn
	 , DATEDIFF(ss, drs.last_sent_time, DRS.last_received_time) AS Last_Log_commit_Duration_sec
  FROM sys.availability_replicas r
  JOIN sys.dm_hadr_database_replica_states DRS
	ON r.replica_id = DRS.replica_id
 --WHERE r.replica_id ='E0CCAF57-F7B8-46D0-BDE4-0EDF1FECE310'
  WHERE drs.synchronization_state NOT IN (2)
  --WHERE 1=1
    --and DB_NAME(DRS.database_id)='abce'
 --   OR drs.synchronization_health <> 2
	--OR DRS.is_suspended=1
	--OR drs.suspend_reason IS NOT NULL
	--OR DATEDIFF(ss, drs.last_sent_time, DRS.last_received_time)>30 -- longer than 30 sec.

/*
<synchronization_state>
0 Not synchronizing.
	For a primary database, indicates that the database is not ready to synchronize its transaction log with the corresponding secondary databases.
	For a secondary database, indicates that the database has not started log synchronization because of a connection issue, is being suspended
							, or is going through transition states during startup or a role switch.
1 Synchronizing.
	For a primary database, indicates that the database is ready to accept a scan request from a secondary database.
	For a secondary database, indicates that active data movement is occurring for the database.
2 Synchronized.
	A primary database shows SYNCHRONIZED in place of SYNCHRONIZING.
	A synchronous-commit secondary database shows synchronized when the local cache says the database is failover ready and is synchronizing.
3 Reverting.
	Indicates the phase in the undo process when a secondary database is actively getting pages from the primary database.Caution noteCaution
	When a database on a secondary replica is in the REVERTING state, forcing failover to the secondary replica leaves the database in a state in which it cannot be started as a primary database.
	Either the database will need to reconnect as a secondary database, or you will need to apply new log records from a log backup.
4 Initializing.
	Indicates the phase of undo when the transaction log required for a secondary database to catch up to the undo LSN is being shipped and hardened on a secondary replica. Caution noteCaution
	When a database on a secondary replica is in the INITIALIZING state, forcing failover to the secondary replica leaves the database in a state in which it be started as a primary database.
	Either the database will need to reconnect as a secondary database, or you will need to apply new log records from a log backup.

<database_state>

0 Not healthy.
	The synchronization_state of the database is 0 (NOT SYNCHRONIZING).
1 Partially healthy.
	A database on a synchronous-commit availability replica is considered partially healthy if synchronization_state is 1 (SYNCHRONIZING).
2 Healthy.
	A database on an synchronous-commit availability replica is considered healthy if synchronization_state is 2 (SYNCHRONIZED)
	, and a database on an asynchronous-commit availability replica is considered healthy if synchronization_state is 1 (SYNCHRONIZING).
*/