PowerProtect Data Manager 19.10 Microsoft SQL Server User Guide

Add a protection policy for Microsoft SQL Server database protection

Use the PowerProtect Data Manager UI to add a protection policy group for the purposes of Microsoft SQL Server database protection.

1. In the left navigation pane, select Protection > Protection Policies.
   The Protection Policies window appears.
2. Click Add.
   The Add Policy wizard appears.
3. On the Type page, specify the new protection policy group fields, and then click Next:

   • Name—Type a descriptive name for the protection policy, for example, SQL Prod Databases.

     NOTE The name that you specify becomes part of the DD MTree entry.
   • Description—Type a short description for the protection policy, for example, SQL Prod Daily Backups.
   • Type—Select Microsoft SQL.
4. On the Purpose page, select from the following options to indicate the purpose of the new protection policy group, and then click Next:

   • Centralized Protection—Select this option to use PowerProtect Data Manager to centrally manage all stages of the protection policy.

     Centralized protection means that PowerProtect Data Manager schedules the backups and manages the life cycle of the copies.

     Click Set Credentials to specify new credentials or select existing credentials from the list.

     NOTE

     The supported syntax for credentials is <user>@<host> or <user>@<domain>. The syntax <host>\<user> or <domain>\<user> is not supported.

     The host-level credentials take precedence over protection policy-level credentials.

   • Self-Service Protection—Select this option to use Microsoft SQL Server to create local backup protection. PowerProtect Data Manager creates a protection policy and manages extra stages.

     Self-service protection means that DBAs schedule the backups but PowerProtect Data Manager discovers and manages the life cycle of the copies.

   • Exclusion—Select this option when the protection policy contains Oracle assets that you want to exclude from data protection operations.
5. On the Assets page, select the assets for inclusion in this policy by using either the Microsoft SQL Server hierarchical view or the list view. To select the preferred view, click one of the two icons on the top right of the page. You can switch between the views by clicking the icons:
   NOTE A Microsoft SQL Server database asset can be protected by only one protection policy at a time.

   • Hierarchical view—This view uses a tree view that shows the hierarchical relationships of the Microsoft SQL Server hosts, their application servers or instances (including Failover Cluster Instances (FCIs)), stand-alone database assets, and any Always On availability groups (AAGs) with their database assets. When you expand the hierarchical or tree view, you can see all the assets and AAGs within a host and instance. When you select a host or instance container, all the contained assets and objects are also selected. You can also select individual assets or a group of assets within the host or instance container to include in the protection policy.

     NOTE

     For FCIs and AAGs, when you select the node of one host, the same selection automatically applies to the other hosts in the cluster.

     The hierarchical view is supported only for an Application Direct protection policy, not an application-aware protection policy.

     The hierarchical view is also available on the following pages in the PowerProtect Data Manager UI:

     • Protection > Protection Policies > Protect Now
     • Protection > Protection Policies > Edit Policy > Assets
     • Infrastructure > Assets
     • Restore > Assets

     When you select a host or instance within the hierarchical view, the Dynamic Protection and AAG Selection icons appear on the container line:

     NOTE If a selected instance is the only instance on a host, the icons appear on the host line above the instance, not on the instance line.

     • Dynamic Protection—When this icon is enabled (by default), a dynamic protection rule is automatically created to ensure that all the selected assets within the selected host or instance container are dynamically protected by the protection policy. PowerProtect Data Manager manages the protection rule. The rule is updated automatically when you edit the policy and make changes to the container selections, or when assets are moved into or out of a selected container.

       With dynamic protection, any new databases or assets that you add to the instance or AAG or container after the policy is created or edited will be automatically added to the protection policy. Any databases or assets that you remove from the instance or AAG or container are also removed from the policy. When any selection overlap occurs between different policies, the UI displays the overlaps and helps you to resolve the asset assignment conflicts by adjusting the protection rules' priority.

       To disable the dynamic protection for a container, click the Dynamic Protection icon and then click Disable in the displayed text box. When the dynamic protection is disabled, the protection policy does not dynamically protect the selected container and its objects. As a result, all the selected objects within the container become static selections that are not automatically protected by the policy.

       After you create or edit a protection policy that has dynamic protection, you can select Protection > Protection Rules to see the protection rule details for the protection policy, including the priority of the protection rule. Dynamic protection rules apply only at the container level.

     • AAG Selection—This icon appears when the selected host or instance includes one or more AAGs. By default, the icon is enabled and all the AAGs within the host or instance are selected. When you want to exclude all the contained AAGs from the protection policy, click the AAG Selection icon and then click Exclude in the displayed text box.

     In the hierarchical view, you might see a yellow warning icon in the Protection Policy column, next to a selected host, instance, database, or AAG. The warning icon indicates that one of the following issues exists. Hover over the warning icon to see the issue description:

     • When the Microsoft application agent version is earlier than 19.9, an AAG folder might display an incomplete hierarchical view of the data. To obtain a complete view of the data, update the Microsoft application agent.
     • An asset assignment conflict exists because the host, instance, or contained object is already protected by another protection policy. After you click Next on the Assets page and click OK on a notification page, the Check conflicts due to rule priority page appears, where you can adjust the protection rule priorities and resolve the conflicts.
   • List view—This view uses a table display that enables you to see all unprotected assets within a table, and then select individual unprotected assets that you want to include in the protection policy.
