Sql-server – How to enable email notification for failover in SQL Server

availability-groupssql server

We have a few servers in a High Availability group and need to configure email notification for any failover that happens on the server.

Best Answer

You can set up a SQL Agent alert for event 1480 that filters on the string

"RESOLVING" to "PRIMARY"

The reason for the filter is that 1480 events cover all state transitions. If a secondary loses connection for a bit, it will go into resolving, then back to secondary, so if you don't use a filter you'll get alerts on more than just failover.

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N'HA Error Number 1480 - R2P filter', 
        @message_id=1480, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=60, 
        @include_event_description_in=1, 
        @notification_message=N'Failover Detected!', 
        @event_description_keyword=N'"RESOLVING" to "PRIMARY"', 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
Related Question