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.
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.
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.
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.
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:
- 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.
- 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.
- 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.
- availability_replica_manager_state – This event collects data about the heartbeat of your replica manager.
- 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.
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.
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
