alert - How do I get SQL Server 2019 to email me when availibility group role changes?

Question

I hope you can help, I have been following articles and I can't get this to work.

I am tying to get SQL Server 2019 to email an Alert when an AlwaysOnAvailabilityGroup role changes servers (used to be mirrored databases failing over before Availability Groups became available).

Version: Microsoft SQL Server 2019 (KB4583458) - v15.0.2080.9

I have enabled Database Mail XPs by running:

sp_configure 'show advanced options', 1;
GO  
RECONFIGURE;  
GO
   
sp_configure 'Database Mail XPs', 1;  
GO  
RECONFIGURE  
GO

I have configured Database Mail, setting it to be the Default Public Profile

NOTE: it does allows me to right click > Send Test E-Mail... successfully.

I have also set the SQL Server Agent property, Alert System > Enable mail profile, selecting Database Mail and the Mail Profile I just created.

And I created a SQL Server Agent Job using the GUI, with a Script Action that reads:

EXEC msdb.dbo.sp_update_alert @name=N'AlwaysOn - Role Change', 
        @message_id=1480, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=1, 
        @database_name=N'', 
        @notification_message=N'', 
        @event_description_keyword=N'', 
        @performance_condition=N'', 
        @wmi_namespace=N'', 
        @wmi_query=N'', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

I have since restated the SQL Server Agent many times already.

But when I manually Right Click on the Availability Group > Failover..., I do not receive an email. What am I missing please?

Many thanks in advance

question from:https://stackoverflow.com/questions/65939770/how-do-i-get-sql-server-2019-to-email-me-when-availibility-group-role-changes

Answer 1

Waitting for answers