Skip to main content

How can we help you?

Druva Documentation

Configure MS-SQL instance or AG for backup

 

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

In Phoenix agent versions 4.7.1 and later, after registering an MS-SQL server, the Phoenix agent discovers standalone instances and availability groups (AG) running on the server. Druva Phoenix lists these instances and AGs under the All SQL Resources page under Protect > MS-SQL servers.  In Phoenix agent versions 4.8.5 and later, right after registering an MS-SQL server, Druva Phoenix initiates a database discovery. If the database discovery fails to find any databases on the instance or AG, you cannot create backup sets for that instance or AG. 

To back up MS-SQL databases in instances or AGs, configure the instances or AGs by creating MS-SQL backup sets.

This topic explains how to:

Note: You cannot select multiple  SQL resources to create an MS-SQL backup set. 

Configure MS-SQL standalone instance for backup

The following steps describe the procedure to configure MS-SQL server databases on a standalone instance for backup. Druva Phoenix excludes AG databases from  backups of standalone instances. 

  1. Log in to the Phoenix Management Console.

  2. On the menu bar, click the drop-down next to All Organizations and select the organization which has your MS-SQL server instance.

  3. On the menu bar, click Protect > MS-SQL servers.

  4. On the All SQL Resources page, select the standalone instance to be backed up, and click Create Backup Set.

Note: You can only select one resource at a time to create a backup set.


Create Backup Set highlight.png

  1. In the Backup Settings page, perform the following tasks:

    1. The Create SQL Backup Set page displays the option to select or create an administrative group if no instances or availability groups on this SQL server have been associated with any administrative group.

      Create Backup Set - Backup Settings.png

      Select an administrative group from the drop-down next to Select Administrative Group. Druva Phoenix gives you a Default Administrative Group that you can select. This group is not associated with any servers by default. You can also choose to create a new administrative group. To create a new administrative group, click New Administrative Group, and enter the following details:

      Create Administrative Group.png

      1. Name: Enter the name of the administrative group.

      2. Description: Enter a description of the administrative group. This is an optional field.
        Click Save. The newly created administrative group is now selected under the Select Administrative Group drop-down.

    2. In the Backup Settings section, perform the following tasks:
      Backup Settings section.png

      1. Enter a backup set name.
      2. Select a storage.
        Note: Storage once assigned cannot be changed later.

      3. Select a backup policy. You can click View Policy to view details of a selected policy. You can also click New Backup Policy to create a new backup policy. For more information on creating a new backup policy, see Create an MS-SQL server backup policy.

      4. Click Next.

  2. In the Backup Content page, perform the following tasks:

    Create Backup Set - Backup Content.png
     

Field Description
Database Selection

Under the Database Name column, select the databases that you want to backup. All the databases are selected by default. You can also search for databases on this instance using the Search field. The database search is case-insensitive. If the database name is empdb and you enter EmpDb, Druva Phoenix will search and list empdb as a choice.

Note:

  • Databases that are part of an AG are auto-excluded from the standalone instance backup. 
  • Databases that have already been configured for backup in other backup sets of the standalone instance are greyed out and cannot be selected.
Database Selection Rule
Include Patterns

Enter a database name or substring to include the database in the backup set. Selecting the Include all unconfigured and future databases checkbox includes all unconfigured databases that currently exist on the instance and automatically includes those that will be added in the future. If you select the  Include all unconfigured and future databases checkbox, you need not select any databases manually.  

Druva Phoenix does not support patterns like Emp*, *Emp or *Emp*

For example, the pattern Emp includes all databases with the substring Emp in their names. If there are three databases on the instance with the names HREmployees, FinanceEmployees, and EngineeringEmployees, adding the pattern Emp includes all the three databases in the backup set.

The include patterns are case-sensitive. The pattern Emp will include ITEmployees but exclude the database Exemployees in the backup. Druva Phoenix only includes those databases that have the specified case-sensitive string in their database names. You can enter more than one substring.

Exclude Patterns

Enter a database name or sub-string to exclude the database(s)  from the backup set. For example, if there are two databases on the instance with names FormerSystems and FormerSoftware, adding the substring Former in this field excludes both the databases from the backup set.

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

The exclusion filter takes precedence over the inclusion filter. For example, consider a database on the instance called FormerEmployees. You enter the pattern Former in both the Include and Exclude Pattern fields.  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, Druva Phoenix excludes the database from the backup set.

Note: Druva Phoenix automatically includes or excludes any databases added in the future that match the Include or Exclude patterns in the backup set.
Note: Databases that match the Exclude Pattern are greyed out and disabled in the Database Selection. Databases that match the Include Pattern are selected and disabled in the Database Selection.

  1. Click Next.

  2. In the Summary screen, verify the backup set configuration settings and the databases selected for backup and click Finish.

    Create Backup Set - Summary.png

Note: If no databases are manually selected or match the Include or Exclude Patterns, the backup of the backup set is skipped. If a database discovery that runs every 24 hours discovers any databases during a subsequent cycle, the backup is triggered as scheduled in the backup policy.

