5 1 0:Support for MS SQL AlwaysOn Availability Groups

From SEPsesam


Welcome to the latest SEP sesam documentation version 5.1.0 Apollon. For previous documentation version(s), check documentation archive.


Overview


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.

Requirements

To ensure error-free operation of SEP sesam and improve performance, make sure that the following conditions are met:

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

System databases cannot be backed up via the listener.

To perform the AOAG backup, proceed as follows:

  1. Install SEP sesam Client on all MS SQL cluster nodes. You have to run the SEP sesam Client service as domain administrator.
  2. Configure the listener and all MS SQL nodes via FQDNs (fully qualified domain names) as a separate client.
  3. 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 the listener and all MS SQL nodes by adding it to the SEP sesam environment: Main selection -> Components -> Clients -> New client -> add your MS SQL client. For details, see Configuring Clients.

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

  1. 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. Select MS SQL Server as task type.
  2. Specify the Source. You need to create a separate task for each database.
    • To back up a databases, enter their name manually, e.g., DB1. If you want to backup multiple databases create more tasks and combine the tasks into a task group. For more information see MS SQL Backup.
    • Note
      If you have specified the backup source for the individual database 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 as a backup source.
  3. Optionally, switch to the Options tab and specify any additional options for backup or restore, such as pre/post operations, modify log level, etc.
    Click OK to create the task.
  4. If you want to start the newly created task immediately, right-click the name of the task and click Immediate Start. If you want to create a periodic backup, you have to create a schedule for your backup task: Click New schedule under Main Selection -> Scheduling -> Schedules and set up a schedule. For more details, see Creating a Schedule.
  5. Tip
    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.
  6. Once you have configured a schedule, you must create a new backup event for it. For details, see Creating a backup event.

Monitoring backups

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:

  1. Remove the database from the AOAG.
  2. In SEP sesam GUI, restore the MS SQL database to the primary replica.
  3. 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:

  1. On the primary replica (server), expand the list of Availability Databases for the AG.
  2. Right-click the database you want to restore and select Remove Database from Availability Group... from the context menu.
  3. 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:

  1. Open the MS SQL Server Management Studio. In the Database folder on the secondary replica (server), right-click the database and click Delete.
  2. In the AG on the primary replica, right-click Availability Databases and select Add Database... from the context menu. Click Next.
  3. Select the database you want to add to the AG and click Next.
  4. 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.
  5. Click the Connect... button to connect to the secondary replicas(s). Click Next.
  6. The Add Database to Availability Group wizard validates all settings for the new availability group database. After the validation is complete, click Next.
  7. Review your settings and click Finish to add the database to the AG.

See also

MS SQL BackupMS SQL Restore

Copyright © SEP AG 1999-2024. All rights reserved.
Any form of reproduction of the contents or parts of this manual is allowed only with the express written permission from SEP AG. When compiling and designing user documentation SEP AG uses great diligence and attempts to deliver accurate and correct information. However, SEP AG cannot issue a guarantee for the contents of this manual.