6. Click Next.

   If the Assets page included any asset assignment conflicts with other protection policies, a notification page appears that describes the assets that are already assigned to other protection policies. Click OK to continue or Cancel to return to the Assets page.

   When you click OK, the Check conflicts due to rule priority page appears, displaying the assets with conflicting assignments and their protection policies and rules. In the Protection Rules pane, you can click the up and down arrows to change the protection rule priority of any policy. When you raise the rule priority for a policy, the assets with conflicts in a lower-priority policy are moved to the policy with the higher protection rule priority.

7. Click Next.

   If you selected Exclusion on the Purpose page, the Summary page appears. Proceed to the final two steps.

   If you selected Centralized Protection or Self-Service Protection on the Purpose page, the Objectives page appears for creating the protection policy backup configuration.

8. On the Objectives page, select a policy-level Service Level Agreement (SLA) from the Set Policy Level SLA list, or select Add to open the Add Policy Service Level Agreement wizard and create a new policy-level SLA.
   Add a service-level agreement provides instructions.
9. Complete the steps for the specified type of protection policy group:

   • For Centralized Protection:

     1. Click Add under Primary Backup.

        The Add Primary Backup dialog appears.

     2. On the Target pane of the Add Primary Backup dialog, specify the following fields:

        • Storage Name—Select a backup destination from the list of existing DD systems, or select Add to add a system and complete the details in the Storage Target dialog.

        • Storage Unit—Select whether this protection policy should use a New storage unit on the selected DD system, or select an existing storage unit from the list. Hover over a storage unit to view the full name and statistics for available capacity and total capacity, for example, testvmpolicy-ppdm-daily-123ab (300 GB/1 TB).

          When you select New, a new storage unit in the format policy name hostname unique identifier is created in the storage system upon policy completion, for example, testvmpolicy-ppdm-daily-123cd.

          NOTE The Space field indicates the total amount of space, and the percentage of available space, on the storage system.
        • Network Interface—Select a network interface from the list, if applicable.

        • Retention Lock—Move the Retention Lock slider to the right to enable retention locking for these backups on the selected system. PowerProtect Data Manager uses Governance mode for retention locking, which means that the lock can be reverted at any time if necessary. Toggling the Retention Lock slider on or off applies to the current backup copy only, and does not impact the retention lock setting for existing backup copies.

          NOTE Primary backups are assigned a default retention lock period of 14 days. Replicated backups, however, are not assigned a default retention lock period. If you enable Retention Lock for a replicated backup, ensure that you set the Retain for field in the Add Replication dialog to a minimum number of 14 days so that the replicated backup does not expire before the primary backup.

        • SLA—Select an existing service level agreement that you want to apply to this schedule from the list, or select Add to create an SLA within the Add Backup Service Level Agreement wizard.

          Add a service-level agreement provides instructions.

     3. On the Schedules pane of the Add Primary Backup dialog:

        1. Specify the following fields to schedule the full backup of this protection policy:

           • Create a Full backup every—Specify how often to create a full backup.

           • Retain for—Specify the retention period for the backup.

             You can extend the retention period for the latest primary backup copy by using the Extend Retention schedule. For example, your regular schedule for daily backups can use a retention period of 30 days, but you can apply extended retention to keep the full backups taken on Mondays for 10 weeks. Step 9 provides instructions.

             NOTE For database backups, PowerProtect Data Manager chains the dependent backups together. For example, the incremental or transaction log backups are chained to their base full backup. The backups do not expire until the last backup in the chain expires. This ensures that all incremental and transaction log backups are recoverable until they have all expired.

           • Start and End—The activity window. Specify a time of day to start the full backup, and a time of day after which backups cannot be started.

             NOTE Any backups started before the End time occurs continue until completion.

        2. Click Add backup if you want to add an incremental differential or log backup, and then specify the following fields to schedule the backup of this protection policy:

           NOTE When you select this option, the backup chain is reset.

           • Create a <backup_type> backup every—For <backup_type>, select Differential or Log from the drop-down list, and then specify the interval at which the backup job runs within the window that you specify. The backup interval depends on the backup interval of the full backup schedule:

             • If the full backup schedule is hourly or daily, the backup interval of subsequent backup levels can be between 1 and 12 hours or between 1 and 60 minutes.
             • If the full backup schedule is weekly or monthly, the backup interval of subsequent backup levels can be daily, between 1 and 12 hours, or between 1 and 60 minutes.

           • Retain for—Specify the retention period for the backup.

             CAUTION If you set a shorter retention period for a differential or log backup than for the corresponding full backup, then data loss might occur and you might be unable to recover the point-in-time copies.

           • Start and End—The activity window. Specify a time of day to start the backup, and a time of day after which backups cannot be started.

             NOTE Any backups started before the End Time occurs continue until completion.

     4. Click Save to save the changes and return to the Objectives page.

        The Objectives page updates to display the name and location of the target storage system under Primary Backup.

        NOTE

        After completing the backup schedule, you can change any schedule details by clicking Edit under Primary Backup.

        When a new asset is added to a protection policy, the asset is not protected until the next full backup runs, whether or not the asset is added within the backup schedule window. To immediately start protecting the asset, run a manual full backup of the entire policy from the policy page or the newly added asset from the assets page.

   • For Self-Service Protection:

     1. Click Add under Primary Retention.

        The Add Primary Retention dialog appears.

     2. On the Target pane of the Add Primary Retention dialog, specify the following fields:

        • Storage Name—Select a backup destination from the list of existing DD systems, or select Add to add a system and complete the details in the Storage Target dialog.

        • Storage Unit—Select whether this protection policy should use a New storage unit on the selected DD system, or select an existing storage unit from the list. Hover over a storage unit to view the full name and statistics for available capacity and total capacity, for example, testvmpolicy-ppdm-daily-123ab (300 GB/1 TB).

          When you select New, a new storage unit in the format policy name hostname unique identifier is created in the storage system upon policy completion, for example, testvmpolicy-ppdm-daily-123cd.

          NOTE The Space field indicates the total amount of space, and the percentage of available space, on the storage system.
        • Network Interface—Select a network interface from the list, if applicable.

        • Retention Lock—Move the Retention Lock slider to the right to enable retention locking for these backups on the selected system. PowerProtect Data Manager uses Governance mode for retention locking, which means that the lock can be reverted at any time if necessary. Toggling the Retention Lock slider on or off applies to the current backup copy only, and does not impact the retention lock setting for existing backup copies.

          NOTE Primary backups are assigned a default retention lock period of 14 days. Replicated backups, however, are not assigned a default retention lock period. If you enable Retention Lock for a replicated backup, ensure that you set the Retain for field in the Add Replication dialog to a minimum number of 14 days so that the replicated backup does not expire before the primary backup.

        • SLA—Select an existing service level agreement that you want to apply to this schedule from the list, or select Add to create an SLA within the Add Backup Service Level Agreement wizard.

          Add a service-level agreement provides instructions.

     3. On the Retention (Self Service) pane of the Add Primary Retention dialog, change any required retention times.

        By default, all backup types have the same retention time. To change the retention times for specific backup types, clear Set the same retention time for all backup types and change the Retain <backup_type> For field values as required.

        CAUTION If you set a shorter retention period for a differential or log backup than for the corresponding full backup, then data loss might occur and you might be unable to recover the point-in-time copies.

     4. Click Save to save the changes and return to the Objectives page.

        The Objectives page updates to display the name and location of the target storage system under Primary Retention.

        NOTE After completing the backup retention setting, you can change the setting by clicking Edit under Primary Retention.
