Skip to main content
Druva Documentation

Configure MS-SQL instanceĀ or AG for backup

Phoenix Editions: File:/tick.png Business File:/cross.png Enterprise File:/tick.png Elite

From version 4.7.1, after you register an MS-SQL server, the Phoenix agent discovers standalone instances and availability groups (AG) running on the server. Phoenix lists these instances and AGs under the Instances/AGs tab on the MS-SQL page. However, the server is not yet ready for backup. To back up MS-SQL databases in instances or AGs, configure the instances or AGs by creating MS-SQL backup sets.

 This topic provides instructions to configure the following:

Note: You cannot select the standalone instances and AGs simultaneously to create an MS-SQL backup set. 

Configure MS-SQL instance for backup

During the backup of a standalone instance, Phoenix excludes the AG databases from the backup.

To configure an MS-SQL instance for backup, perform the following steps:

Step 1: Attach server to an administrative group

The administrative group allows you to logically categorize servers for better management. To configure an MS-SQL instance for backup, you must first attach the server associated with the instance to an administrative group before creating a backup set. 

You can attach the registered server to an existing administrative group or create a new administrative group.

Attach server to an existing administrative group

If you have already created an administrative group, attach the registered server to the administrative group. 

Procedure

  1. Log on to Phoenix Management Console, select the organization from the menu bar, and click Protect MS-SQL Servers.
  2. Under the Instances/AGs tab on the MS-SQL page, configure servers as follows:
    • For single-instance configuration: Select one instance and click Create SQL Backup Set. The Create SQL Backup Set: Assign Administrative Group wizard opens.
    • For multi-instance configuration: Select multiple instances and click Create SQL Backup Set. The Create SQL Backup Set: Assign Administrative Group wizard opens.
    Note: Phoenix displays the Create SQL Backup Set: Assign Administrative Group page only if any instances associated with the server is not mapped to any administrative group.
  3. From the Administrative Group drop-down list, select the required administrative group, and click Next: Create Backup Set. For more information about creating a backup set for the server, see Create backup set for instance.

Note: Phoenix provides the Default Administrative Group without any servers attached to the group.

Attach server to a new administrative group

When you configure a server for backup, Phoenix lets you create a new administrative group to organize servers in the organization for better management.

Procedure

  1. Log on to Phoenix Management Console, select the organization from the menu bar, and click Protect MS-SQL Servers.
  2. Under the Instances/AGs tab on the MS-SQL page, configure instances as follows:
    Note: Phoenix displays the Create SQL Backup Set: Assign Administrative Group page only if the server associated with the selected standalone instance or AG is not mapped to any administrative group.

    SQL_instance_Attach_AdminGroup.PNG 

    • For single-instance configuration: Select one instance and click Create SQL Backup Set. The Create SQL Backup Set: Assign Administrative Group wizard opens.
    • For multi-instance configuration: Select multiple instances and click Create SQL Backup Set. The Create SQL Backup Set: Assign Administrative Group wizard opens.
  3. Click Create New Group adjacent to the Administrative Group drop-down list.
    The Create New Administrative Group window opens.
  4. In the Name box, type the name of the administrative group.
  5. In the Description box, type a description for the administrative group.
  6. Click Save.
    The administrative group created now appears in the Administrative Group drop-down list on the Create SQL Backup Set: Assign Administrative Group wizard.
  7. From the Administrative Group drop-down list, select the new administrative group, and click Next: Create Backup Set.

Step 2: Create backup set for MS-SQL instance

Backup set allows you to define the backup content and schedule for backing up databases of the instance.

