SQL Backup 7

AlwaysOn Availability Groups

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.

If the 'sysadmin' role is granted via group membership, and not directly to the account, you still need to skip user checks.

To do this:

  1. Open Registry Editor and locate HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\(local) or <instance name>
  2. Create a new DWORD type registry entry called SkipChecks and set the data value to 1.
  3. 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.


Didn't find what you were looking for?