Loading...

AlwaysOn MSSQL Availability Groups

SnapProtect data protection for AlwaysOn Availability Groups provides a disaster-recovery solution for databases on SQL Server 2012 and later. AlwaysOn Availability Groups support a failover environment for a set of user databases, known as availability databases, that fail over together. An Availability Group client protects availability databases based on the backup preferences and backup priorities configured for the availability group, and provides a single entry point for initiating backups and restores.

Availability Group Client

After installing the SQL Agent on all the physical nodes of an AlwaysOn cluster, create a new Availability Group (AG) client. An AG client is a logical grouping of one or more availability groups. You can create separate AG clients for each availability group or use the same AG client for all the availability groups, even if these availability groups belong to different clusters.

For steps to create an AG client, see Creating an Availability Group Client.

Alternatively, if you already have an AG client created, you can add an availability group to the AG client.

For steps to add an availability group to an existing AG client, see Adding Availability Groups to an Availability Group Client.

Notes:

  • Use the same impersonation account for all the SQL instances of an AG client. For more information, see User Account and Password Management - SQL Server Agent.
  • Configure the user account at the instance level. The availability groups may belong to different clusters under the same SQL AG client, so the permissions may not work at the agent level.
  • This feature is not supported for SnapProtect and Volume Shadow Copy Service (VSS).

Backups on an Availability Group Client

You can either use the default subclient or create a new subclient to run backups on availability group clients. You can run the backup operation the same way that you run backups on a regular SQL subclient.

When you start the backup, the proxy client runs the master process, and then retrieves the backup information from the availability group. Depending on the type of backup, backup preference, and the backup priority, the backup job runs on either the primary replica or one of the secondary replicas. All activities of the master process are logged and available for review in the SQLBackupmaster.log file on proxy client.

Notes:

  • Full and differential backups always run on the primary replica.
  • Full copy only and transaction log backups run either on the primary replica, if the primary replica is set as the preferred replica for running backups, or on the secondary replica with the highest backup priority.
  • For successful backup of availability databases, verify that the secondary replica can communicate with the primary replica, and that the replicas are either Synchronized or Synchronizing. For more information, see Article ID: SQL0018.

If the preferred replica for running a backup job is not available to backup, then the backup runs on the next available replica. The backup operation fails if the CVD services on the selected replica are down.

Databases that are a part of an availability group reside on the physical instances. These databases are backed up twice, from the AG client and also as part of the physical instance backups. You can prevent two times backup of databases by using one of the following methods:

  • If all the AG clients are configured, you can prevent the databases from getting backed up twice by setting the value of the global parameter BackupAGDBsViaActualInstance to zero. Use the Command Line Interface to set the value of the global parameter. For more information, see qcommand execscript for details.
  • If all the AG clients are not configured, but you still want to back up the databases by using an AG client and not as part of the physical instance backups, add the databases on the physical instance to the Do Not Backup subclient. After the databases are added to the Do Not Backup subclient, they are not included in the subsequent instance backup operations.

    For more information, see Excluding Databases from Backup Operations.

For steps to run a backup operation, see Backups for SQL Server Agent.

Availability Database Restores

An availability group instance protects a set of user databases, known as availability databases. By default, a database is restored to the same location where it was backed up, and the existing database files are overwritten. Because restores are not allowed on any replica of availability groups, before running the restore, you must remove the database from the availability group. After running the restore operation, you can manually add the database back to the availability group.

Even if you performed the backup on a different replica, run the browse and restore operations from the availability group instance, because all backups are registered against the availability group instance. Run the restore to an instance installed with the same SQL version or later than the instance where the backup ran.

For steps to run a restore operation, see Restores for SQL Server Agent.

Best Practice

It is a best practice to set primary as the preferred backup replica in the SQL Management Studio:

  • Setting primary as the preferred replica ensures that the complete backup cycles are available for restore later. Setting this preference eliminates the possibility of few errors. For more information on these errors, see the Article ID: SQL0017.
  • If transaction log backups are running on all the replicas, and truncation happens during backup on secondary replica, it can lead to breaking of Log Sequence Numbers (LSN) on secondary replica. This will automatically attempt to convert the next transaction log backup into a full backup, which is not supported on a secondary replica.