Simon's SQL

/***********************************************************************************************
**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). 
*/