Backup preference
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_replica to make sure it's not the preferred replies for Full/Diff backups.
- The
backup_priority of 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.
