Whilst a previous post mentioned storing when the primary server had changed, this didn't help with us knowing it had changed so we had to look at SQL server Agent notifications
USE [msdb] GO EXEC msdb.dbo.sp_update_alert @name = N'AlwaysOn - Role Change' ,@message_id = 1048 ,@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 EXEC msdb.dbo.sp_update_notification @alert_name = N'AlwaysOn - Role Change' ,@operator_name = N'Andrew' ,@notification_method = 1 GO
This (assuming you have your notification service set up with an Operator called Andrew and SQL agent running) will email you whenever there is a change of AG role on the server, such as Primary failing to secondary or secondary becoming primary. Realistically your more likely to get the secondary to primary as if its an unexpected failover your old primary might not be there anymore.
No comments:
Post a Comment