Always On MSSQL Availability Groups Support for SharePoint Agent

Commvault data protection for Always On Availability Groups provides a disaster-recovery solution for databases on SQL Server 2012 and later. Always On 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 Always On 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.

SharePoint Backups on an Availability Group Client

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 operation 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.

If the preferred replica for running a backup operation 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.

While creating a new SharePoint subclient, verify that you select the availability group client as the SQL client. For steps to create a new SharePoint subclient, see Creating a User-Defined Farm Subclient.

For steps to run a full backup operation, see Backing Up SharePoint Farm.

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 SharePoint client. Run the restore to an instance installed with the same SQL version or later than the instance where the backup ran.

While restoring an availability database, ensure that you first restore only the content databases, both on the primary replica and the secondary replica. After restoring the content databases, you can restore the web applications alone.

Following are the high level steps to restore availability databases:

1. On the primary replica, restore only the content database component of the selected availability databases that you want to restore. Select the restore type as Recovery, this will leave the database in an online state.

   For more information about restoring only the content database, see Restoring a Content Database without Web Application.

2. On a secondary replica, restore only the content database component of the selected availability databases that you want to restore. Select the restore type as Norecovery, this will leave the database in a restoring state.

   You can repeat this step to restore databases from multiple secondary replicas.

   For more information about restoring only the content database, see Restoring a Content Database without Web Application.

3. Restore the web applications associated with the databases restored in the above two steps.

   For more information about restoring only the web application component, see Restoring a Windows SharePoint Services (WSS) Web Application.

4. Add the restored databases to an Always On availability group.

   For steps to add the restored databases to an availability group, go to the Microsoft Docs website, Availability Group - Add a Database.

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.