When an availability database is replicated across multiple availability
replicas in an availability group, a configuration option is available to enable you 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
Tivoli® Storage FlashCopy® Manager 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, 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.