Monitor SQL Server AlwaysOn Availability Groups

By:   |   Comments (11)   |   Related: 1 | 2 | 3 | > Availability Groups


The new AlwaysOn Availability Groups feature of SQL Server 2012 provides DBAs with another option for high availability, disaster recovery or offloading reporting. This tip will look at how we can monitor (SQL Server Management Studio, T-SQL, DMVs and System Monitor counters) and make sure AlwaysOn is functioning as expected once properly configured (see Part 1 or Part 2 for how to setup SQL Server AlwaysOn). 


Using SQL Server Management Studio

We'll first start by taking a look at what SSMS provides for monitoring availability groups. In the past I've tended to shy away from using GUI tools to monitor as they usually don't provide as much information as querying backend system tables directly, but so far in testing this new feature you can get quite a bit of information from the dashboard provided within SSMS. Let's first take a quick look at our already configured availability groups.

SQLTEST1 AlwaysOn Availability Groups Configuration
SQLTEST2 AlwaysOn Availability Groups Configuration

The feature within SSMS for monitoring Availability groups is the Always On Dashboard. This main dashboard can be accessed by right clicking on the "Availability Groups" folder under the "Always On High Availability" folder in Object Explorer.

Show Dashboard from Availability Group folder

After clicking "Show Dashboard" above you get the following tab. This tab shows you at a high level all the availability groups that are configured on this instance, which instance is the primary instance, their failover mode and an issues column which would contain links to troubleshooting documentation if there are any issues.

Availability Groups Main Dashboard

From this tab you can drill down to the Availability Group details by clicking on an availability group name. Alternatively you can also access the details dashboard directly from Object Explorer by right clicking on the availability group name under the "Availability Groups" folder.

Show Availabitlity Group Details Dashboard from Availability Group folder

After you do this you are presented with the following tab. This tab contains pretty much all the information you need to monitor what is going on within each of your configured availability groups.

Availability Group Details Dashboard - AG-App1
Availability Group Details Dashboard - AG-App2

The most important piece of information on this tab is the availability group state. You can see here that for both of our groups this status is "Healthy". Other information in this top section is just a repeat of the information from the summary tab. The Availability replica section of this tab lists all of the instances that are part of this availability group, whether they are the primary or secondary instance and each instances synchronization state. An important thing to mention is that if your availability mode is set to "Asynchronous commit" then the secondary server will never show the synchronization state as synchronized, it will always be synchronizing. This is due to the fact that in this availability mode the secondary database tends to lag behind its corresponding primary database (although there could be points in time where it does catch up to the primary). Under the grouping section, we get a listing of all the databases in each instance that are a part of the availability group. This grouping can be changed and grouped based on the following parameters: Availability replicas, Availability databases, Synchronization state, Failover readiness or Issues. While the screen captures above show the default view, another great feature of this dashboard is that it's configurable. There are many different settings/statistics that can be added to this view which provide even more detail on the current state of your system. For example, if you are running in Asynchronous commit mode you can use the "Last Hardened LSN" value to see how far your secondary databases are lagging behind (if at all). These additional columns can be added by right clicking on either of the column headings as shown below.

Availability Group Details - Replica Column Options
Availability Group Details - Replica Group by Column Options

Another good piece of information that is also available on the top right of this tab is the link to list any events captured by the newly created AlwaysOn_health extended event session. By clicking on the link shown in the image below (first image) you get a list of all the events that have been captured on your system (second image).

Availability Group - Health Events - Link
Availability Group - Health Events - Details

Using TSQL

Now for those of us that prefer having even more control over how the data is displayed or would like to automate monitoring the health of our system, SQL Server provides us with many new DMVs (link includes description of each parameter) that we can use to create our own customized scripts and output. All of the information displayed in the above dashboards can be queried directly using the following DMVs.

select * from sys.dm_hadr_cluster
select * from sys.dm_hadr_cluster_members
select * from sys.dm_hadr_cluster_networks
select * from sys.availability_groups
select * from sys.availability_groups_cluster
select * from sys.dm_hadr_availability_group_states
select * from sys.availability_replicas
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 * from sys.dm_hadr_auto_page_repair
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_database_replica_cluster_states
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_group_listeners
select * from sys.dm_tcp_listener_states

Using System Monitor (Performance Counters)

SQL Server has also added additional performance counters to help us monitor what is going on with our availability groups. Within System Monitor there are two new objects, SQLServer:Database Replica and SQLServer:Availability Replica, which contain a number of different counters that can be collected and used for analysis. For those of us that prefer using TSQL, I include myself in that category, these counters are also exposed through the sys.dm_os_performance_counters DMV. Using the following query we can see exactly what counters are available.

select object_name,counter_name,instance_name,cntr_value
from sys.dm_os_performance_counters
 where object_name like '%replica%'

A complete description of each of the below parameters can be found on the links above but the below result set gives you an idea of what's available.

Availability Groups - Performance Counters
Next Steps
  • Create scripts using DMVs that you can schedule to automatically monitor your AlwaysOn availability groups
  • Include these checks in your daily monitoring scripts
  • Investigate using PowerShell to help monitor/manage AlwaysOn availability groups

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, September 4, 2019 - 3:10:27 AM - Zion Back To Top (82230)


Great post which helped me a lot building my controls, in addition I would like to know how can I trigger replication failures so I can check if my controls work as well.

Thanks in advance

Monday, May 27, 2019 - 5:02:34 AM - Daniele Back To Top (81219)

Hi Ben Snaidero,

could you or someone else, telle me or make a specific post on how to shrink log file, when using AOAG? I found this topic really challenging to maintaining continuity on the service, since the database need to be removed from AOAG for the shrink operation...

Tuesday, July 19, 2016 - 5:07:47 PM - Ben Snaidero Back To Top (41923)



I don't have a script for this information but I assume you could write one fairly easily using the SQL Server Log or the Event Viewer in windows as your source. 

Thanks for reading.


Tuesday, July 19, 2016 - 3:30:20 AM - Deepak Back To Top (41913)


Thanks for article.


How to get number of fail over happened in last month. I wanted it for reports. Is there any script?


Tuesday, November 25, 2014 - 8:30:33 PM - Ben Snaidero Back To Top (35404)

I haven't had to do this myself, I always have admin :), but since all the information comes from the DMVs I'd assume read access to these as well as "VIEW SERVER STATE" and "VIEW DATABASE STATE" rights would do the trick.
Thanks for reading.

Tuesday, November 25, 2014 - 7:26:02 AM - Betty Back To Top (35393)

Hi thanks for this...

Is there a permission setting that can enable non-SA users to see readonly versions of this information?

Tuesday, June 10, 2014 - 5:07:55 AM - chethana Back To Top (32164)

Is there any column in AlwaysOn DMV which could directly say Readable,NON Readable secondary.



Wednesday, April 23, 2014 - 9:38:34 PM - Hugo Back To Top (30489)

Going live with SQL 2012 HA soon and this article has been of immense help.



Wednesday, March 13, 2013 - 2:40:53 AM - Pandian Back To Top (22771)

Excellent and great effort.


Tuesday, March 6, 2012 - 10:27:38 AM - Elango C Back To Top (16263)



Very good one.  This helped me to understand or fetch the inter relationship between the Windows cluster node and the SQL instance name.



Elango C


Monday, January 16, 2012 - 4:13:26 PM - Jeremy Kadlec Back To Top (15668)


Great tip.  I think the screen shots and code examples are great.  I am sure many people are ready to put AlwaysOn through its paces real soon once SQL Server 2012 gets released.

Thank you,
Jeremy Kadlec

get free sql tips
agree to terms