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

 

SQL Server Availability Group Monitoring with SQL Monitor


By:   |   Last Updated: 2016-02-09   |   Comments (1)   |   Related Tips: More > Monitoring

Problem

SQL Server is mission critical for our organization. We have adopted SQL Server Availability Groups to meet our high availability and disaster recovery needs to support the business requirements. With all of the day to day systems I need to support along with new projects I really want to work on, how do I ensure the investment our company has made in Availability Groups will be there if we have a disruption in our SQL Server infrastructure?

Solution

In many respects, Microsoft introduced Availability Groups to offer SQL Server professionals a whole new set of native abilities beyond what was originally available with log shipping, clustering, replication and database mirroring. But like many things you cannot configure and forget about Availability Groups. You need to have a checks and balances system with insight into the environment. However, we cannot have a SQL Server Availability Group failure yield an expensive high availability and disaster recovery solution as unusable. We need to have insight into the health of the overall system for both primary and secondary databases which compromise a SQL Server Availability Group and be able to address issues such as:

  • A listener offline
  • Downed SQL Server clusters
  • Transactional delays
  • Data replication latency
  • Data replication out of sync
  • High signal time for Availability Group wait statistics
  • High Redo Rate

Our organization has made a significant investment in the technology and cannot be in a situation where the high availability and disaster recovery functionality expected to support the business is in fact failing. This is the absolute worst case scenario in a disaster recovery or high availability situation. Microsoft has included some monitoring options in SQL Server Management Studio, but there are some short comings. You can also query the system objects for basic status of the Availability Groups, but this approach is not completely intuitive nor a time saver.

SQL Server DBAs are busy and need an Availability Group monitoring solution that is:

  • Simple yet comprehensive monitoring for junior and experienced professionals
  • Plug and play a monitoring solution requiring minimal implementation effort
  • Real time alerting to address issues before they become critical
  • Historical data collection to analyze and report on the environment
  • Time savings to balance numerous projects

To meet these needs, Redgate has introduced Availability Group monitoring with the 5.0 release of SQL Monitor. SQL Monitor has helped companies globally address SQL Server performance monitoring needs and with the latest release addresses the following:

  • Global overview
  • Summarized status for primary and secondary servers and their databases
  • Consistent data collection
  • Analysis by server, database, time period, counter and more
  • Out of the box meaningful Availability Group alerting
  • Historical analysis
  • Guidance

Global Overview of Your SQL Server Environment

For a global overview of your SQL Server environment, Redgate's SQL Monitor includes a single customizable interface to review the server status, alerts and high level metrics. This high level view of the environment provides a means to prioritize your time as well as enables you to begin troubleshooting an issue raised via an alert or user. This interface is also the launching pad to drill into servers, Availability Groups and/or clusters.

Redgate SQL Monitor Global Overview

Figure 1 - SQL Monitor 5.0 Global Overview

SQL Server Availability Group Replicas Monitoring

From the Global Overview interface you use the Availability Group Monitoring interface to see the entire SQL Server Availability Group infrastructure including the databases. This interface provides insight into:

  • Primary and Secondary Replica status
  • Alerts
  • Listener status
  • Log Growth
  • Transaction Delay
  • Flow Control Time
  • Replication status
  • Queue status

This interface provides all pertinent details for the Availability Group in one interface to streamline troubleshooting to determine if alerts are on a specific database, in a particular data center, if networking delays are causing log growth or transactional delays and more.

Redgate SQL Monitor Primary Replica Status

Figure 2 - SQL Monitor 5.0 Availability Group Status

SQL Server Wait Statistics Monitoring

Beyond the metrics directly for the Availability Group as shown above, wait statistics are also valuable metrics to determine performance issues that can be impacting your infrastructure. SQL Monitor offers a review of the Top 10 wait stats and drill down to affected queries in a sortable list. This snippet of data enables you to quickly determine if a wait stat or group of wait stats are an issue.

Redgate SQL Monitor Top 10 Wait Statistics

Figure 3 - SQL Monitor 5.0 Top 10 Wait Stats

SQL Server Availability Group Alerts

As busy SQL Server DBAs we do not have the luxury of personally monitoring metrics all day long. We need to be alerted when an issue occurs, jump on it and protect the business from an unexpected issue. With the SQL Monitor 5.0 release there are 7 new alerts specifically for Availability Groups (as shown below) and 33 SQL Server alerts that have been hand selected as the most needed to streamline monitoring. These email based alerts can be sent to a central team mail box for review and address as issues occur. You also have the option to review alerts based on the Availability Group, cluster or database as previously shown. There is also the option to build customized alerts for known issues in your environment or for business specific needs.

