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.
Setting the backup preference in availability group properties only affects the output of the
sys.fn_hadr_backup_is_preferred_replica function. 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. This setting also applies overall to any backup type that calls this function. So, you’d have to use custom code to manipulate log backup preference to be somewhere different than what your Full and Diff backup preference is.
Custom stored procedure to offload log backups
To offload log backups to a replica other than the preferred replica where Full and Diff backups are taken, you’d need to check two values to determine if the current replicas is a candidate for the Log backup.
- The result of
sys.fn_hadr_backup_is_preferred_replicato make sure it’s not the preferred replies for Full/Diff backups.
backup_priorityof the current replicas in
sys.availability_replicas, so that you don’t blindly take log backups on all secondary replicas.
You’d want to create this as a stored procedure and reference it in your log backup jobs. Below is a conceptule stored procedure you might be able to use to accomplish this. ** Note, I just threw this together as an example and have not tested it. Use at own risk!
CREATE FUNCTION is_preferred_replica_for_log_backup ( @DatabaseName ) RETURNS bit BEGIN IF sys.fn_hadr_backup_is_preferred_replica (@DatabaseName) = 1 BEGIN /* Is preferred for Diff/Full, skip log backup */ RETURN 0 END IF sys.fn_hadr_backup_is_preferred_replica (@DatabaseName) = 0 BEGIN IF @@SERVERNAME = ( /* If the replicase contains more than one Availability Group, some additional code would be needed here to filter to the correct one */ SELECT TOP 1 replica_server_name FROM sys.availability_replicas ORDER BY backup_priority ) BEGIN /* Replica is preferred for Log backups */ RETURN 1 END ELSE BEGIN RETURN 0 END END /* Failsafe: DB not in AG or status not be determinable, take log backup. */ RETURN 1 END
Risks of splitting up backups and preferences
Log backups may be taken on any and all replicas within an Availability Group, regardless of where the full backups are taken. This is because
log chains are persisted across all replicas in the availability group. For example, let’s say you perform the following backups, in this order.
- full backup 1 on the primary replica
- log backup 1 on the primary replica
- log backup 2 on the secondary replica
- log backup 3 on the primary replica
After log backup 2 on the secondary replica completes, the Primary replica will be able to truncate its transaction log up to that point in time. As such, when log backup 3 the primary replica runs, it will not be able to backup log data that was already backed during log backup 2 on the secondary replica.
To perform a point-in-time restore in this scenario, you would need to restore the full backup taken on the primary, plus all three log backups taken across all replicas.
For this reason, I would not recommended spreading backups across more than one replica. In fact, I’d actually advise all backups be taken on the primary replica to help avoid potential data loss. I discuss these potential data loss issues in more detail in my recent blog post on Offloading Database Backups to Secondary Replicas. If your real issue is performance during peak times, considering adding additional resources on your SQL Servers to support all backup types running on the primary replica during peak times.