SQL instance is part of Availability Groups details:
SELECT
AG.name AS [AG_Group_Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC
To get some information about the databases in the Availability Group:
SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole],
dbcs.database_name AS [DatabaseName],
ISNULL(dbrs.synchronization_state, 0) AS [SynchronizationState],
ISNULL(dbrs.is_suspended, 0) AS [IsSuspended],
ISNULL(dbcs.is_database_joined, 0) AS [IsJoined]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
ORDER BY AG.name ASC, dbcs.database_name
List of databases that are secondary in the Availability Group, to be excluded when writes are required:
SELECT DISTINCT
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1
ORDER BY dbcs.database_name
Monitoring AGs in WSFC Cluster:
select * from sys.dm_hadr_cluster
select * from sys.dm_hadr_cluster_members
select * from sys.dm_hadr_cluster_networks
select * from sys.dm_hadr_instance_node_map
select * from sys.dm_hadr_name_id_map
Monitoring AGs:
select * from sys.availability_groups
select * from sys.availability_groups_cluster
select * from sys.dm_hadr_availability_group_states
monitoring availability replicas
select * from sys.availability_replicas
select * from sys.availability_read_only_routing_lists
select * from sys.dm_hadr_availability_replica_cluster_nodes
select * from sys.dm_hadr_availability_replica_cluster_states
select * from sys.dm_hadr_availability_replica_states
select sys.fn_hadr_backup_is_preferred_replica ('DBNAME') -- Used to determine if the current replica is the preferred backup replica.
Monitoring Availability databases :
select * from sys.availability_databases_cluster
select * from sys.databases
select * from sys.dm_hadr_auto_page_repair
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_database_replica_cluster_states
Monitor the availability group listeners:
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_group_listeners
select * from sys.dm_tcp_listener_states
Please refer the below queries for AlwaysOn Availability Groups monitoring reference:
SERVERPROPERTY (Transact-SQL)
sys.availability_group_listener_ip_addresses (Transact-SQL)
sys.availability_group_listeners (Transact-SQL)
sys.availability_databases_cluster (Transact-SQL)
sys.availability_groups (Transact-SQL)
sys.availability_read_only_routing_lists (Transact-SQL)
sys.availability_replicas (Transact-SQL)
sys.dm_hadr_availability_replica_cluster_nodes (Transact-SQL)
sys.dm_hadr_availability_replica_cluster_states (Transact-SQL)
sys.database_mirroring_endpoints (Transact-SQL)
sys.dm_hadr_auto_page_repair (Transact-SQL)
sys.dm_hadr_availability_group_states (Transact-SQL)
sys.dm_hadr_availability_replica_cluster_states (Transact-SQL)
sys.dm_hadr_availability_replica_states (Transact-SQL)
sys.dm_hadr_database_replica_states (Transact-SQL)
sys.dm_hadr_database_replica_cluster_states (Transact-SQL)
sys.dm_hadr_cluster (Transact-SQL)
sys.dm_hadr_cluster_members (Transact-SQL)
sys.dm_hadr_cluster_networks (Transact-SQL)
sys.dm_hadr_database_replica_cluster_states (Transact-SQL)
sys.dm_hadr_database_replica_states (Transact-SQL)
sys.dm_hadr_instance_node_map (Transact-SQL)
sys.dm_hadr_name_id_map (Transact-SQL)
sys.dm_os_performance_counters (Transact-SQL)
sys.dm_tcp_listener_states (Transact-SQL)
sys.fn_hadr_backup_is_preferred_replica (Transact-SQL)
Thanks To: RAJU RASAGOUNDER NZ
SELECT
AG.name AS [AG_Group_Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC
To get some information about the databases in the Availability Group:
SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole],
dbcs.database_name AS [DatabaseName],
ISNULL(dbrs.synchronization_state, 0) AS [SynchronizationState],
ISNULL(dbrs.is_suspended, 0) AS [IsSuspended],
ISNULL(dbcs.is_database_joined, 0) AS [IsJoined]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
ORDER BY AG.name ASC, dbcs.database_name
List of databases that are secondary in the Availability Group, to be excluded when writes are required:
SELECT DISTINCT
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1
ORDER BY dbcs.database_name
Monitoring AGs in WSFC Cluster:
select * from sys.dm_hadr_cluster
select * from sys.dm_hadr_cluster_members
select * from sys.dm_hadr_cluster_networks
select * from sys.dm_hadr_instance_node_map
select * from sys.dm_hadr_name_id_map
Monitoring AGs:
select * from sys.availability_groups
select * from sys.availability_groups_cluster
select * from sys.dm_hadr_availability_group_states
monitoring availability replicas
select * from sys.availability_replicas
select * from sys.availability_read_only_routing_lists
select * from sys.dm_hadr_availability_replica_cluster_nodes
select * from sys.dm_hadr_availability_replica_cluster_states
select * from sys.dm_hadr_availability_replica_states
select sys.fn_hadr_backup_is_preferred_replica ('DBNAME') -- Used to determine if the current replica is the preferred backup replica.
Monitoring Availability databases :
select * from sys.availability_databases_cluster
select * from sys.databases
select * from sys.dm_hadr_auto_page_repair
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_database_replica_cluster_states
Monitor the availability group listeners:
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_group_listeners
select * from sys.dm_tcp_listener_states
Please refer the below queries for AlwaysOn Availability Groups monitoring reference:
SERVERPROPERTY (Transact-SQL)
sys.availability_group_listener_ip_addresses (Transact-SQL)
sys.availability_group_listeners (Transact-SQL)
sys.availability_databases_cluster (Transact-SQL)
sys.availability_groups (Transact-SQL)
sys.availability_read_only_routing_lists (Transact-SQL)
sys.availability_replicas (Transact-SQL)
sys.dm_hadr_availability_replica_cluster_nodes (Transact-SQL)
sys.dm_hadr_availability_replica_cluster_states (Transact-SQL)
sys.database_mirroring_endpoints (Transact-SQL)
sys.dm_hadr_auto_page_repair (Transact-SQL)
sys.dm_hadr_availability_group_states (Transact-SQL)
sys.dm_hadr_availability_replica_cluster_states (Transact-SQL)
sys.dm_hadr_availability_replica_states (Transact-SQL)
sys.dm_hadr_database_replica_states (Transact-SQL)
sys.dm_hadr_database_replica_cluster_states (Transact-SQL)
sys.dm_hadr_cluster (Transact-SQL)
sys.dm_hadr_cluster_members (Transact-SQL)
sys.dm_hadr_cluster_networks (Transact-SQL)
sys.dm_hadr_database_replica_cluster_states (Transact-SQL)
sys.dm_hadr_database_replica_states (Transact-SQL)
sys.dm_hadr_instance_node_map (Transact-SQL)
sys.dm_hadr_name_id_map (Transact-SQL)
sys.dm_os_performance_counters (Transact-SQL)
sys.dm_tcp_listener_states (Transact-SQL)
sys.fn_hadr_backup_is_preferred_replica (Transact-SQL)
Thanks To: RAJU RASAGOUNDER NZ
No comments:
Post a Comment