Enhanced data protection for Microsoft SQL AlwaysOn Availability Groups (AAGs)

Content

AlwaysOn Availability Group (AAG) overview

The AlwaysOn Availability Group (AAG) provides high availability and disaster recovery at the SQL database level. SQL AlwaysOn Failover Clusters Instance provide high availability and disaster recovery at the SQL server level. AAGs and SQL AlwaysOn Failover Clusters Instances can be used together. When working in a AAG environment, here are some topics to consider:

• A group of single or clustered server instances, each holding a copy of all databases, can be in the AAG.
• There can be as many as five online copies of a database (one primary and up to four secondary copies).
• Synchronous and asynchronous replication.
• Log shipping.
• Automatic and manual failover modes.
• Databases within an AAG failover as a group.

Backup solutions

All VSS (full) and legacy (full, differential, file/set/group, and log) backup operations are supported on the primary replica. Copy-only VSS and legacy backup operations, and normal log backups are supported on a secondary replica. No differential backup on a secondary replica are supported.

For backups on a secondary replica, the replica must be in the synchronized or synchronizing state. You can have multiple AAGs in a SQL server cluster. You can also have a mix of standard databases and AAGs on a SQL server cluster. All databases included in a VSS-type backup are snapped together.

When backing up data, provide options from backup deployment. You want to distribute the backup workload for scalability and isolate backup activity to a dedicated backup node. Isolating backup activity minimizes the impact to production databases.

Ideally, avoid redundant backups of the same databases. Recognizing all replicas as copies of the same database helps achieve this goal. You can also apply retention policies to "unique" databases.

Allowing backups from any node in the availability group and enabling restores from any node in the availability group is also ideal.

Achieving goals with the Data Protection for Microsoft SQL Server component

When using Data Protection for Microsoft SQL Server to manage AAG backups, use the following SQL Server constructs:

• Backup priority: Specified per database in an AAG. Defines priority order for which replica should be used to back up a database in an AAG.
• Preferred replica: Specfied at an AAG level whether primary or secondary replicas can be used for backup.
• Prefer secondary replica: Scheduled backups occur on a secondary replica, if available. If the secondary replica is not available, the primary replica can be used.
• Secondary only: Scheduled backups can only occur on a secondary replica.
• Primary: Scheduled backups can only occur on the primary replica.
• Any replica: Scheduled backups can occur on any replica.

You can also use the following Data Protection for Microsoft SQL Server constructs:

• The ALWAYSONNODE parameter provides a common namespace for all backups. Each node authenticates separately with Tivoli Storage Manager. Backed up data is stored in the AlwaysOnNode namespace (using the Asnode option).
• The /ALWAYSONPriority parameter 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 only applies to scheduled backups.

Sample data protection deployments in AAG environments

There are two approaches to back up data:

• A legacy backup is distributed across AAG replicas.
• A VSS backup is distributed across AAG replicas.

Approach 1: Legacy backup is distributed across AAG replicas

When a legacy backup is distributed across AAG replicas, use the following list when configuring the environment:

• Set the preferred replica to prefer secondary replica.
• Install Data Protection for Microsoft SQL Server on all replicas eligible to run a backup.
• Define a CMD type schedule to run a CMD file with a backup command similar to the following sample:
tdpsqlc backup db1,db2,db3 full /alwaysonpriority
• Associate each Data Protection for Microsoft SQL Server node with the defined schedule.
• Run backups on the SQL node according to defined priorities for each database.

Approach 2: VSS backup is distributed across AAG replicas

When a VSS backup is distributed across AAG replicas, use the following list when configuring the environment:

• Set the preferred replica to prefer secondary replica.
• Install Data Protection for Microsoft SQL Server on all replicas eligible to run a backup.
• Define a CMD type schedule 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
• Associate each Data Protection for Microsoft SQL Server node with the defined schedule.
• Run backups on the SQL node according to defined priorities for each database.

For more information about Tivoli Storage Manager for Databases: Data Protection for Microsoft SQL Server, see http://www.ibm.com/support/knowledgecenter/SSTFZR_7.1.0/com.ibm.itsm.db.sql.doc/t_protect_dpdbsql.html.

For more information about Tivoli Storage FlashCopy Manager for Windows, see http://www.ibm.com/support/knowledgecenter/SS36V9_4.1.0/com.ibm.itsm.fcm.win.doc/t_protect_fcmw.html.