When an availability database
is replicated across multiple availability replicas in an availability group, a configuration option
is available that you can use to select a single replica on which to run a backup operation instead
of backing up all replicas.
About this task
Microsoft SQL Server 2012 and later versions provide a
set of configuration options that you can use to specify whether scheduled backups are run on the
primary or secondary availability replica. You can use the IBM Storage Protect Snapshot GUI to set
these options.
The configuration option can also be used to offload the backup from a primary
replica to a secondary replica for load balancing. When databases fail over, backups must continue
to run from other replicas to ensure that high availability is maintained.
Procedure
- Start Microsoft
Management Console (MMC).
- In the Management section of the
window, click Protect Data next to the SQL Server workload.
- In
the Action pane, click Properties.
- 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 that is 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, IBM Storage Protect Snapshot 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.