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:

1. Always On Availability Groups feature on a Server instance
2. Availability groups
3. Availability replicas
4. Availability databases
5. Availability groups listeners.

To monitor Always On availability groups feature on a server instance

1. To monitor Always On availability groups feature on a server instance, use the following built-in function:

Function Used: Serverproperty

Property: IsHadrEnabled, IsClustered

 T-SQL

Select serverproperty (‘IsHadrEnabled’)

The result will be either 1 or 0

1 = enabled

0= disabled

Monitoring Availability Groups

2. To monitor Always On availability groups for which the server instance hosts an availability replica, use the following views:

sys.availability_groups

T-SQL

Select * from sys.availability_groups

Select * from sys.availability_groups_cluster

Select * from sys.dm_hadr_availability_group_states

Monitoring Availability Replicas

3. To monitor availability replicas, use the following views and system function:

sys.availability_replicas
Returns a row for every availability replica in each availability group for which the local instance of SQL Server hosts an availability replica.

T-SQL

Select * from sys.availability_replicas

Monitoring Availability Databases

4. To monitor availability databases, use the following views:

T-SQL

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:

T-SQL

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.