10. Optionally, extend the retention period for the latest primary backup copy:
    Extended retention provides more information.
    1. Click Extend Retention next to Primary Backup or Primary Retention. An entry for Extend Retention is created below Primary Backup or Primary Retention.
    2. Under Extend Retention, click Add. The Add Extended Retention dialog appears.
    3. Retain the next scheduled full copy every—Specify the preferred recurrence for the extended retention backup schedule.
    4. Repeat on—Depending on the frequency of the backup schedule, specify the day of the week, the date of the month, or the date of the year that the extended retention backup will occur.
    5. Retain for—Specify the retention period for the backup. You can retain an extended retention backup for a maximum of 70 years.
    6. Click Save to save your changes and return to the Objectives page.
11. Optionally, replicate the backups to a remote storage system:
    1. Click Replicate next to Primary Backup, Primary Retention, or Extend Retention. An entry for Replicate is created to the right of the primary or extended retention backup schedule.
       NOTE PowerProtect Data Manager supports replicating an extended retention backup only if the primary backup already has one or more replication stages. Also, for replication of an extended retention backup, you can only select the DDs that are used by the replication stages based on the primary stage.

       For example, if there are 6 DDs available (DD001-DD006), and the primary backup is on DD0001:

       • Replicate1 based on the primary backup is replicated to DD002
       • Replicate2 based on the primary backup is replicated to DD003
       • Extended retention backup is backed up to DD001
       • Replicate3 based on the extended retention backup must be replicated to DD002 or DD003.
    2. Under Replicate, click Add. The Add Replication dialog appears.
       NOTE To enable replication, ensure that you add a remote DD system as the replication location. Add protection storage provides detailed instructions about adding a remote DD system.
    3. Complete the schedule details in the Add Replication dialog.

       By default, all replicated copies have the same retention time. To change the retention times for specific replicated copies, clear Set the same retention time for all replicated copies and click Edit to change the Retain for field values as required.

       For the replication of centralized policy backups, when you set the retention time for different backup types, any undefined backup types will use the full backup retention time. For example, when you do not define a log backup for a centralized primary backup, a log backup is also undefined for the replicate stage. After you run an ad hoc log backup, the replication of the log backup uses the same retention time as the full backup.

       CAUTION If you set a shorter retention period for a differential or log backup than for the corresponding full backup, then data loss might occur and you might be unable to recover the point-in-time copies.

       Click Save to save the changes and return to the Objectives page.

       NOTE The schedule frequency can be every day, week, month, or x hours for replication of the primary backup, and every day, week, month, year, or x hours for replication of the extended retention backup. For daily, weekly, and monthly schedules, the numeric value cannot be modified. For hourly, however, you can edit the numeric value. For example, if you set Create a Full backup every 4 hours, you can set a value of anywhere 1 to 12 hours.
