Otdfbt

As an international instructor, should I openly talk about my accent?

Is it acceptable to use working hours to read general interest books?

Can I criticise the more senior developers around me for not writing clean code?

Is a 5 watt UHF/VHF handheld considered QRP?

My bank got bought out, am I now going to have to start filing tax returns in a different state?

"Rubric" as meaning "signature" or "personal mark" -- is this accepted usage?

Mistake in years of experience in resume?

With indentation set to `0em`, when using a line break, there is still an indentation of a size of a space

"My boss was furious with me and I have been fired" vs. "My boss was furious with me and I was fired"

Co-worker works way more than he should

Additive group of local rings

Split coins into combinations of different denominations

I preordered a game on my Xbox while on the home screen of my friend's account. Which of us owns the game?

Has a Nobel Peace laureate ever been accused of war crimes?

Error: Syntax error. Missing ')' for CASE Statement

Flash for group photos near wall

What is /etc/mtab in Linux?

std::is_constructible on incomplete types

Check if a string is entirely made of the same substring

Are these square matrices always diagonalisable?

Why did C use the -> operator instead of reusing the . operator?

What is the term for a person whose job is to place products on shelves in stores?

Could moose/elk survive in the Amazon forest?

How to open locks without disable device?

Is there such thing as an Availability Group failover trigger?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

6

1

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

sql-server

share|improve this question

asked Apr 17 at 19:16

Michael J SwartMichael J Swart

1,07551229

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

 | 
show 2 more comments

6

1

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

sql-server

share|improve this question

asked Apr 17 at 19:16

Michael J SwartMichael J Swart

1,07551229

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

 | 
show 2 more comments

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

sql-server

share|improve this question

asked Apr 17 at 19:16

Michael J SwartMichael J Swart

1,07551229

share|improve this question

asked Apr 17 at 19:16

Michael J SwartMichael J Swart

1,07551229

share|improve this question

asked Apr 17 at 19:16

Michael J SwartMichael J Swart

1,07551229

asked Apr 17 at 19:16

Michael J SwartMichael J Swart

1,07551229

asked Apr 17 at 19:16

Michael J SwartMichael J Swart

1,07551229

2 Answers
2

active

oldest

votes

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.

share|improve this answer

edited Apr 17 at 19:31

answered Apr 17 at 19:20

Allan HirtAllan Hirt

742

add a comment | 

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.

share|improve this answer

answered Apr 17 at 19:20

Bob PusateriBob Pusateri

49926

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f235085%2fis-there-such-thing-as-an-availability-group-failover-trigger%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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.

share|improve this answer

edited Apr 17 at 19:31

answered Apr 17 at 19:20

Allan HirtAllan Hirt

742

add a comment | 

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.

share|improve this answer

edited Apr 17 at 19:31

answered Apr 17 at 19:20

Allan HirtAllan Hirt

742

add a comment | 

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.

share|improve this answer

edited Apr 17 at 19:31

answered Apr 17 at 19:20

Allan HirtAllan Hirt

742

share|improve this answer

edited Apr 17 at 19:31

answered Apr 17 at 19:20

Allan HirtAllan Hirt

742

answered Apr 17 at 19:20

Allan HirtAllan Hirt

742

answered Apr 17 at 19:20

Allan HirtAllan Hirt

742

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.

share|improve this answer

answered Apr 17 at 19:20

Bob PusateriBob Pusateri

49926

add a comment | 

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.

share|improve this answer

answered Apr 17 at 19:20

Bob PusateriBob Pusateri

49926

add a comment | 

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.

share|improve this answer

answered Apr 17 at 19:20

Bob PusateriBob Pusateri

49926

share|improve this answer

answered Apr 17 at 19:20

Bob PusateriBob Pusateri

49926

answered Apr 17 at 19:20

Bob PusateriBob Pusateri

49926

answered Apr 17 at 19:20

Bob PusateriBob Pusateri

49926