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.
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] and [sys.dm_hadr_database_replica_states]. See [this Google query] for more ideas. :