Enhanced data protection for Microsoft SQL AlwaysOn Availability Groups

You can run VSS (full) and legacy (full, differential, file/set/group, and log) backup operations on a primary replica. You can run copy-only VSS and legacy backup operations, and normal log backups on a secondary replica. You cannot run a differential backup on a secondary replica.

For backups on a secondary replica, the replica must be in the synchronized or synchronizing state. You can have multiple AlwaysOn Availability Groups (AAGs) in a SQL Server cluster. You can also have a mix of standard databases and AAGs on a SQL Server cluster.

When you back up data, you can distribute the backup workload for scalability and isolate backup activity to a dedicated backup node. When you isolate backup activity, it minimizes the effect on production databases.

Given that replicas are copies of the same database, avoid redundant backups of the same databases. Apply retention policies to unique databases.

As a best practice, allow backups from any node in the availability group and enable restore operations from any node in the availability group.

Best practices for backing up data in an AAG

When you use Tivoli® Storage FlashCopy® Manager for SQL Server to manage AAG backups, consider the following backup options:
Backup priority
Specified per database in an AAG, the backup priority option defines the order in which replicas are used to back up a database in an AAG.
Preferred replica
Specified at an AAG level, the preferred replica option defines whether primary or secondary replicas can be used for backup operations.
  • Prefer secondary replica: Scheduled backups occur on a secondary replica, if available. If the secondary replica is not available, you can use the primary replica.
  • Secondary only: Scheduled backups can occur only on a secondary replica.
  • Primary: Scheduled backups can occur only on the primary replica.
  • Any replica: Scheduled backups can occur on any replica.
/USEALWAYSONNode parameter
A parameter option on the backup command that provides a common namespace for all backups. Each node authenticates separately with Tivoli Storage Manager. Backed up data is stored in the AlwaysOnNode namespace by using the Asnode option.
/ALWAYSONPriority parameter
A parameter option on the backup command that specifies that a local availability database is backed up only if it has the highest backup priority among the availability replicas that are working properly. This parameter applies only to scheduled backups.

Typical data protection deployments in AAG environments

You can back up data in an AAG in the following ways:
  • Distribute a legacy backup across AAG replicas
  • Distribute a VSS backup across AAG replicas
Approach®: Legacy backups are distributed across AAG replicas
When you configure your environment to distribute a legacy backup across AAG replicas, follow these steps:
  1. Set the preferred replica to Prefer secondary replica.
  2. Install Tivoli Storage FlashCopy Manager for SQL Server on all replicas that are eligible to run a backup.
  3. Create a command script to run a .CMD file with a backup command similar to the following example: 
    tdpsqlc backup db1,db2,db3 full /alwaysonpriority
  4. Associate each Tivoli Storage FlashCopy Manager for SQL Server node with the defined schedule.
  5. Run backups on the SQL node according to defined priorities for each database.
Scenario: VSS backups are distributed across AAG replicas
When you configure your environment to distribute a VSS backup across AAG replicas, follow these steps:
  1. Set the preferred replica to Prefer secondary replica.
  2. Install Tivoli Storage FlashCopy Manager for SQL Server on all replicas that are eligible to run a backup.
  3. Create a command script to run a .CMD file with a separate backup command per database similar to the following sample
    tdpsqlc backup db1 full /alwaysonpriority /backupmethod=VSS 
	backupdest=TSM
tdpsqlc backup db2 full /alwaysonpriority /backupmethod=VSS 
	backupdest=TSM
tdpsqlc backup db3 full /alwaysonpriority /backupmethod=VSS 
	backupdest=TSM
  4. Associate each Tivoli Storage FlashCopy Manager for SQL Server node with the defined schedule.
  5. Run backups on the SQL node according to defined priorities for each database.