12. Optionally, add a cloud stage for a primary, replication, or extended retention backup schedule for the purpose of moving backups from DD storage to cloud tier:
    1. Click Cloud Tier next to Primary Backup or Extend Retention or, if adding a cloud stage for a replication schedule that you have added, click Cloud Tier under Replicate. An entry for Cloud Tier is created to the right of the primary or extended retention backup schedule, or below the replication schedule.
    2. Under the entry for Cloud Tier, click Add.
       The Add Cloud Tier Backup dialog appears, with summary schedule information for the parent node to indicate whether you are adding this cloud tier stage for the primary backup schedule, the extended retention backup schedule, or the replication schedule.
    3. Complete the schedule details in the Add Cloud Tier Backup dialog, and then click Save to save your changes and return to the Objectives page.
       Add a cloud tier schedule to a protection policy provides detailed instructions for adding a cloud stage for a primary, replication, or extended retention backup schedule.
    NOTE In order to move a backup or replica to cloud tier, schedules must have a retention time of 14 days or more. Also, discovery of a DD system configured with a cloud unit is required.
13. Click Next.
    The Options page appears.
14. On the Options page, select the additional options that are required for the policy:
    NOTE If the PowerProtect Data Manager version is 19.6 but the application agent version is earlier than 19.6, then the Backup Promotion and Exclude Simple Database options are not supported, although the options appear in the PowerProtect Data Manager UI.

    • Exclude Simple Database—Select this option to exclude the databases in simple recovery model from the transaction log backups.
    • Exclude System Databases—Select this option to exclude the Microsoft SQL Server system databases (including databases named master, model, and msdb) from the differential and transaction log backups.
    • Exclude Unprotectable Database—Select this option to exclude the databases in an unprotectable state from all backups.

    • Backup Promotion—Select one of the following backup promotion options to use for differential and transaction log backups:

      • ALL—Enables backup promotion. This setting is the default backup promotion setting.
      • NONE—Disables backup promotion, without displaying a warning during backups.
      • NONE_WITH_WARNINGS—Disables backup promotion, but displays a warning during a backup when a backup promotion would normally occur.
    • Troubleshooting—Select this option to enable the debug logs for troubleshooting purposes.
15. Click Next.
    The Summary page appears.
16. Review the protection policy group configuration details. You can click Edit next to any completed window's details to change any information. When completed, click Finish.
    An informational message appears to confirm that PowerProtect Data Manager has saved the protection policy. When a new protection policy is created, PowerProtect Data Manager performs the first full backup and subsequent backups according to the specified schedule.
17. Click OK to exit the window, or click Go to Jobs to open the Jobs window to monitor the backup of the new protection policy group.

    You can monitor and view detailed information in the Jobs window for both centralized and self-service backups and restores of database application agents.

    NOTE The Cancel and Retry options are not available for self-service jobs that are created by database application agents.