IBM Tivoli Storage FlashCopy Manager for Windows, Version 4.1

Configuring where scheduled backups are run on SQL availability replicas

You can configure to select an availability replica on which to run a backup.

About this task

When an availability database is replicated across multiple availability replicas in an availability group, a mechanism is required to select a single replica on which to run a backup operation instead of backing up all replicas. This mechanism can also offload the backup from a primary replica to a secondary replica for load balancing. When databases failover, backups must continue to run from other replicas for high availability.

Microsoft SQL Server 2012 provides a set of configuration options that enable these functions. You can use the Tivoli® Storage FlashCopy® Manager GUI to set these options.

Procedure

To specify whether scheduled backups are run on the primary or secondary replica at the availability group level:

Start the Management Console.
In the Management section of the window, click Protect Data next to the SQL Server workload.
Click Properties in the Actions pane.
Click the AlwaysOn Preferences property page.
In the Availability group field, select the AlwaysOn Availability Group for which you want to set up backup preferences.
In the Preferred replica field, select which replica is the preferred replica on which to run scheduled backups.
- Select Prefer Secondary replica if you want scheduled backups to occur on a secondary replica, if it is available. Otherwise, use the primary replica for the scheduled backup.
- Select Secondary only if you want scheduled backups to occur only on a secondary replica.
- Select Primary if you want scheduled backups to occur only on the primary replica.
- Select Any replica if you want scheduled backups to occur on any availability replica.
For each availability replica listed in the Availability replicas list box, specify whether it is a candidate for running scheduled backups by specifying the backup priority for that replica. A value of 1 has the lowest priority, and a value of 100 has the highest priority. A value of 0 indicates that the replica is excluded from schedule backup operations.
Click OK to save your configuration and exit the Data Protection Properties page. The settings are saved to the tdpsql.cfg file and can be replicated to the other replicas in the availability group.

What to do next

After you configure where scheduled backups are run, the administrator can specify the tdpsql backup command along with the /ALWAYSONPriority parameter in a backup schedule. For example:

tdpsqlc backup TestDb1 full /ALWAYSONPriority

When this scheduled backup command is run, Tivoli Storage FlashCopy Manager queries the SQL Server to determine the highest-priority availability replica that is active or online, ordered by preference. If the replica meets the specified criteria, the replica is backed up. Otherwise, the backup operation ends and a message is added to the log to indicate why the replica was not backed up.

An administrator can create a common backup schedule to run on all availability replicas. When the backup schedule starts, each tdpsqlc command queries each replica to determine whether it is to run the backup. Only one of the scheduled backups runs the backup.

Feedback