With hundreds of counters and metrics available from Performance Monitor, Extended Events, Dynamic Management objects, system database objects and more, Redgate developers took the time to prioritize the SQL Server Alerts to save you time while also providing an option to build customized alerts. This enables you as a DBA to plug and play SQL Monitor in your environment and be confident that you have critical alerting to support your organization.

Redgate SQL Monitor Availability Group Alerts

Figure 4 - SQL Server Availability Group Alerts in SQL Monitor 5.0

SQL Monitor Analysis Graph

Analysis is a critical component for SQL Monitor. The Analysis Graph in SQL Monitor includes the predefined counters that are deemed as the most important by the Redgate team to help you focus on issues. This interface enables you to review the historical data to see when an issue occurred for a single or multiple counters, review data for a predefined time frame and export the findings as a CSV file for reporting and information sharing purposes with your team. Specifically for Availability Groups you can also review the counters for specific clusters and databases to help fine tune your analysis.

SQL Monitor 5.0 Analysis Graph

Figure 5 - SQL Monitor 5.0 Analysis Graph

Beyond the metrics, graphing and time frame options on this interface, another key component on the bottom right of the Analysis Graph is the Description section. The team at Redgate took the time to include detailed descriptions of each metric to better understand the data and more efficiently troubleshoot the issue. Here is one example for Redo Rate that has the definition, explanation, guidelines and related counters.

SQL Monitor 5.0 Analysis Graph Counter Descriptions

Figure 6 - SQL Monitor 5.0 Analysis Graph Counter Descriptions

Seeing a single metric spike or drop is interesting, but just because a metric spikes does not mean this is out of the norm or an issue. The Redgate Team understands that what is critical is understanding the context of the data. To address this they have incorporated overlaying a baseline in the Analysis Graph with multiple standard deviations of data outlined in shades of blue. This visual representation of the baseline easily identifies if a particular counter is in fact outside the norm or if the counter value is typical for the workload. This functionality in the Analysis Graph is very intuitive to quickly determine if the workload is outside the norm and if action should be taken.

SQL Monitor 5.0 Analysis Graph with an Overlaid Baseline

Figure 7 - SQL Monitor 5.0 Analysis Graph with an Overlaid Baseline

SQL Monitor 5.0 for Availability Groups

To summarize, SQL Monitor 5.0 provides the following value to your SQL Server Availability Groups:

  • Prioritized monitoring data to focus on the most critical issues
  • Detailed status of your Availability Group infrastructure
  • Key metrics for primary and secondary replicas and their databases
  • Proactive alerting to address issues in real time
  • Counter based analysis with intuitive baselines
  • Detailed explanations of metrics to take action

How else can SQL Monitor 5.0 help me manage SQL Server?

Beyond SQL Monitor's first of its kind SQL Server Availability Group monitoring, SQL Monitor is first and foremost a SQL Server monitor product delivering:

  • Holistic SQL Server monitoring
  • 24 x 7 data collection and retention
  • Low overhead data collection
  • Detailed baselines with standard deviations
  • Supports multiple baselines
  • Drill down from high level metrics down to the line of code
  • Reporting and data CSV exports
  • More than 40 prioritized alerts
  • Customized alerting
  • Historical data collection and retention
  • Storage monitoring and metrics
  • Top system processes and associated performance metrics

>>> Learn More

How do I start monitoring my SQL Server Availability Groups?

  1. Go check out all of the monitoring resources available for SQL Server and Availability Groups from Redgate.
  2. Check out the online demo to see SQL Monitor in action for yourself.
  3. If the online demo is interesting for you download a fully functional version of SQL Monitor 5.0 for 14 days to see how it can help in your environment.
  4. Contact the Redgate Team for assistance - their full knowledge of SQL Monitor will save you a lot of time during your evaluation and make sure you cover everything there is to see!
  5. Think about all of the SQL Server Availability Group and performance monitoring challenges you face and how SQL Monitor can save you time.
  6. After your 14 day trial, gather all of your findings then communicate with your team and management about how you think SQL Monitor save your team time on a daily basis.
  7. Determine the next steps for your team.
  8. Think about all that you can accomplish with your new found free time.

Next Steps

MSSQLTips.com Product Editorial sponsored by Redgate, makers of SQL Monitor.



Last Updated: 2016-02-09


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

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.



    



Wednesday, May 24, 2017 - 8:18:52 AM - Brahma reddy Back To Top

 Excellent 

 

*** NOTE *** - If you want tbo include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.


Learn more about SQL Server tools