Creating SQL Server backups in AAG environment

Edit online

On SQL Server 2012 and later versions, you can back up databases in an AlwaysOn Availability Group (AAG) cluster. This procedure describes different approaches for backing up your SQL Server in an AAG cluster environment.

Before you begin

  • To run a legacy backup, ensure that the Data Protection for SQL Server license file is installed.
  • To back up availability databases, ensure that IBM Storage® Protect Snapshot is configured to use an AlwaysOn node. Additionally, specify the AlwaysOn node in the AlwaysOn Node field in the IBM Storage Protect Node Names page of the IBM Storage Protect Configuration Wizard.
  • For all backup operations of secondary availability replicas, ensure that the secondary replicas are in a synchronized or synchronizing state.
  • When running a scheduled backup using a script of a database in an availability group, ensure the backup preferences for the availability group are set. This is done at AAG level when the AAG is being set up. Ensure the backup priority option that defines the order in which replicas are used to back up a database in an AAG is set. Also, ensure that the preferred replica to define whether primary or secondary replicas can be used for backup operations is set. For more information, see Data Protection for AlwaysOn Availability Groups.

Create backup using MMC

Edit online

About this task

On MMC, you can choose the replica you want to run the backup on and depending on the replica you want to back up, the following backup types are available. You can select the following types of VSS backup operations:
  • Full VSS backups of the primary availability replica
  • VSS copy-only full backups of availability replicas

You can run the following types of legacy backup operations:

  • On the primary replica, legacy full, differential, file, set, group, and log backups
  • On the secondary replica, legacy full, file, set, group, and log backups
  • VSS and legacy copy-only full backups, legacy copy-only file, set, or group backups, and legacy copy-only and normal log backups

Procedure

  1. Start MMC.
  2. In the tree view, select the SQL Server node instance which you want to back up.
  3. On the Protect tab of the selected SQL Server node instance, select an option for viewing databases and information about the included databases.
    Table 1. Database backup views
    Task Action
    View a list of SQL Server databases that are available for a backup operation Click View: Databases.
    View a list of SQL Server 2012 and later version availability databases that are available for a backup operation Click Standard Databases. Information about the availability databases in an availability group is displayed, including the replica role, synchronization state, and space and log usage.

    Toggle the Standard Databases / Availability Databases button for the respective database views.
  4. Verify the backup options. If the backup options are not displayed, click Show Backup Options.
  5. In the Actions pane, click Backup Method and select Legacy.
  6. In the Actions pane, select TSM for the Backup Destination.
    The only option that is available to you is TSM because the database backups are stored on IBM Storage Protect server storage.
  7. Optional: Choose a mode for the current task:
    • Run Interactively: Click this item to run the current task interactively. This selection is the default.
    • Run Scheduled: Click this item to convert the current action into a scheduled task. When you select this item, the schedule wizard runs the command that is required to complete the task.
  8. To start the backup operation, in the Actions pane, take one of the following actions:
    • Full Backup
    • Copy-Only Full Backup
    • Differential Backup to IBM Storage Protect
    • Log Backup to IBM Storage Protect
  9. Review the status of the backup operation by clicking Task List in the results pane. Click Task Details to view detailed status information.

Create scheduled backup

Edit online

Procedure

  1. Start MMC.
  2. In the tree view, select the SQL Server node instance and open the Automate tab. An integrated command line is available in the task window from which you can enter PowerShell cmdlets or command-line interface commands.
  3. Toggle the PowerShell / Command Line option to choose which you wish to use.
  4. To run using the command-line interface, in the Details pane, create a command script to run a backup command and to run, click the Execute icon.
    For example, to create a legacy backup, execute the following command:
    tdpsqlc backup db1,db2,db3 full /alwaysonpriority
    To create a VSS backup, execute the following commands. For each database, execute a separate command.
    tdpsqlc backup db1 full /alwaysonpriority /backupmethod=VSS backupdest=TSM
    tdpsqlc backup db2 full /alwaysonpriority /backupmethod=VSS backupdest=TSM
    tdpsqlc backup db3 full /alwaysonpriority /backupmethod=VSS backupdest=TSM
  5. To run a scheduled backup using Powershell cmdlets, you can do so on the Result pane or the Task List pane. For more information, see Automating tasks. The format for SQL Server cmdlets is Xxx-DpSqlxxx and to view a full list of available cmdlets, enter Get-Help. For example, to run a backup, execute Backup-DpSqlComponent.
  6. Review the status of the backup operation by clicking Task List in the results pane. Click Task Details to view detailed status information.