Arsthbt

Amount of permutations on an NxNxN Rubik's Cube

Generate an RGB colour grid

Why wasn't DOSKEY integrated with command.com?

Is there such thing as an Availability Group failover trigger?

Where are Serre’s lectures at Collège de France to be found?

Is there a holomorphic function on open unit disc with this property?

Is it ethical to give a final exam after the professor has quit before teaching the remaining chapters of the course?

First console to have temporary backward compatibility

Ports Showing Closed/Filtered in Nmap Scans

On SQL Server, is it possible to restrict certain users from using certain functions, operators or statements?

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

8 Prisoners wearing hats

If my PI received research grants from a company to be able to pay my postdoc salary, did I have a potential conflict interest too?

Closed form of recurrent arithmetic series summation

Would "destroying" Wurmcoil Engine prevent its tokens from being created?

What are the out-of-universe reasons for the references to Toby Maguire-era Spider-Man in ITSV

How to tell that you are a giant?

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

Why do we bend a book to keep it straight?

How to down pick a chord with skipped strings?

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

What's the meaning of "fortified infraction restraint"?

Do wooden building fires get hotter than 600°C?

Do I really need recursive chmod to restrict access to a folder?

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