Procedure

  1. Define the content to back up the databases of the instance.
    1. Under the Storage & Backup Content tab, provide the following information:
      Field Description
      Storage The Phoenix storage where the backed up data of the instance is stored.

      Select Content

       

      Under the Exclude database(s) box, enter the database names that you want to exclude from the backup. Phoenix excludes databases whose names are a partial or an exact match with the strings that you have provided. The database names are case-sensitive. 
      You can exclude databases that are mirrored or encrypted or databases with an apostrophe or a comma in their names.

      SQL_Storage&BackupContent.PNG

      Note: Databases that are part of an AG are auto-excluded from the standalone instance backup.

    2. Click Next to attach an instance of the MS-SQL server to a backup policy.
  2. Under the Backup Policy tab, you can attach the MS-SQL server instance to an existing backup policy or create a new backup policy and attach it to the instance.
  • Attach instance to an existing backup policy.
  1. From the Backup Policy drop-down list, select the required backup policy to attach it to the instance.
  2. Under the Policy Details section, verify the configuration summary of the selected backup policy, and click Finish. 

Step 1: Provide backup policy details

  1. Under the Summary tab, provide the following information:
    • Name: The name of the backup policy that you want to create.
    • Description: A short description of the backup policy. 
  2. Click Next to define the backup schedule.

Step 2: Define a backup schedule

  1. Under the Backup Schedule tab, provide the following information:
    Field Description

    Backup Type

    Select Full backupDifferential backup, or Transaction Logs.

    Start at

    The time when you want backups to start. In the adjacent box, select the AM or PM option depending on when you want backups to occur. 

    Duration (Hrs)

    The duration after which you want backup operations to stop. For example, if you set Start at to 5 AM and you set the duration to 5 hours, backups from the server start at 5 AM and stop at 10 AM, even if they do not complete. 

    Max Bandwidth (Mbps)

    The maximum bandwidth that each MS-SQL server can consume while backing up data to Phoenix. For scheduled backup, the job will consume the assigned bandwidth. However, for manually triggered backup, the job will consume the available bandwidth on your network.

    Repeat on

    The days on which you want backups to occur.

    For transaction log backups, you can specify an interval of 5, 10, 15, 30, 45, 60, or 120 minutes. Log backups are triggered as soon as a full or differential backup completes. 

Note: Log backups are not applicable for databases in the simple recovery mode.

  1. Click Add More to create more schedules.

  2. To add multiple schedules, repeat step a as many times as the number of schedules that you want to create. To delete a schedule, click the delete icon next to the schedule. If you have only one schedule defined, you cannot delete it.

  3. By default, the Ignore backup duration for first backup option is enabled. When this option is enabled, it ignores the specified backup duration for the first backup job. The first backup job is complete when the first restore point is created. You can disable this option to enforce backup duration for the first backup job. 

  4. By default, the Automatic Retry check box is enabled. If you do not want to define the automatic retrial options, clear the Automatic Retry check box.
    Provide the following details for the automatic retrial options:
    Field Description
    Max number of retries In an active schedule window, the maximum number of retry attempts for a job. By default, the field is set to 2 attempts.
    Wait interval before each retry The wait interval between each retry. By default, the field is set to 10 minutes.

    Automatic retry is not applicable for transaction log backups. For more details, see Backup retry.

Note: The snapshot timestamps for backup sets of the standalone instances are displayed according to the server time zone. For example, for servers located in New York and London, the timestamps are displayed according to EST and UTC time zones, respectively. . The first backup according to the schedule is a full backup. 

  1. Click Next to define the retention period.

Step 3: Define retention period

  1. Under the Retention tab, provide the following information.
    Field Description
    All snapshots for The duration for which you want to retain all snapshots.
    Weekly revisions for The duration for which you want to retain weekly snapshots.
    Monthly revisions for The duration for which you want to retain monthly snapshots.
    Yearly revisions for The duration for which you want to retain yearly snapshots.

Note: Ensure that you enter a value in at least one of the fields. Phoenix treats the values in the empty fields as zero. 

  1. Click Finish to create the backup policy.
    The created backup policy is listed in the Backup Policy drop-down list. 

  2. From the Backup Policy list, select the created backup policy.
    Under the Policy Details section, verify the configuration summary of the new backup policy, and click Finish. 

The instance configured for backup now appears under the SQL Backup Sets tab on the MS-SQL page. 

