Mdthbs

What does this Jacques Hadamard quote mean?

old style "caution" boxes

Denied boarding although I have proper visa and documentation. To whom should I make a complaint?

How does the math work when buying airline miles?

How to find all the available tools in mac terminal?

Trademark violation for app?

What does the "x" in "x86" represent?

Most bit efficient text communication method?

Is there any way for the UK Prime Minister to make a motion directly dependent on Government confidence?

How to Make a Beautiful Stacked 3D Plot

Why didn't Eitri join the fight?

8 Prisoners wearing hats

Why are both D and D# fitting into my E minor key?

Is this homebrew Lady of Pain warlock patron balanced?

How could we fake a moon landing now?

Fundamental Solution of the Pell Equation

Should I use a zero-interest credit card for a large one-time purchase?

Can an alien society believe that their star system is the universe?

Circuit to "zoom in" on mV fluctuations of a DC signal?

Do wooden building fires get hotter than 600°C?

An adverb for when you're not exaggerating

Is it common practice to audition new musicians one-on-one before rehearsing with the entire band?

What font is "z" in "z-score"?

Is it a good idea to use CNN to classify 1D signal?

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;
}

4

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 9 hours ago

Michael J SwartMichael J Swart

1,06551229

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is this a common thing others would want? Or is it just me?
  
  – Michael J Swart
  9 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  7 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  6 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If you cannot have different settings I don’t understand what the trigger should do?
  
  – eckes
  6 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  4 hours ago
  

  
  
  
  

 | 
show 1 more comment

4

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 9 hours ago

Michael J SwartMichael J Swart

1,06551229

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is this a common thing others would want? Or is it just me?
  
  – Michael J Swart
  9 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  7 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  6 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If you cannot have different settings I don’t understand what the trigger should do?
  
  – eckes
  6 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  4 hours ago
  

  
  
  
  

 | 
show 1 more comment

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 9 hours ago

Michael J SwartMichael J Swart

1,06551229

share|improve this question

asked 9 hours ago

Michael J SwartMichael J Swart

1,06551229

share|improve this question

asked 9 hours ago

Michael J SwartMichael J Swart

1,06551229

asked 9 hours ago

Michael J SwartMichael J Swart

1,06551229

asked 9 hours ago

Michael J SwartMichael J Swart

1,06551229

2 Answers
2

active

oldest

votes

5

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 9 hours ago

answered 9 hours ago

Allan HirtAllan Hirt

642

New contributor

Allan Hirt is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

2

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 9 hours ago

Bob PusateriBob Pusateri

47926

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

5

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 9 hours ago

answered 9 hours ago

Allan HirtAllan Hirt

642

New contributor

Allan Hirt is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

5

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 9 hours ago

answered 9 hours ago

Allan HirtAllan Hirt

642

New contributor

Allan Hirt is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

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 9 hours ago

answered 9 hours ago

Allan HirtAllan Hirt

642

New contributor

Allan Hirt is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this answer

edited 9 hours ago

answered 9 hours ago

Allan HirtAllan Hirt

642

New contributor

Allan Hirt is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 9 hours ago

Allan HirtAllan Hirt

642

New contributor

Allan Hirt is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 9 hours ago

Allan HirtAllan Hirt

642

2

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 9 hours ago

Bob PusateriBob Pusateri

47926

add a comment | 

2

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 9 hours ago

Bob PusateriBob Pusateri

47926

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 9 hours ago

Bob PusateriBob Pusateri

47926

share|improve this answer

answered 9 hours ago

Bob PusateriBob Pusateri

47926

answered 9 hours ago

Bob PusateriBob Pusateri

47926

answered 9 hours ago

Bob PusateriBob Pusateri

47926