Hello, and welcome to the GitHub repository for sp_CheckAG! This is a free 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.
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 potential availability group issues. We didn't find one, so we made one.
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.
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.
@VersionCheck – to check the version number and version date of this tool.
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. This is the stuff you may have enabled that you may or may not need.
There are two requirements.
1. You need to be in the sysadmin role. This tool is designed to be used by administrators only, as they are the only ones who can address many of the vulnerabilities and discrepancies that could be found. If you aren't in the sysadmin role, this isn't the stored procedure you're looking for.
2. Your SQL Server instance needs to be using SQL Server 2016 or higher. If you are using an earlier version, execution of the stored procedure will be aborted because some of the DMVs used don't exist in earlier versions.