The agent backs up data from the instance based on the backup schedule and retains snapshots as defined in the retention period. 

Configure MS-SQL availability group for backup

Before you configure MS-SQL availability group for backup, ensure that the Phoenix agent is installed on all the nodes of the AG. The databases that are part of the AG will be backed up as part of the AG backup set. During the backup of an AG, Phoenix excludes the standalone instance databases from the backup.

Prerequisites

Before configuring the MS-SQL AG for backup, ensure the following:

  1. Install Phoenix agent on primary and all secondary nodes in the AG. If the Phoenix agent is not installed on the primary node of the availability group, Phoenix does not list details of all nodes of the AG on Phoenix Management Console.

  2. Enable the Readable secondary option for the secondary nodes for successful backups from that node.

  3. All the nodes in an AG must have the same Phoenix agent version installed on them.

To configure MS-SQL availability group for backup, perform the following steps:

Step 1: Attach AG to an administrative group

The administrative group allows you to logically categorize the servers for better management. To configure MS-SQL AGs for backup, you must first attach the AG to the administrative group before creating a backup set.

You can attach the registered AG to an existing administrative group or create a new administrative group.

Attach AG to an existing administrative group

If you have already created an administrative group, attach the registered AG to the administrative group. 

Procedure

  1. Log on to Phoenix Management Console, select the organization from the menu bar, and click Protect MS-SQL Servers.
  2. Under the Instances/AGs tab on the MS-SQL page, configure AGs as follows:
    • For single-AG configuration: Select one AG and click Add New Backup Set. The Assign Administrative Group wizard opens.
    • For multi-AG configuration: Select multiple AGs and click Add New Backup Set. The Assign Administrative Group wizard opens.
    Note: Phoenix displays the Assign Administrative Group page only if the selected AG is not mapped to any administrative group.
  3. From the Administrative Group drop-down list, select the required administrative group, and click Next. For more information about creating a backup set for the AG, see Create backup set for AG.

Note: Phoenix provides the Default Administrative Group without any servers attached to the group.

Attach server to a new administrative group

  1. Log on to Phoenix Management Console, select the organization from the menu bar, and click Protect MS-SQL Servers.
  2. Under the Instances/AGs tab on the MS-SQL page, configure AGs as follows:
    • For single-AG configuration: Select one AG and click Add New Backup Set. The Assign Administrative Group wizard opens.
    • For multi-AG configuration: Select multiple AGs and click Add New Backup Set. The Assign Administrative Group wizard opens.
  3. Click Create New Group adjacent to the Administrative Group drop-down list.
    The Create New Administrative Group window opens.
  4. In the Name box, type the name of the administrative group.
  5. In the Description box, type a description for the administrative group.
  6. Click Save.
    The administrative group created now appears in the Administrative Group drop-down list on the Assign Administrative Group wizard.
  7. From the Administrative Group drop-down list, select the new administrative group, and click Next.

Step 2: Create backup set for AG

Backup set allows you to define the backup content and schedule for backing up the databases of the AGs. Before creating a backup set, ensure that you have associated the AG(s) with the administrative group. For more information, see Attach server to an administrative group.

Procedure

  1. Define the content to back up your server.
    1. Under the Storage & Backup Content tab, provide the following information:
      Field Description
      Storage The Phoenix storage where the backed up data of the AG is stored.
      Time Zone

      The time zone of the primary server of the availability group in which Phoenix triggers the backups. 

      Note: Phoenix does not support backup of AGs for nodes in the different time zones.

      Select Content

       

      Under the Exclude database(s) box, enter database names that you want to exclude from the backup. Phoenix excludes databases whose names are a partial or an exact match with the strings that you have provided. The database names are case-sensitive. 
      You can exclude databases that are mirrored or encrypted or databases with an apostrophe or a comma in their names.

      Note: Databases that are not part of the AGs are auto-excluded from the AG backup.

    2. Click Next to attach the AG to a backup policy.
  2. Under the Backup Policy tab, you can attach the AG to an existing backup policy or create a new backup policy and attach it to the AG.
  • Attach AG to an existing backup policy.
  1. From the Backup Policy drop-down list, select the required backup policy to attach it to the AG.
  2. Under the Policy Details section, verify the configuration summary of the selected backup policy, and click Finish. 

