Sql-server – How do Availability Groups work with cheduled jobs and SSIS packages

availability-groupssql serversql-server-2012sql-server-agent

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?

Best Answer

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.