How to Monitor Always On Availability Groups Instantly
This blog is based on the monitoring the availability groups, replicas and associated databases by using T-SQL. There are multiple catalogs and dynamic management views (DMV) concerned with Always On availability groups.
Using T-SQL to monitor:
- Always On Availability Groups feature on a Server instance
- Availability groups
- Availability replicas
- Availability databases
- Availability groups listeners.
To monitor Always On availability groups feature on a server instance
- To monitor Always On availability groups feature on a server instance, use the following built-in function:
Function Used: Serverproperty
Property: IsHadrEnabled, IsClustered
Select serverproperty (‘IsHadrEnabled’)
The result will be either 1 or 0
1 = enabled
Monitoring Availability Groups
- To monitor Always On availability groups for which the server instance hosts an availability replica, use the following views:
Select * from sys.availability_groups
Select * from sys.availability_groups_cluster
Select * from sys.dm_hadr_availability_group_states
Monitoring Availability Replicas
- To monitor availability replicas, use the following views and system function:
Returns a row for every availability replica in each availability group for which the local instance of SQL Server hosts an availability replica.
Select * from sys.availability_replicas
Monitoring Availability Databases
- To monitor availability databases, use the following views:
Select * from sys.availability_databases_cluster
Monitoring Availability Group Listeners
5. To monitor the availability group listeners on subnets of the WSFC cluster, use the following views:
Select * from sys.availability_group_listeners
Select * from sys.availability_group_listener_ip_addresses
Note: Friends, it is highly recommended to try all these T-SQL statements and check the result.