Step 1: Provide backup policy details

  1. Under the Summary tab, provide the following information:
    • Name: The name of the backup policy that you want to create.
    • Description: A short description of the backup policy. 
  2. Click Next to define the backup schedule.

Step 2: Define a backup schedule

  1. Under the Backup Schedule tab, provide the following information:
    Field Description

    Backup Type

    Select Full backupDifferential backup, or Transaction Logs.  

    Start at

    The time when you want backups to start. In the adjacent box, select the AM or PM option depending on when you want backups to occur. 

    Duration (Hrs)

    The duration after which you want backup operations to stop. For example, if you set Start at to 5 AM and you set the duration to 5 hours, backups from your server start at 5 AM and stop at 10 AM, even if they do not complete. 

    Max Bandwidth (Mbps)

    The maximum bandwidth that each SQL servers can consume while backing up data to Phoenix. For scheduled backup, the job will consume the assigned bandwidth. However, for manually triggered backup, the job will consume the available bandwidth on your network.

    Repeat on

    The days on which you want backups to occur.

    For transaction log backups, you can specify an interval of 5, 10, 15, 30, 45, 60, or 120 minutes. Log backups are triggered as soon as a full or differential backup completes. 

  2. Click Add More to create more schedules.

  3. To add multiple schedules, repeat step a as many times as the number of schedules that you want to create. To delete a schedule, click the delete icon next to the schedule. If you have only one schedule defined, you cannot delete it.

  4. By default, the Ignore backup duration for first backup option is enabled. When this option is enabled, it ignores the specified backup duration for the first backup job. The first backup job is complete when the first restore point is created. You can disable this option to enforce backup duration for the first backup job. 

  5. By default, the Automatic Retry check box is enabled. If you do not want to define the automatic retrial options, clear the Automatic Retry check box.
    Provide the following details for automatic retrial options:
    Field Description
    Max number of retries In an active schedule window, the maximum number of retry attempts for a job. By default, the field is set to 2 attempts.
    Wait interval before each retry The wait interval between each retry. By default, the field is set to 10 minutes.

    Automatic retry is not applicable for transaction log backups. For more details, see Backup retry.

Note: The snapshot timestamps for backup sets of the AGs are displayed according to the time zone configured during creation of backup set.

  1. Click Next to define the retention period.

Step 3: Define retention period

  1. Under the Retention tab, provide the following information.
    Field Description
    All snapshots for The duration for which you want to retain all snapshots.
    Weekly revisions for The duration for which you want to retain weekly snapshots.
    Monthly revisions for The duration for which you want to retain monthly snapshots.
    Yearly revisions for The duration for which you want to retain yearly snapshots.

Note: Ensure that you enter a value in at least one of the fields. Phoenix treats the values in the empty fields as zero. 

  1. Click Finish to create the backup policy.
    The created backup policy is listed in the Backup Policy drop-down list. 

  2. From the Backup Policy list, select the created backup policy.
    Under the Policy Details section, verify the configuration summary of the new backup policy, and click Finish. 

The backup set created for the AG(s) will now appear under the SQL Backup Sets tab on the MS-SQL page. 

The agent backs up data from the AG based on the backup schedule and retains snapshots as defined in the retention period. 

Next steps

After the MS-SQL backup set is created, Phoenix backs up databases automatically. If a restore is required, you can trigger it from the Phoenix Management Console. For more information about restore jobs, see:

If you want to modify an administrative group, or backup policy, or upgrade the server, see:

If you have deployed CloudCache, you can attach backup sets to the CloudCache. For more information, see Attach a new backup set to CloudCache.