Setup SQL Backup with an AlwaysOn Availability Group Cluster

The simplest way to go about this in SQL Backup is to create 2 backup jobs on every one of the nodes:

- a full backup job
EXEC master..sqlbackup '-sql "BACKUP USER DATABASES TO DISK = [<AUTO>] WITH CHECK_PREFERRED_AG_REPLICA, SECONDARY_REPLICA_COPY_ONLY"'

- and a transaction log backup job
EXEC master..sqlbackup '-sql "BACKUP LOGS [*] TO DISK = [<AUTO>] WITH CHECK_PREFERRED_AG_REPLICA, SECONDARY_REPLICA_COPY_ONLY"'

On SQL Server's side, you would need to set up the secondary replica's backup priorities so that only one of them will actually run the backup job.

For full backups, if the primary replica is the preferred backup replica, a full backup will be made.  If a secondary replica is the preferred replica, then a full backup will be made with the COPY_ONLY option added.  For transaction log backups, the backup will only be taken off the preferred replica.  For examples, see http://www.sqlbackuprestore.com/high-availability_group_suppor.htm

Points to note:
- The user can offload the backup tasks to the appropriate secondary replicas to lighten the load on the primary server.

- New databases will be automatically picked up for backups, BUT there may be some errors at the beginning.  The log backup jobs will probably run first and will fail because there are no full backups made yet.  FULLIFREQUIRED would probably not work as the log backups would most likely be taken off a secondary replica and the 'forced' backup doesn't know that it needs to use the COPY_ONLY option. 

- If you are taking differentials off the primary replica and all your full backups run off secondary replicas, you should still take a full backup off the primary occasionally to reset the base differential flag.  Otherwise, your differential backups will grow over time as full backups taken off secondary replicas do not reset the base differential flag.

- If the replicas are on different subnets, then backups on the secondary replicas may fail if the database does not accept connections.  This is because the SQL Backup has to connect to the primary replica to check database level permissions.   Please see https://msdn.microsoft.com/en-us/library/gg471494.aspx.  SQL Backup doesn't support the MultiSubnetFailover option.

- Naming the files; if you want to be able to identify the backup files by cluster and availability group name, you can use the new naming convention tags - <CLUSTER> and <AVAILABILITYGROUP>.  As the names imply, one tag is for the cluster name, and the other is for the availability group name.  These tags actually fall back to <SERVER> and <INSTANCE> values if they are not valid for the current database.

E.g. You have an instance that contains a mix of regular and replica databases. If you named your files with the <CLUSTER> and <AVAILABILITYGROUP> tags and they had no fallback, then the regular databases' backup file names will have empty values for those tags.  Or it might get even messier if you had to use <SERVER>_<INSTANCE>_<CLUSTER>_<AVAILABILITYGROUP> as part of the names.

With the fall back, the following 2 naming conventions will generate the same file names for 'regular' databases:

EXEC master..sqlbackup '-sql "backup database model to disk = [e:\temp\<cluster>_<availabilitygroup>_<database>.sqb] with init"'

EXEC master..sqlbackup '-sql "backup database model to disk = [e:\temp\<server>_<instance>_<database>.sqb] with init"'

For high-availability databases, the cluster and availability group names will be added accordingly.