SQL Server Availability group and Backups
SQL Server availability group backup preferences.
We have 3 nodes for our AG.
I would like to take full and differential backups on primary, log backups on secondary.
When I configured "any replica" made backup priority 50 for all replicas log backups aren't running on secondary replica.
If I do prefer secondary full backups wont run on primary.
How can I configure to take full/diff on primary only and log backup on secondary only?
The reason we wanted to offload backups to secondary replica is to improve performance on primary during peak hours.
Our current setup takes full every 3 days and diff everyday after hours, we’ve log backups running every 15mins. It’s through SQL Agent job with custom SP.
You can take log backups in always on availability groups on any replica, even if your full backups are taken on a different replica. Log chains are persisted across all replicas. For example, let’s say you perform the following backups in order.
- full on primary
- log on primary
- log on secondary
- log on primary
To get back to the most current time, you’d need to restore all three logs. In fact, you’ll get an error if you skip the log backup from the secondary.
Therefor, taking log backups on the secondary will still allows the primary to truncate its log.
For this reason, it is not recommended to take log backups on more than one replica.
However, I’d still advise all backups be taken on the primary to avoid the potential for data loss. But I still think it’s important to understand how this process works.
Back to the original question. Setting backup preference in availability group properties only affects the output of the
sys.fn_hadr_backup_is_preferred_replicafunction. If your backup jobs aren’t referencing that function, they will take a backups wherever they’re executed, with no regard to your preference setting.