AlwaysOn Availability Groups back up schedules

Question

We have AlwaysOn Availability Groups primary and secondary replicas where the secondary replica is preferred for back up jobs to take place as long as it is available. I created back up jobs on both replicas and scheduled the jobs at the secondary replica.

My question is: - Do I have to schedule the jobs at the primary replica and then disable them until fail-over occurs and then manually enable jobs, or what is the best way to deal with these please?

Answer 1

My question is: - Do i have to schedule the jobs at primary replica and then disable them until fail-over occurs and then manually enable jobs or what is the best way to deal with these please?

No, you should just add a check with the new function sys.fn_hadr_backup_is_preferred_replica

if sys.fn_hadr_backup_is_preferred_replica('your db name') <> 1
begin
    print 'This is not preferred backup replica .....'
        -- If this is not the preferred replica, exit (without error).
end
else
begin
    print 'This is the preferred replica .. continue with backup'
        -- If this is the preferred replica, continue to do the backup.
        -- here goes the backup command .....
end

Alternatively, you can use SQL Server Maintenance Solution - by Ola hallengren which supports AlwaysON as well.

Answer 2

Below steps are used if you are using https://ola.hallengren.com/ , SQL Server Maintenance Solution - by Ola hallengren which supports AlwaysON as well.

I recommend that you configure it like this:

1. Have the jobs enabled and scheduled on both servers.

2. Add @CopyOnly = 'Y' to the full backup jobs.

3. Drop the differential backup jobs

Ola's jobs will then automatically perform the backup on the preferred replica.