How to set up a LiteSpeed backup job so that it only runs on the secondary replica (not the primary)? The goal is to set up the backup jobs - Maintenance Plan - so that it only runs on the secondary but it running on both the primary and secondary.
WORKAROUND:
We cannot choose just the secondary unless its the "preferred" database.
When you set up the AlwaysOn group, if you chose "prefer secondary" when setting up the group, then using the "perferred only" setting in a Maintenance Plan, it should work.
Secondary only in group setup should work with the LiteSpeed "preferred only" setting.
Scripting of Backup Jobs
The availability group and replica configuration settings have no effect unless you script backup jobs to use these settings. On every availability replica whose backup priority is greater than zero (>0), you need to script backup jobs for the databases in the availability group.
You can determine whether the current replica is the preferred backup replica by calling the sys.fn_hadr_backup_is_preferred_replica function. If the availability replica that is hosted by the current server instance is the preferred replica for backups, this function returns 1. If not, the function returns 0. By running a simple script on each availability replica that that queries this function, to determine which replica should run a given backup job, and if the function returns '1', runs a backup job.
To determine whether the current replica is the preferred backup replica
sys.fn_hadr_backup_is_preferred_replica
The logic for this script is as follows:
If (top-priority replica is local)
Run backup job
Else
Exit with success
If you use the Maintenance Plan Wizard (native or LiteSpeed) to create a given backup job, the job will automatically include the scripting logic that calls and checks the sys.fn_hadr_backup_is_preferred_replica function. However, the backup job will not return the “This is not the preferred replica…” message. Be sure to create the job(s) for each availability database on every server instance that hosts an availability replica for the availability group.
