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 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.
      Backup Settings.png

      Select an administrative group from the drop-down next to 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 from the drop-down, 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 Administrative Group drop-down.

    2. In the Backup Settings section, perform the following tasks:
      New Backup Policy.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 from the drop-down 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:
    Step 6.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. The Configuration Pre-Checks screen is displayed with the following validations:
    Config Prechecks.png

    To prevent any future failures in your backups due to misconfigured settings, Druva Phoenix broadly performs these validations while creating a backup set:

    Field Descriptions
    Client activation status This validation ensures that the Druva Phoenix agent is installed and activated on the SQL server.
    Client connection status This validation ensures that the client is in the connected state i.e the Druva Phoenix Agent Client Service on the SQL-Server is in a running state.
    Sysadmin privileges for the user account Transaction Log and VDI differential backups require the assigned SQL server user login to have the ‘Sysadmin’ role. This validation ensures that the same is provided.
    Database mirroring check Phoenix supports backup of mirrored databases with some limitations. For more information, see System requirements for MS-SQL servers.
    Database recovery model check Databases in Simple recovery model do not support Transaction Log backups. 
    If the backup set is configured for Transaction Log backups, this validation ensures that at least one of the selected databases is in the Full recovery model.

    If a check to create a backup set fails, then fix the issue and re-run the checks. If all the checks are successful, then the Summary screen is displayed.
    If a check fails and you still want to proceed, then click Proceed Anyway.

  3. In the Summary screen, verify the backup set configuration settings and the databases selected for backup and click Finish.
    Summary tab.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.png
 

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

    1. The Create 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 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 from the drop-down and enter the following details:
      Backup Settings.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 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 from the drop-down 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:
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 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. The Configuration Pre-Checks window is displayed with the following validations:
    Prechecks.png

    To prevent any future failures in your backups due to misconfigured settings, Druva Phoenix performs these validations while creating a backup set:
    Field Descriptions
    Client activation status This validation ensures that the Druva Phoenix agent is installed and activated on all nodes of the Availability Group.
    Client connection status This validation ensures that the client is in the connected state i.e the Druva Phoenix Agent Client Service on the SQL-Server is in a running state.
    Sysadmin privileges for the user account Transaction Log and VDI differential backups require the assigned SQL server user login to have the ‘Sysadmin’ role. This validation ensures that the same is provided.
    If a check to create a backup set fails, then fix the issue and re-run the checks. If all the checks are successful, then the Summary screen is displayed.
    If a check fails and you still want to proceed, then click Proceed Anyway.
  3. In the Summary screen, verify the backup set configuration settings and the databases selected for backup and click Finish.
    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 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.