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 multiple  SQL resources 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 resource you want to configure that has 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
    You can select only one resource at a time to create a backup set.

    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 Backup Content tab, provide the following information:
      create_sql_backup_set.png
      Field Description

      Select databases

       

      Under the Database Name box, select the databases that you want to include in this backup set. You can use the search box to filter the databases running on the instance. The database search on this window is case-insensitive. If the database name is asdb and you enter ASDB, Phoenix will search and list asdb as a choice. By default, all the databases are selected for backup. 

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

      Include: Database name or substring Enter a database name or sub-string to include the database in the backup set. For example, there are three databases on the instance with names as HREmployees, FinanceEmployees, and EngineeringEmployees.  Adding the pattern Emp includes all the three databases in the backup set.

      Note: The text inputs in this field are case sensitive. If you enter Emp, Phoenix includes databases that have the substring in their names and if the substring matches the case of the text that you enter. For example, Phoenix includes the FinanceEmployees database, but Phoenix skips including the database exemployees in the backup set. You can enter more than one substrings.
      Exclude: Database name or substring Enter a database name or sub-string to exclude the database from the backup set. For example, there are two databases on the instance with names as FormerSystems and FormerSoftware.  Adding the substring Former in this list excludes both the databases from the backup set. 

      Note: The text inputs in this field are case sensitive. If you enter Former, Phoenix includes databases that have the substring in their names and the if the substring matches the case of the text that you enter. For example, Phoenix excludes the FormerSystems database, but Phoenix skips excluding the database formerproducts from the backup set. You can enter more than one substrings.

      The exclusion filter takes precedence over the inclusion filter. For example, there is a database on the instance called FormerEmployees. The FormerEmployees database should be included based on the inclusion filter, however, it should be excluded based on the exclusion filter. In such a case, Phoenix excludes the database from the backup set. 
    2. Click Next to attach storage and a backup policy to the MS-SQL server backup set.
  6. Under the Storage & Backup Policy tab, attach storage and backup policy to the MS-SQL server backup set. 
    From the Storage drop-down list, select the required storage.
    From the Backup Policy drop-down list, select the required backup policy for the backup set and then click Next. 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 the 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. Verify the configuration summary and the databases selected for backup, 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. 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 the resource that has the resource type as Availability Group and then click Add New Backup Set. The Assign Administrative Group wizard opens.
    Note
    You can select only one resource at a time to create a backup set.

    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 to back up the databases of the availability group.
    1. Under the Backup Content tab, provide the following information:
      create_sql_backup_set.png
      Field Description

      Select databases

       

      Under the Database Name box, select the databases that you want to include in this backup set. You can use the search box to filter the databases running on the availability group. The database search on this window is case-insensitive. If the database name is asdb and you enter ASDB, Phoenix will search and list asdb as a choice.  By default, all the databases are selected for backup.

      Include: Database name or pattern Enter a database name or pattern to include the database in the backup set. For example, there are three databases on the availability group with names as HREmployees, FinanceEmployees, and EngineeringEmployees.  Adding the pattern Emp includes all the three databases in the backup set. 
      Exclude: Database name or pattern Enter a database name or sub-string to exclude the database from the backup set. For example, there are two databases on the instance with names as FormerSystems and FormerSoftware.  Adding the substring Former in this list excludes both the databases from the backup set. 

      The exclusion filter takes precedence over the inclusion filter. For example, there is a database on the instance called FormerEmployees. The FormerEmployees database should be included based on the inclusion filter, however, it should be excluded based on the exclusion filter. In such a case, Phoenix excludes the database from the backup set. 
      Note: The text inputs to the include databases filter and exclude databases filter fields are case sensitive. You can enter more than one substrings.
    2. Click Next to attach storage and a backup policy to the MS-SQL server backup set.
  6. Under the Storage & Backup Policy tab, attach storage and backup policy to the MS-SQL server backup set. 
    From the Storage drop-down list, select the required storage.
    From the Backup Policy drop-down list, select the required backup policy for the backup set and then click Next. 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 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 checkbox 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. 

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.

Note: If you add a database to the SQL Server, ensure that you use the include field in the backup sets with appropriate substrings added so that Phoenix can detect the newly added database and attach it to a backup set automatically. The file and folder backup set that you create for the Windows Server will not be listed in the instance details page.

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.