AlwaysOn Monitoring

Secondary Replica Health

0-NOT SYNCHRONIZING,1-partially healthy,2-healthy

select  synchronization_health secondaryreplicahealth
from  sys.dm_hadr_availability_replica_states
where  role_desc = 'secondary'

Primary Replica Health

--preferred secondary backup

declare @DBName as varchar(150)
declare @preferredReplica as int
SET  @DBName = (select top 1 name from sys.databases where database_id > 4)
SET  @PreferredReplica =(Select master.sys.fn_hadr_backup_is_preferred_replica(@DBName))

 --print @preferredReplica

IF
(@preferredReplica = 0 )
BEGIN
select
synchronization_health Primaryreplicahealth from sys.dm_hadr_availability_replica_states
where role_desc = 'primary'
END

 IF (@preferredReplica = 1 )
BEGIN
select
 99
END

Replica Custernode status
0 = Not joined
1 = Joined, standalone instance
2 = Joined, failover cluster instance

select
distinct  min(join_State) from
sys.dm_hadr_availability_replica_cluster_states
where  join_state_desc ='JOINED_STANDALONE'

--Listener Status

--IP resource ONLINE/OFFLINE state from the WSFC cluster, one of:

-- 0= Offline. IP resource is offline.

-- 1 = Failed. IP resource was being brought online but failed.

-- 2 = Online Pending. IP resource is offline but is being brought online.

-- 3 = Online. IP resource is online.

　

select  'state' = case 
when  state_desc = 'OFFLINE' then 0
when  state_desc = 'FAILED' then 1
when  state_desc = 'ONLINE PENDING' then 2
when  state_desc = 'ONLINE' then 3
end  from
sys.availability_group_listener_ip_addresses

 

Data Loss Time (RPO)