Automated script run on Availability Group failover?

• Mick Opalak

  SSCommitted

  Points: 1631

  More actions

  May 29, 2015 at 12:34 pm

  #304547

  Let's say I have a two node AG, Server A and Server B. Server A is normally the primary and Server B is the replica. Whenever the primary fails over from A to B or from B to A, I'd like to automatically run a script that will restart the SQL Agent service on the new replica. Any ideas on how to do this?

• AlexSQLForums

  SSChampion

  Points: 14277

  More actions

  May 29, 2015 at 2:28 pm

  #1801735

  you can use:

  exec xp_servicecontrol 'stop','SQLServerAgent'

  GO

  exec xp_servicecontrol 'start','SQLServerAgent'

  GO

  Alex S

• Mick Opalak

  SSCommitted

  Points: 1631

  More actions

  May 29, 2015 at 2:51 pm

  #1801749

  Yes, that will take care of the service, but how do I run that automatically on failover?

• AlexSQLForums

  SSChampion

  Points: 14277

  More actions

  June 1, 2015 at 9:19 am

  #1802032

  There are other ways like Powershell or SQLCMD to accomplish this but this is the linked server way.

  You have to create Linked Servers and add both servers participating in AO AG with RPC and RPC out.

  Make sure user has rights to restart SQL Agent.

  Create a Job 'Restart SQL Agent' and paste this code

  exec RemoteServer.master..xp_servicecontrol 'stop','SQLServerAgent'

  GO

  WAITFOR DELAY '00:00:05' --Pause between commands.

  exec RemoteServer.master..xp_servicecontrol 'start','SQLServerAgent'

  GO

  Create below Alert

  EXEC msdb.dbo.sp_add_alert

  @name = N'AlwaysOn AG Role Change',

  @message_id = 1480,

  @severity = 0,

  @enabled = 1,

  @delay_between_responses = 0,

  @include_event_description_in = 1;

  GO

  EXEC msdb.dbo.sp_add_notification

  @alert_name = N'AlwaysOn AG Role Change',

  @operator_name = N'SQLDBA',

  @notification_method = 1;

  GO

  Add the job 'Restart SQL Agent' in Response section.

  Don't forget to setup Database Mail and operator in order to get emails during failover.

  Alex S

• David Reabow

  Newbie

  Points: 1

  More actions

  August 27, 2021 at 7:56 pm

  #3924586

  I know this is an old thread but as AG's aren't going away and this method was not suggested:

  I'm making the assumption that you only want jobs to run on the primary.

  Don't disable the Agent, just put a first step in your jobs that checks if the sever is the Primary node.

  If it's not primary you can either raise and error or run "Select 1/0" to force an error.

  You can either just let the job fail or on failure quit the job successfully.