Sql-server – AlwaysOn Availability Groups back up schedules

availability-groupssql server

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?

Best Answer

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.

Related Question