question

DeepakRao avatar image
DeepakRao asked

How SQL Jobs will work in always ON configuration?

As per my understanding "Always ON" configuration internally have two nodes and active node will always serve the request. But suppose I have SQL Jobs in these nodes then how can I enable/disable these jobs based on active node? Kindly provide the best practises.
sql-agentalways-onalwayson
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
I would say that the best thing to do isn't to enable/disable the jobs depending on which node is active, but to have the jobs enabled on all nodes, and re-architect the job to look something like this: * Step 1: Check to see if this is on the active node. If not, then quit reporting success * Step 2: Do the job. This has the benefit of *all* jobs being enabled at all times, meaning that you won't get alerts from SCOM or Redgate SQL Monitor or anything else. Re: Step 1. I'm pretty sure I've got some code for this somewhere, but I've just changed jobs, so can't lay my hands on it immediately. Sorry about that. But it will make use of the DMVs for [sys.dm_hadr_availability_replica_states][1] and [sys.dm_hadr_database_replica_states][2]. See [this Google query][3] for more ideas. [1]: https://msdn.microsoft.com/en-us/library/ff878537.aspx [2]: https://msdn.microsoft.com/en-us/library/ff877972.aspx [3]: https://www.google.co.uk/search?q=check+to+see+if+database+availability+group+primary
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
See this link, which makes your jobs "intelligent": [link text][1] [1]: https://blog.sqlrx.com/2015/10/08/alwayson-availability-groups-enable-or-disable-jobs-on-failover/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.