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.