4 4 3 Beefalo:Support for MS SQL AlwaysOn Availability Groups

From SEPsesam
Jump to: navigation, search

Copyright © SEP AG 1999-2020. 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.

Draft.png WORK IN PROGRESS
This is a draft document for the SEP sesam upcoming 4.4.3 Beefalo V2 release. Note that the article is in the initial stage and may be updated, replaced or deleted at any time. It is inappropriate to use this document as reference material as it is a work in progress and should be treated as such.
Docs latest icon.png Welcome to the latest SEP sesam documentation version 4.4.3 Beefalo V2. For previous documentation version(s), check MS SQL.


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

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 MS SQL 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 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.

Information sign.png 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.
  2. 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.
    • Information sign.png Note
      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.

    File:MS SQL AOAG bck.png

  3. If you have specified the backup source as all, you may want to exclude some databases from the backup. In this case, enter their name in the Exclude List field manually, e.g., *DB1,*DB2,*DB3.
  4. 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.
  5. 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.
  6. SEP Tip.png 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.
  7. Once you have configured a schedule, you must create a new backup event for it. For details, see Creating a backup event.

You can view the status of your backup jobs by selecting Last backup state in the Main Selection window. The backup status overview provides detailed information about the last run of backup jobs, including 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. 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.