2

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

0

1 Answer 1

6

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.