SQL Server Availability group and Backups

Posted on

Question :

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.

Answer :

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.

  1. The result of sys.fn_hadr_backup_is_preferred_replica to make sure it’s not the preferred replies for Full/Diff backups.
  2. 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.

  1. full backup 1 on the primary replica
  2. log backup 1 on the primary replica
  3. log backup 2 on the secondary replica
  4. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *