November 18, 2015 at 10:17 am
Hello All,
We would like to be notified whenever the primary nodes fails over to the secondary node.
I did setup a SQLServer Alert whenever this happens using the message_ids 1480,35264,35265 but the issue is that multiple emails(Alerts) get generated for every database that is a part of the Availability databases.
select * from sys.messages where message_id IN (1480,35264,35265) and language_id = 1033
This Alerts/Emails happen for the 30 databases which are a part of the AAG for each of the 30 it the Alerts(Emails) go out 3 times.
changing roles from "PRIMARY" to "RESOLVING
changing roles from "SECONDARY" to "RESOLVING"
changing roles from "RESOLVING" to "PRIMARY"
I just need one alert that goes out when the Secondary Instance (Secondary Node) takes over as the Primary (Failover) and an other Alert that goes out when the Failback happens When the Primary Node now takes over the as the Primary Instance.
Any ideas as to how this can be implemented. I'm also open to look at Extended Events, Event Notifications ... etc.
Based on the alerts we have to enable TRUSTWORTHY on a few databases. I'm hoping to get this done via a SQLJob that would get triggered by the Alert.
Thanks,
rgn
September 28, 2017 at 4:54 am
did you get any solution for this by any chance? 🙂
September 28, 2017 at 6:34 am
Create an alert for Error 19406 which is the replica state change, so you get the alert for primary_normal, primary_resolving, secondary_normal etc etc
September 28, 2017 at 7:05 am
Thank you .. it works !!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply