4 4 3 Beefalo:Support for MS SQL AlwaysOn Availability Groups
As of 4.4.3 Beefalo V2, SEP sesam supports backups of the MS SQL Server AlwaysOn Availability Groups (AOAG) that provide several options for better database availability.
The MS SQL AlwaysOn environment can contain multiple availability groups, however, a separate availability group is required for each MS SQL instance. AG supports a set of read-write primary replicas and 1 to 8 sets of corresponding secondary replicas. Each AG has its own listener; the failover cluster resource which is responsible for application access and MS SQL client access. The listener always follows the primary replica node.
Currently, SEP sesam supports the configuration of the AOAG backup on the primary replica via the listener. All other secondary replica nodes are kept synchronous, but only read access is possible. Note that the AOAG databases can only run in the Full recovery model.
To ensure error-free operation of SEP sesam and improve performance, make sure that the following conditions are met:
- Check the support matrix for the list of supported MS SQL versions.
- SEP sesam Server ≥ 4.4.3. Beefalo V2. Check Hardware requirements for SEP sesam Server.
- MS SQL Server with installed SEP sesam Client package.
- A user account with domain administrator privileges to connect to all SQL databases. For details, see Specifying the user account for the SEP sesam client service.
- The SEP sesam MS SQL backup module requires a license. For details, see Licensing.
Configuring AOAG backup
Since the databases can fail over to a different cluster node, you have to perform the AOAG backup using the listener (IP name for accessing the database). All backup levels (FULL, DIFF, INCR and COPY) are supported for the AOAG backup on the primary replica (source server).
To perform the AOAG backup, proceed as follows:
- Install SEP sesam Client on all MS SQL cluster nodes. You have to run the SEP sesam Client service as domain administrator.
- Configure the listener and all MS SQL nodes via FQDNs (fully qualified domain names) as a separate MS SQL client.
- Create an MS SQL backup task at the listener only.
Adding the MS SQL client to SEP sesam environment
Once you have downloaded the SEP sesam package, configure your MS SQL client for the listener and all MS SQL nodes by adding it to the SEP sesam environment: Main selection -> Components -> Topology -> New client -> add your MS SQL client. For details, see Configuring Clients.
|Before proceeding, you should validate that you can backup to SEP sesam. Run a test Path backup manually to ensure that your backups work. For details on how to configure backup, see Standard Backup Procedure.|
Creating MS SQL backup task
To perform an AOAG backup, you have to create an MS SQL backup task at the listener only.
- From Main Selection -> Tasks -> By Clients, select the MS SQL client that you have configured for the listener and all MS SQL nodes, and click New backup task. The New backup task window opens.
- Specify the Source. You can back up all databases at once or choose to back up only the selected ones.
- To back up all databases, specify all as the backup source for all listeners. You can browse for the source or enter it manually. When you browse for the source, the task type and task name are set automatically. If you enter the source manually, you have to enter the task name and select the task type from the drop-down list.
- To back up only selected databases, enter their names manually, e.g., DB1. When backing up more databases, enter their names followed by a comma (without spaces), e.g., DB1,DB2,DB3.
|If you have specified the backup source for the individual database(s) as <hostname>/<instance>/<DB_name>, e.g., SQL17-AOHAG1/AOHAG1/DB1, the backup will no longer work after a failover to a different node. Use only the database name(s) as a backup source.|
Click OK to create the task.
|You can also add your backup task to an existing schedule by double-clicking the backup task, selecting the tab Schedules and adding it to one or more schedules. Additionally, you can group your backup tasks to task groups. For details, see Adding a Task to the Task Group.|
You can view the status of your backup jobs in the GUI (Monitoring -> Last Backup State or Job State -> Backups) or SEP sesam Web UI. The backup status overview provides detailed information about the last run of backup jobs, including the task name, start and stop time of the last backup, backup level, data size, throughput, assigned media pool, etc.
Restoring AOAG databases
It is not possible to restore the MS SQL database that is part of AlwaysOn Availability Group directly to the AOAG. To restore the AOAG database successfully, proceed as follows:
- Remove the database from the AOAG.
- In SEP sesam GUI, restore the MS SQL database to the primary replica.
- Add the restored database back to the AOAG by using Full as the data synchronization option.
Step 1: Removing the database from AOAG
You can remove the database from the AlwaysOn Availability Group by using the MS SQL Server Management Studio:
- On the primary replica (server), expand the list of Availability Databases for the AG.
- Right-click the database you want to restore and select Remove Database from Availability Group... from the context menu.
- Click OK to remove the database from the AG.
Step 2: Restoring MS SQL database
Restoring the AG database is similar to a normal MS SQL database restore. You have to restore the database to the primary replica (server) in AlwaysOn Availability Group by selecting the last Full backup and Overwrite existing items execution option. Note that the restore node can be any of the replica nodes.
The restore is triggered from the SEP sesam GUI menu bar -> Activities -> Restore or via the web interface Restore Assistant. The restore wizard guides you through the process of restoring your data. For details, see MS SQL Restore.
Step 3: Adding the restored database to the AOAG
After you have restored the MS SQL database to the primary replica (server), add it back to the AlwaysOn Availability Group:
- Open the MS SQL Server Management Studio. In the Database folder on the secondary replica (server), right-click the database and click Delete.
- In the AG on the primary replica, right-click Availability Databases and select Add Database... from the context menu. Click Next.
- Select the database you want to add to the AG and click Next.
- Select Full as the data synchronization setting. This will take a full backup of the database on the primary replica and restore it on the secondary replica(s). You have to specify a network location that is accessible to the primary and all secondary replicas where the backup files are to be placed. Click Next.
- Click the Connect... button to connect to the secondary replicas(s). Click Next.
- The Add Database to Availability Group wizard validates all settings for the new availability group database. After the validation is complete, click Next.
- Review your settings and click Finish to add the database to the AG.