Scheduling backup jobs for an Availability Group In a MS SQL Server 2014 AlwaysOn AG setup, I would like to schedule backup jobs for a given availability group. The ultimate goal is to have the regular backups run on the synchronized secondary and, most importantly, not bound to the availability of a particular secondary node.

The approach I have seen so far was to use the SQL Server scheduler, set up identical jobs on all running instances and introduce conditional logic into the scheduler steps in order to determine if the role is primary or secondary. This won't work for my use case for several reasons:


I want to run the scheduled action on a secondary just once, but I have multiple secondaries
I want to ensure that it is ultimately run, regardless if there are any secondaries left at all - if no secondaries are left, it should run on the primary


The backup job consists of running BACKUP LOG [...] WITH COMPRESSION, NOINIT, NOFORMAT every 15 minutes.

Right now, I am thinking about creating a clustered scheduled task bound to the respective AG's failover cluster role but wondered if there is a more easy and streamlined way to implement this.
In a MS SQL Server 2014 AlwaysOn AG setup, I would like to schedule
  backup jobs for a given availability group. The ultimate goal is to
  have the regular backups run on the synchronized secondary and, most
  importantly, not bound to the availability of a particular secondary
  node.


This can be accomplished without issue. Just keep in mind that your systems should be spec'd to be able to take the full load of the synchronization mechanics for AlwaysOn Availability Groups, plus the redo needed, plus the backups. The last thing that you want is to overwhelm your IO system and cause outages or a large redo queue. This is assuming it's not actively being used as a readable secondary - if it is, you'll want to factor that in and watch for blocked redo threads.


  1.I want to run the scheduled action on a secondary just once, but I have multiple secondaries
  2.I want to ensure that it is ultimately run, regardless if there are any secondaries left at all - if no secondaries are left, it should run on the primary


There is an Availability Group setting and a system function that can be used in order to accomplish this within your expected guidelines.

The first is the setting, per AG, called AUTOMATED_BACKUP_PREFERENCE which has four different options. The one you're describing is called SECONDARY which will prefer any secondary node over the primary node. The secondary node chosen is by the BACKUP_PRIORITY which is set per replica ranging from 0-100, where the higher the number the more weight it has. If there is a tie in weights for secondary replicas, the replica that is sorted first given the system collation will be chosen. If all secondary replicas should fail, the primary replica will be chosen.

The second part of the equation is the system function used to check if the replica that is running the job is the preferred replica based on the values in the previous paragraph. This system function is called sys.fn_hadr_backup_is_preferred_replica(). Given a database name (any database in the AG) a value of 0 will be returned if it is not the preferred replica and a value of 1 if it is preferred.

When creating the agent job to accomplish this, you'll want to wrap the backup logic in an IF conditional to check for the preferred replica. That's it. Put the identical agent job on all replicas.

Please be sure to test that this truly is what you want and expect.