The Phoenix 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 All SQL Resources page under Protect > MS-SQL servers.

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, Druva 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, Druva 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.

Procedure

  1. Log in to the Phoenix Management Console.

  2. On the menu bar, click the drop-down next to All Organizations and select the organization which has your MS-SQL server availability group.

  3. On the menu bar, click Protect > MS-SQL servers.

  4. On the All SQL Resources page, select the availability group to be backed up, and click Create Backup Set.

Note: You can only select one availability group at a time to create a backup set.

Create Backup Set highlight - Availability Group.png

  1. In the Backup Settings page, perform the following tasks:

    1. The Create SQL Backup Set page displays the option to select or create an administrative group if this availability group has not been associated with any administrative group. Select an administrative group from the drop-down next to Select Administrative Group. Druva Phoenix gives you a Default Administrative Group that you can select. This group is not associated with any servers by default. You can also choose to create a new administrative group. To create a new administrative group, click New Administrative Group and enter the following details:

      Create Backup Set - Backup Settings - Availability Group.png

      1. Name: Type the name of the administrative group.

      2. Description: Enter a description of the administrative group. This is an optional field.
        Click Save. The newly created administrative group is now selected under the Select Administrative Group drop-down.

    2. In the Backup Settings section, perform the following tasks:
      1. Backup Set Name:  Enter a backup set name.
      2. Storage: Select a storage.

Note: Storage once assigned cannot be changed later.

  1. Timezone: The timezone in which the backups must run. For example, if your timezone is US/Pacific, and you’ve scheduled Full backups for 5 AM every Sunday, the Full backup will run at 5 AM Pacific time in the US.

Note:  Timezone once assigned cannot be changed.

  1. Backup Policy: Select a backup policy. You can view details of a selected policy. You can also click New Backup Policy to create a new backup policy. For more information on creating a new backup policy, see Create an MS-SQL server backup policy.
  2. Click Next.

6. In the Backup Content page, perform the following tasks:

Create Backup Set - Backup Content - Availability Group.png

Field Description
Database Selection

Under the Database Name column, select the databases that you want to backup. All the databases are selected by default. You can also search for databases on the availability group using the Search field. The database search is case-insensitive. If the database name is empdb and you enter EmpDb, Druva Phoenix will search and list empdb as a choice.

Note: Databases that have already been configured for backup in other backup sets of the availability group are greyed out and cannot be selected.

Database Selection Rule
Include Patterns

Enter a database name or substring to include the database in the backup set. Selecting the Include all unconfigured and future databases checkbox includes all unconfigured databases that currently exist on the instance and automatically includes those that will be added in the future. If you select the  Include all unconfigured and future databases checkbox, you need not select any databases manually.

Druva Phoenix does not support patterns like Emp*, *Emp or *Emp*

For example, the pattern Emp includes all databases with the substring Emp in their names. If there are three databases on the instance with the names HREmployees, FinanceEmployees, and EngineeringEmployees, adding the pattern Emp includes all the three databases in the backup set.

The include patterns are case-sensitive. The pattern Emp will include ITEmployees but exclude the database Exemployees in the backup. Druva Phoenix only includes those databases that have the specified case-sensitive string in their database names. You can enter more than one substring.

Exclude Patterns

Enter a database name or sub-string to exclude the database(s)  from the backup set. For example, if there are two databases on the instance with names FormerSystems and FormerSoftware, adding the substring Former in this field excludes both the databases from the backup set. 

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

The exclusion filter takes precedence over the inclusion filter. For example, consider a database on the instance called FormerEmployees. You enter the pattern Former in both the Include and Exclude Pattern fields.  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, Druva Phoenix excludes the database from the backup set.

 

 

Note:

  • Druva Phoenix automatically includes or excludes any databases added in the future that match the Include or Exclude patterns in the backup set.
  • Databases that match the Exclude Pattern are greyed out and disabled in the Database Selection. Databases that match the Include Pattern are selected and disabled in the Database Selection
  1. Click Next.
  2. In the Summary screen, verify the backup set configuration settings and the databases selected for backup and click Finish.

    Create Backup Set - Summary - Availability Group.png

Note: If no databases are manually selected or match the Include or Exclude Patterns, the backup of the backup set is skipped. If a database discovery that runs every 24 hours discovers any databases during a subsequent cycle, the backup is triggered as scheduled in the backup policy

The Phoenix agent backs up data from the availability group based on the backup schedule and retains snapshots as defined in the retention period. The availability group configured for backup now appears under the All SQL Resources page under Protect > MS-SQL servers.

Next steps

The SQL Backup Sets page shows the backup sets for the Standalone Instance and Availability Groups. Druva Phoenix backs up the databases on the configured standalone instances and availability groups as per the configured schedule.

Note: After adding a database to an MS-SQL server, ensure that you update the Include Patterns in the respective backup set with the appropriate substrings or manually include the database for backup.

 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.