2

In this scenario there are three MS SQL servers. ServerA has a series of jobs that write data to databases on ServerB. I want to set up ServerC as a mirror of ServerA, with all the same jobs available. I want to keep the SQL Server Agent down on ServerC until such a time as ServerA fails over, at which time I would like the SQL Server Agent on ServerC to automatically start up and continue to service those same jobs writing to ServerB until we get ServerA back online.

Can this be accomplished? And what is the best way to go about it?

Improve this question

2 Answers 2

Reset to default
2

You can keep the Agent enabled but disable the individual jobs on ServerB and create a dedicated job on ServerB that continuously runs (or schedule it to run every X amount of time) and checks if the current server is the Primary. If it is, then enable the other jobs with sp_update_job.

You can also check which jobs are enabled with msdb.dbo.sysjobs so your dedicated job only tries to enable them once.

Using this same methodology you could have the dedicated job disable itself and have a secondary job check for when the current server becomes a Secondary Replica so that it can re-enable the dedicated job, or you can just let the dedicated job continually check but do nothing when the current server is the Primary and it already enabled all the jobs for the first time.

Improve this answer
0

You could leave the agent active in both servers and you could add a first step to them in this way:

  1. check the database status (active or restoring)
  2. if the DB is not active the job stop itself
  3. if the DB is active the job can contine on next steps
Improve this answer
1
  • Both of these solutions seem very good. While MBuschi's solution seems the simpler, my concern here is that several of these jobs are scheduled to run every minute. I'm wondering what kind of cost that would generate. I like J.D,'s solution, but I can see it's going to require a good deal more research on my part. Thanks gentleman.
    – Tom
    Commented May 17, 2021 at 18:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.