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.
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.
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
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/
21 People are following this question.
