Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Extended Events to Monitor SQL Server Availability Groups


By:   |   Last Updated: 2015-02-25   |   Comments (1)   |   Related Tips: More > Availability Groups

Problem

There are various ways you can monitor your SQL Server Availability Groups, but one of the best features that was delivered with SQL Server 2012 is the improved Extended Events. Extended Events can now be configured via SQL Server Management Studio under the Management menu. For this tip I will demonstrate an easy way to monitor the health of your Availability Group via Extended Events.

Solution

Why use Extended Events to monitor the health of your SQL Server Availability Groups? One good reason is that Extended Events are lightweight and Extended Events are already built into the SQL Server engine which means less overhead.  Plus in SQL Server 2012 there is already a simple GUI in SSMS!

For more information on setting up Extended Events for overall system health visit this link.

For this tip I will focus on how to setup the Extended Event for your AlwaysOn Availability Group. I assume that you have an Availability Group completely configured and running. From Object Explorer in SSMS, go to Management > Extended Events > Sessions > AlwaysOn_health. See the figure below.

Using Extended Events to Monitor your Availability Group

Right click on AlwaysOn_health session and click on Start Session. This will start the session to collect data about your AlwaysOn health and start sending the data to the target file.  See the figure below.

Right click on AlwaysOn_health session

After successfully starting the session for your AlwaysOn_health, it should display a small green arrow in the GUI. To visit the properties of this session, you may right click on the session and go to the Properties menu. This will open the Session Properties window dialog box. Here we configure the AlwaysOn_health session. You can proceed with the changes while the session is running, but the changes will take effect after you have stopped and started the session again.

For the General menu in the Session Properties see the figure below.

The General menu in the Session Properties

From this menu, you have the option to automatically start the AlwaysOn_health session when your SQL Server restarts.

For the Events menu, see the figure below.

Automatically start the AlwaysOn_health session

One good thing about Extended Events is that you have the option of what data to collect and send to the target. For this tip we will focus on the critical events first. The five default events for your AlwaysOn are the following:

  1. alwayson_ddl_executed - This event collects data when a data definition language statement is executed, including CREATE, ALTER or DROP.  These actions causes your AlwaysOn configuration to have runtime issues.
  2. availability_group_lease_expired - This event indicates that there is a connectivity issue in your AlwaysOn implementation and when the lease expires it will cause AlwaysOn to failover or go offline.
  3. availability_replica_automatic_failover_validation - This events collects information about the readiness of your databases after a failover event. It would be good to have this data especially if your Availability Group has automatic failovers and you need to troubleshoot why these automatic failovers are happening.
  4. availability_replica_manager_state - This event collects data about the heartbeat of your replica manager.
  5. availability_replica_state_change - This event indicates that your replica state changed maybe caused by creation of Availability Group or failover event.

From the Event library pane on the left side, you may select other events that you are interested to monitor or troubleshoot further. Click on the arrow going to the right side so that the event you selected will move to the list of Selected Events pane on the right.

Then we move to the Data Storage menu, figure below.

The Data Storage menu

This is where you need to configure the target where to send your collected data. Click on Add and key in the target file for your events.

For the Advanced settings for your AlwaysOn_session, go to Advanced menu. See the figure below.

Advanced settings for your AlwaysOn_session

This is where you setup the memory usage for the session. Most of the options here are disabled by default.

To read the data collected from your AlwaysOn_health session, there is a built in function sys.fn_xe_file_target_read_file(). Basic use of the function is as below:

SELECT  *
FROM sys.fn_xe_file_target_read_file('T:\Local Dump\ExtendedEventLogs\XE_Default_Trace*.xel', null, null, null) AS F;

From the above script, it will read all the .xel files within the T:\Local Dump\ExtendedEventLogs\ folder path.

Hope this simple tip will make you want to explore more about how Extended Events can help you monitor the health of your AlwaysOn setup and also the overall system health of your SQL Server. It would be good to gradually move your monitoring and tracing to Extended Events as SQL Profiler and Trace is being gradually deprecated by Microsoft in the upcoming releases of SQL Server. So now is a good time!

Next Steps
  • Check out these additional resources:
    • For more tips on AlwaysOn and Availability Groups, visit this link.
    • For more tips on Extended Events, visit this link.


Last Updated: 2015-02-25


get scripts

next tip button



About the author
MSSQLTips author Carla Abanes Carla Abanes works for a private bank in Singapore as a SQL Server DBA.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, September 07, 2017 - 10:48:31 AM - Des Back To Top

M question is out of this tip I hope you can help me.

First I would like to thank you all your positive support for every one on your blog.

I already build 2016 SQL server Alwayson availability (AAGs) on standalone SQL sync b/n two nodes at stage environment so far.

I need to test AAGs by connecting application and what I want from you is that what type of test I have to do step by step to check the HA solution is working properly. Like failover, failback, DDM on primary DBs and check on secondary replica etc.

Do you have any material or resource for this please.

Thank you,


Learn more about SQL Server tools