SQL Server 2012 - AlwaysOn - Notifications

As we've grown and developed our database system further since we first (hurriedly) introduced AlwaysOn. I have realised that some of my suggestions in earlier posts are not the best way of identifying changes.

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.