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 All SQL Resources 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 standalone instance for backup

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

The following steps describe the procedure to configure MS-SQL server databases on a standalone instance for backup

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

  • Step 2: Create backup set for MS-SQL instance
    • Select the content to back up.
    • Attach a backup policy to the server.

To configure databases on an MS-SQL server standalone instance for backup

  1. Log in to Phoenix Management Console, select the organization from the menu bar, and then click Protect MS-SQL Servers.
  2. Under the All SQL Resources tab on the MS-SQL page, select the resources you want to configure that have the resource type as Standalone Instance and then click Create SQL Backup Set. The Create SQL Backup Set: Assign Administrative Group wizard appears.
    SQL_instance_Attach_AdminGroup.PNG
    Note: Phoenix displays the Create SQL Backup Set: Assign Administrative Group page only if any instances associated with the servers are not mapped to any administrative group.
  3. From the Administrative Group drop-down list,  either select the required administrative group or click Create New Group. Phoenix provides the Default Administrative Group without any servers attached to the group. If you click Create New Group, provide the following details:
    1.  The Create New Administrative Group window opens.
    2. In the Name box, type the name of the administrative group.
    3. In the Description box, type a description for the administrative group.
    4. 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.
  4. After you select an administrative group for the server, click Next: Create Backup Set.
  5. 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 a backup policy to the MS-SQL server.
  6. 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. From the Backup Policy drop-down list, select the required backup policy to attach it to the instance and click Next or click Create New Backup Policy. If you select an existing policy, skip the following information and go to step 7. If you click Create New Backup Policy, provide the following 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.
    3. 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.

    4. Click Add More to create more schedules.
    5. 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.
    6. 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.
    7. 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.
    8. Click Next to define the retention period.
    9. 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. 
    10. Click Finish to create the backup policy. The created backup policy is listed in the Backup Policy drop-down list.
    11. From the Backup Policy drop-down list, select the required backup policy to attach it to the instance and click Next.
  7. Under the Policy Details section, verify the configuration summary of the selected backup policy, and click Finish.
  8. The agent backs up data from the instance based on the backup schedule and retains snapshots as defined in the retention period. The instance configured for backup now appears under the SQL Backup Sets tab on the MS-SQL page.

Configure MS-SQL availability group (AG) for backup

Before you configure MS-SQL availability group (AG) 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 a 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 the 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 the 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.

The following steps describe the procedure to configure MS-SQL server databases on an AG for backup

  • 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.
  • Step 2: Create backup set for AG
    • Select the content to back up.
    • Attach the server to a backup policy.

To configure databases on an MS-SQL server AG for backup

  1. Log in to the Phoenix Management Console, select the organization from the menu bar, and then click Protect MS-SQL Servers.
  2. Under the All SQL Resources tab on the MS-SQL page, select resources that have the resource type as Availability Group 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. If you click Create New Group, provide the following information:
    1. Click Create New Group adjacent to the Administrative Group drop-down list.
      The Create New Administrative Group window opens.
    2. In the Name box, type the name of the administrative group.
    3. In the Description box, type a description for the administrative group.
    4. Click Save. The administrative group created now appears in the Administrative Group drop-down list on the Assign Administrative Group wizard.
  4. From the Administrative Group drop-down list, select the required administrative group, and click Next to define the content for backup.
  5. Define the content for backup.
    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.
  6. 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. Either select the required backup policy to attach it to the AG from the Backup Policy drop-down list and click Next, or click Create New Backup Policy. If you choose an existing backup policy, skip the following information and go to step 7. If you click Create New Backup Policy, provide the following information: 
    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.
    3. 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. 

    4. Click Add More to create more schedules.

    5. 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.
    6. 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. 
    7. 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.
    8. Click Next to define the retention period.
    9. 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.
    10. Click Finish to create the backup policy. The created backup policy is listed in the Backup Policy drop-down list.
      Note: Ensure that you enter a value in at least one of the fields. Phoenix treats the values in the empty fields as zero. 
    11. From the Backup Policy drop-down list, select the required backup policy to attach it to the instance and click Next.
  7. Under the Policy Details section, verify the configuration summary of the selected 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.  

Note: The file and folder backup set that you create for the Windows Server will not be listed in the Instance Details page.

Next steps

A new backup set of type MS-SQL is created for the configured server. 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:

Similarly, you can add file backup sets to the server. For more information about adding a file backup set to a Windows Server, see Add file backup set to the configured server. If you have deployed CloudCache, you can attach backup sets to the CloudCache. For more information, see Attach a new backup set to CloudCache.