Mdtryhht

Fantasy story; one type of magic grows in power with use, but the more powerful they are, they more they are drawn to travel to their source

How do I find out the mythology and history of my Fortress?

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

Is it cost-effective to upgrade an old-ish Giant Escape R3 commuter bike with entry-level branded parts (wheels, drivetrain)?

Why aren't air breathing engines used as small first stages

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

Can anything be seen from the center of the Boötes void? How dark would it be?

Significance of Cersei's obsession with elephants?

Is CEO the profession with the most psychopaths?

Do square wave exist?

Is there a kind of relay only consumes power when switching?

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

How to tell that you are a giant?

Uniqueness of spanning tree on a grid.

Amount of permutations on an NxNxN Rubik's Cube

How to Make a Beautiful Stacked 3D Plot

What does this Jacques Hadamard quote mean?

Wu formula for manifolds with boundary

Why are the trig functions versine, haversine, exsecant, etc, rarely used in modern mathematics?

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

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

How can I use the Python library networkx from Mathematica?

Trademark violation for app?

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

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

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If you cannot have different settings I don’t understand what the trigger should do?
  
  – eckes
  3 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
  1 hour 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 7 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
  6 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
  4 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
  3 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If you cannot have different settings I don’t understand what the trigger should do?
  
  – eckes
  3 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
  1 hour 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 7 hours ago

Michael J SwartMichael J Swart

1,06551229

share|improve this question

asked 7 hours ago

Michael J SwartMichael J Swart

1,06551229

share|improve this question

asked 7 hours ago

Michael J SwartMichael J Swart

1,06551229

asked 7 hours ago

Michael J SwartMichael J Swart

1,06551229

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

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

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

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

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

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

Bob PusateriBob Pusateri

47926

share|improve this answer

answered 6 hours ago

Bob PusateriBob Pusateri

47926

answered 6 hours ago

Bob PusateriBob Pusateri

47926

answered 6 hours ago

Bob PusateriBob Pusateri

47926