The key to configuring the alert code to notify on every AlwaysOn Availability Group Failover is to filter the “EE-AlwaysOn-AvaliabilityGroup”
custom extended event session below for error_number = 1480.
This post assumed the Extended Events Session here is created and running.
[AlwaysON Availability Groups]
T-SQL:
Declare @ExtendedEventsSessionName varchar(100) = 'EE-AlwaysOn-AvaliabilityGroup'
Declare @DateTimeOffset int = -5 --Check last 5 minutes / SQL Job runs every 5 mins
Declare @ErrorNumber int = 1480
--The %S_MSG database "%.*ls" is changing roles from "%ls" to "%ls" because the mirroring session or availability group failed over due to %S_MSG.
--SELECT * FROM sys.messages m where m.message_id =1480 AND language_id = 1033--ENG
If Exists (Select Name FROM sys.dm_xe_sessions where name = 'EE-AlwaysOn-AvaliabilityGroup')
Begin
DECLARE @target_data XML;
SELECT @target_data = CAST(st.target_data AS XML)
FROM sys.dm_xe_sessions AS se
INNER JOIN sys.dm_xe_session_targets AS st ON se.address like st.event_session_address
where se.name = @ExtendedEventsSessionName
If Exists
(
SELECT TOP 1
n.value('(@timestamp)[1]', 'datetime2') as 'datetime',
n.value('(data[@name="error_number"]/value)[1]', 'varchar(max)') as [error_number],
REPLACE(n.value('(data[@name="message"]/value)[1]', 'varchar(max)'), 'This is an informational message only. No user action is required.', '') as [error_message]
FROM @target_data.nodes('RingBufferTarget/event') AS q(n)
WHERE n.value('(data[@name="error_number"]/value)[1]', 'varchar(max)') = @ErrorNumber
and DATEADD(HH, -5, n.value('(@timestamp)[1]', 'datetime2')) > DATEADD(mi, @DateTimeOffset, GetDate())
ORDER BY n.value('(@timestamp)[1]', 'datetime2') asc
)
Begin
RAISERROR('Alert - AlwaysON Availability Group Fail-Over Occurred! (Error 1480)', 16, 1)
End
End
Output:
Msg 50000, Level 16, State 1, Line 29
Alert - AlwaysON Availability Group Fail-Over Occurred! (Error 1480)
Note: This Extended Events session will only track the state changes for the Local Server Replica.
Messages:
SELECT *
FROM sys.messages m where language_id = 1033 -- ENG
AND ([message_id]=(9691) OR [message_id]=(35204) OR [message_id]=(9693) OR [message_id]=(26024) OR [message_id]=(28047)
OR [message_id]=(26023) OR [message_id]=(9692) OR [message_id]=(28034) OR [message_id]=(28036) OR [message_id]=(28048)
OR [message_id]=(28080) OR [message_id]=(28091) OR [message_id]=(26022) OR [message_id]=(9642) OR [message_id]=(35201)
OR [message_id]=(35202) OR [message_id]=(35206) OR [message_id]=(35207) OR [message_id]=(26069) OR [message_id]=(26070)
OR [message_id]>(41047) AND [message_id]<(41056) OR [message_id]=(41142) OR [message_id]=(41144) OR [message_id]=(1480)
OR [message_id]=(823) OR [message_id]=(824) OR [message_id]=(829) OR [message_id]=(35264) OR [message_id]=(35265))
ORDER BY Message_id
|
|
|
|
|
|