6

Is there such thing as a trigger for Availability Group failovers?

I want a certain action to happen when an AG fails over. Specifically, I want to turn on a database setting (turning on RCSI). I want to do this on a failover in order to minimize disruption to 24/7 workloads and scheduled maintenance windows are hard to come by.

I know that sp_procoption can be used to mark procedures as startup procedures. This seems like it could work for failover clusters, but not for Availability Groups.

I did consider adding an alert (sp_add_alert) on message_id=26069 in order to respond to failover actions with a sql agent job. But this seems less direct and in practice it seems slow

  • Is this a common thing others would want? Or is it just me? – Michael J Swart Apr 17 at 19:16
  • Hmm, what’s the advantage of not having RCSI enabled on the secondary by default? Is it because of the additional versioning information? (If so I Wonder if this is actually a bad thing to not have slots for versioning in your existing rows)? – eckes Apr 17 at 21:18
  • You can’t have different RCSI settings on primary and secondary. Any queries that read on the secondary node run under Snapshot isolation any way. – Michael J Swart Apr 17 at 22:53
  • If you cannot have different settings I don’t understand what the trigger should do? – eckes Apr 17 at 22:55
  • I wanted to change the setting during a failover. The timing was the important part. The change in the RCSI setting takes a small outage, so does the failover. I wanted one to happen at the same time as the other. – Michael J Swart Apr 18 at 0:18
6

No. Triggers are not at an instance or AG level. You'd need to base something on the failover event or better yet, as part of a SQL Server Agent job, just check to see if that replica is the primary and then do (or not do) something.

4

I've done things like this before, but all I found available was the alerts. There's actually 2 different alerts, one that fires for each database in the AG, and another that fires when the entire AG fails over. So you have some flexibility.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.