AlwaysOn Availability Groups
Published 03 November 2014
You can use SQL Backup Pro to back up AlwaysOn Availability Groups, including taking a backup from a ReadOnly secondary replica.
For backup activity on ReadOnly secondary replicas, if SQL Backup Pro’s extended stored procedures run under a user account without the sysadmin server role, you might need to skip user rights checks.
To do this:
- Open Registry Editor and locate HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\(local) or <instance name>
- Create a new DWORD type registry entry called SkipChecks and set the data value to 1.
- From the Windows Services panel, stop and restart the SQL Backup Agent service.
To reinstate user rights checks, set the data value to 0 or delete the SkipChecks entry.
For more information about the types of backup allowed on Availability Group primary and secondary replicas, see AlwaysOn Availability Groups (SQL Server) on MSDN.
Availability Group backup preferences
SQL Backup ignores Availability Group backup preferences. If you want a scheduled job to use these preferences, you need to directly modify the job and precede the SQL Backup Pro stored proc call with a check on the backup preferences. For example:
IF (sys.fn_hadr_backup_is_preferred_replica(@DBNAME)<>1) BEGIN Select 'This is not the preferred replica, exiting with success'; RETURN -- This is a normal, expected condition, so the script returns success END
You can schedule the same backup job to run on every availability replica. Each job uses the same data to determine which job should run, so only one of the scheduled jobs actually proceeds to the backup stage.
For more information, see Configure Backup on Availability Replicas (SQL Server) on MSDN.