I have 2 SQL Servers set up with a database in an AlwaysOn Availability Group.

I also have jobs set up which run against the database. During a failover how do I ensure the jobs will continue to run on the secondary server? Do I need to install the jobs and SSIS packages on both machines and manually disable them on the secondary machine... then manually enable them in the case of a failover? Or is there built in functionality to handle this?


Yes, you will need to create the jobs on any other replicas that you would want those specific jobs to run if they were the primary replica.

You will need to create your own logic for if/when each SQL Server Agent job will run. For instance, do you want to run a job only if the current instance is the primary replica of a particular Availability Group? You will need to put that into your job. It can't be blanketed automatically, because that would take away the flexibility of AlwaysOn AG. Whether you want them disabled on the secondary replica(s) is completely up to you, what those jobs do, and how/when/if you want them to run.

Remember, the secondary replica server isn't just a stand-by server waiting for failover. It could be a fully functional, accessible server. Because of this, having every job sitting by idle would be a huge disability.

So, yes, you will need to push our your jobs to other replicas and use some logic as to if the job should continue execution when it kicks off.

For instance, backup jobs can take advantage of the sys.fn_hadr_backup_is_preferred_replica function by determining whether the current replica is the preferred one for a particular database. This will derive how you have your Availability Group setup for backup preferences.

| improve this answer | |
  • That's sounds promising. I only want the jobs to run on the primary server (which ever server that may be at the time). – John Dec 12 '12 at 16:11

I encountered a bit of similar frustration w/ the documentation on Books Online saying 'when scheduling your job, add logic to only execute on the primary' but not saying how to do so. The sys.fn_hadr_backup_is_preferred_replica is super-nice for backup jobs, but there doesn't seem to be a similar function for 'is primary' or what have you. Fortunately you can grab that info from something like this:

  sys.dm_hadr_availability_replica_states a
inner join 
  sys.dm_hadr_database_replica_states d
  a.replica_id = d.replica_id
  a.group_id = d.group_id
  database_id = DB_ID()
  d.is_local = 1

That would just let you know the role the current database is in.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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