Zrjydtj

Around usage results

Maximum summed powersets with non-adjacent items

Has negative voting ever been officially implemented in elections, or seriously proposed, or even studied?

Quick way to create a symlink?

Can a new player join a group only when a new campaign starts?

When a candle burns, why does the top of wick glow if bottom of flame is hottest?

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

How to answer "Have you ever been terminated?"

Fundamental Solution of the Pell Equation

Is safe to use va_start macro with this as parameter?

Amount of permutations on an NxNxN Rubik's Cube

Generate an RGB colour grid

Can a party unilaterally change incumbent candidates in preparation for a General election?

Can non-competes be enforced after expiration?

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

Why are there no cargo aircraft with "flying wing" design?

Find the length x such that the two distances in the triangle are the same

Extracting terms with certain heads in a function

Dating a Former Employee

Is there such thing as an Availability Group failover trigger?

How can I use the Python library networkx from Mathematica?

Can you shove before Attacking with Shield Master using a Readied action?

Using et al. for a last / senior author rather than for a first author

Significance of Cersei's obsession with elephants?

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;

3

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 1 hour ago

Michael J SwartMichael J Swart

1,06051229

• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  

add a comment | 

3

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 1 hour ago

Michael J SwartMichael J Swart

1,06051229

• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  

add a 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 1 hour ago

Michael J SwartMichael J Swart

1,06051229

share|improve this question

asked 1 hour ago

Michael J SwartMichael J Swart

1,06051229

share|improve this question

asked 1 hour ago

Michael J SwartMichael J Swart

1,06051229

asked 1 hour ago

Michael J SwartMichael J Swart

1,06051229

asked 1 hour ago

Michael J SwartMichael J Swart

1,06051229

2 Answers
2

active

oldest

votes

2

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 1 hour ago

answered 1 hour ago

Allan HirtAllan Hirt

442

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 | 

1

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 1 hour ago

Bob PusateriBob Pusateri

46926

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

2

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 1 hour ago

answered 1 hour ago

Allan HirtAllan Hirt

442

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

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 1 hour ago

answered 1 hour ago

Allan HirtAllan Hirt

442

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 1 hour ago

answered 1 hour ago

Allan HirtAllan Hirt

442

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 1 hour ago

answered 1 hour ago

Allan HirtAllan Hirt

442

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 1 hour ago

Allan HirtAllan Hirt

442

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 1 hour ago

Allan HirtAllan Hirt

442

1

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 1 hour ago

Bob PusateriBob Pusateri

46926

add a comment | 

1

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 1 hour ago

Bob PusateriBob Pusateri

46926

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 1 hour ago

Bob PusateriBob Pusateri

46926

share|improve this answer

answered 1 hour ago

Bob PusateriBob Pusateri

46926

answered 1 hour ago

Bob PusateriBob Pusateri

46926

answered 1 hour ago

Bob PusateriBob Pusateri

46926