How to Monitor Always On

Mirza Husain | July 19th, 2017 | Database Mirroring, SQL, SQL Server 2016 |

How to Monitor Always On

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 the Always On availability groups feature on a server instance

  1. To monitor the 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

  1. To monitor the 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

  1. 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

  1. 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.