How do Availability Groups work with cheduled jobs and SSIS packages?

Question

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?

Answer 1

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.

Answer 2

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:

select 
  DB_NAME(database_id),role_desc
from 
  sys.dm_hadr_availability_replica_states a
inner join 
  sys.dm_hadr_database_replica_states d
on 
  a.replica_id = d.replica_id
  and
  a.group_id = d.group_id
where 
  database_id = DB_ID()
and 
  d.is_local = 1

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