This is a free SQL Server availability group check tool from Straight Path Solutions for SQL Server Database Administrators (or people who play DBA at their organization) to check their SQL Server high availability. It is used to display current information about an availability group and detect vulnerabilities and potential issues with maintaining the availability and performance of SQL Server databases. You can download the most recent version of sp_CheckAG from the Straight Path Solutions GitHub repository.
Check the Availability Groups on your SQL Server Instances!
Here at Straight Path Solutions, we’re big fans of community tools like sp_WhoIsActive, Brent Ozar’s First Responder’s Kit, and Erik Darling’s suite of helpful stored procedures. As database administrators who are constantly looking at new clients and new servers, we wished there was a tool to quickly give an overview of any current or potential availability group issues. We didn’t find one, so we made one.
What does sp_CheckAG do?
This tool will allow you to review your SQL Server availability groups and their databases quickly and easily, and to also identify potential issues with these availability groups like misconfigured settings, high latency, and more.
This tool has several modes that present different sets of data, depending on what you want to examine.
@Mode = 0: the potential issues we could find related to your availability groups.
@Mode = 1: an overview, including result sets for the instance. the cluster, cluster members, endpoints, availability groups, listeners, replicas, and databases.
@Mode = 2: result set showing recent events in the availability group.
Using each of these Modes, you should be able to quickly identify issues with your availability groups and focus on facts about them to help you resolve any issue.
How do I use it?
Execute the script to create sp_CheckAG in the database of your choice, although we would recommend the master so you can call it from the context of any database.
Executing it without using parameters will return two results sets:
• The results of Mode 1, ordered by name corresponding to the result set
• The results of Mode 0, ordered by Importance
Although you can simply execute it as is, there are currently five parameters.
@Help – the default is 0, but setting this to 1 will return some helpful information about sp_CheckAG and its usage in case you aren’t able to read this web page.
@Mode – see the previous few paragraphs to decide which Mode you want to use.
@AGName – for reducing results to focus on a particular availability group.
@LocalOnly – for reducing results to focus only on the local replicas and databases.
@VersionCheack – to check the version number and version date of this tool.
What do the Importance levels in Mode 0 mean?
1 – High. This is stuff that prevents availability, including offline items.
2 – Medium. This is the stuff that can complicate availability that we recommend you review.
3 – Low. Stuff you may have enabled that you may or may not need.
What are the requirements to use sp_CheckAG?
There are two requirements.
1. You need to have VIEW SERVER STATE permissions. This tool uses several system tables and DMVs to collect information about your SQL Server availability groups, but VIEW SERVER STATE permissions will allow you to read all necessary information.
2. Your SQL Server instance should be using SQL Server 2016 or higher, since those are the only versions with the resources used for collecting the information on availability groups. If you are using an earlier version, execution of the stored procedure will not work.