SQL Server Availability Groups can be a great feature to help support your High Availability needs, but what happens when they fail to work as expected?
Do you have an expiring certificate on used by an endpoint? Do you have timeout settings that could contribute to unexpected failovers? Are you suffering from a high number of HADR_SYNC_COMMIT waits?
We’ve seen all those things happen, and like Marvin Gaye we’ve wondered: what’s going on? And we’ve wanted a tool to help us see if other clients were having these problems, and more.
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: sp_CheckAG.
sp_CheckAG is completely free, it’s easy to use, and you don’t even need to give us your email address. You can download it today from the Straight Path GitHub repository along with our sp_checkSecurity, sp_checkBackup, and sp_checktempdb community tools.
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 the current states and setting 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.