John McCormack DBA

SQL Server Databases and Cloud

Alert if your availability group fails over

By Leave a Comment

A simple way to send an alert if your Always On Availability Group fails over. You can run the t-sql below or set it up visually in SQL Agent.

USE [msdb]

/*
Sends an alert if the AG group fails over.
I've limited the requests to one alert per group (by specifying the DB name in @event_description_keyword
Let me know what you think of this approach, I found it better than multiple alerts for each DB in the Availability Group
*/

EXEC msdb.dbo.sp_add_alert @name=N'AG role change - AG1',
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@database_name=N'',
@event_description_keyword=N'PRD_DB1',
@notification_message=N'There has been a failover of your Always On Availability Group AG1 - Please investigate.',
@job_id=N'00000000-0000-0000-0000-000000000000'

-- Add an operator if it doesn't already exist
EXEC msdb.dbo.sp_add_operator @name=N'John McCormack',
@enabled=1,
@pager_days=0,
@email_address=N'john.mccormack@example.com'

-- Add a notification
EXEC msdb.dbo.sp_add_notification @alert_name=N'AG role change - AG1', @operator_name=N'John McCormack', @notification_method = 1

 

Test read intent connections to an AG Listener

By 4 Comments

To test read intent connections to an AG Listener, I prefer to use SQLCMD but you can also test easily using SSMS.

SQLCMD

The -Kreadonly switch is your key to success here but remember to also specify the database using -d. When not set (and with an initial catalog of master for my login), I found I always got the primary instance back during my check. This simple omission cost me hours of troubleshooting work, because I was convinced my listener wasn’t working correctly. In fact, I just wasn’t testing it correctly.

Read/Write Connections
-sqlcmd -S "SQL01-AG1-list" -d WideWorldImporters -E -q "SELECT @@SERVERNAME;"

Read only Connections
-sqlcmd -S "SQL01-AG1-list" -d WideWorldImporters-E -q "SELECT @@SERVERNAME;" -Kreadonly

The instance that you are connected to will show in the command prompt. Type exit to leave sqlcmd.

SSMS

In object explorer, click Connect and choose Database Engine.

Then, in the bottom right hand side of the dialog box, click on Options >>

In Connection Properties, Click Connect to database and then <Browse server ..>. Choose a DB that is in your availability group.

Connect To Server SSMS

Then click on Additional Connection Parameters and type in ApplicationIntent=ReadOnly

ApplicationIntent=ReadOnly SSMS

Click connect and run SELECT @@SERVERNAMEand you should expect to see the instance name of your secondary replica, providing you have set up the read only routing correctly.

If you change connection and remove ApplicationIntent=ReadOnly from the Additional Connection Parameters, you should see the result as the name of your primary instance.

Summary

Hopefully these 2 simple techniques to test read intent connections to an AG Listener will be useful and help save you time. It’s a simple blog post but I wanted to write it because I was looking at the problem in too much depth and missing the obvious mistake of choosing my database context.

Popular posts