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.
Prerequisites
Before you perform a backup on a weekly or monthly schedule from the protection policy, ensure that the
PowerProtect Data Manager time zone is set to the local time zone. If the
PowerProtect Data Manager time zone is not set to the local time zone, the weekly or monthly backup still runs but is triggered based on the
PowerProtect Data Manager time zone.
If applicable, complete all of the virtual network configuration tasks before you assign any virtual networks to the protection policy. The
PowerProtect Data Manager Administration and User Guide provides more information.
NOTE The option to create a storage unit during protection policy configuration does not support compliance mode retention locking, only governance mode. To use compliance mode retention locking, create and configure a storage unit before you configure an associated protection policy. If you enable retention locking and select a storage unit where the retention lock mode is
None, the retention lock defaults to governance mode. The
PowerProtect Data Manager Administration and User Guide provides more information.
About this task
NOTE If a database is protected in an Always On availability group, you cannot configure stand-alone backups of that database in a protection policy group.
Steps
From the left navigation pane, select
Protection > Protection Policies.
The
Protection Policies window appears.
In the
Protection Policies window, click
Add.
The
Add Policy wizard appears.
On the
Type page, specify the following fields, and then click
Next:
Name—Type a descriptive name for the protection policy.
Description—Type a description for the policy.
Type—Select
Microsoft SQL.
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
objectives of the protection policy. This option also enables the self-service operations.
Centralized protection means that
PowerProtect Data Manager schedules the backups and manages the life cycle of the copies. DBAs can also schedule the self-service backups when required.
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
objectives.
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 type if there are assets within the protection policy that you plan to exclude from data protection operations.
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:
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.
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.
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.
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.
Complete the steps for the specified type of protection policy group:
For
Centralized Protection:
Click
Add under
Primary Backup.
The
Add Primary Backup dialog appears.
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 namehostnameunique 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.
The retention lock mode setting comes from the configuration of the selected storage unit. When you enable retention locking, the
Retention Lock Mode field displays the corresponding storage unit setting.
Setting a retention lock 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.
On the
Schedules pane of the
Add Primary Backup dialog:
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.
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.
Click
Add backup if you want to add a 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.
Click
Add Backup and repeat the procedure for creating full backups if you want to create additional backup copies at different intervals with different retention periods.
Within this protection policy, when a full schedule conflicts with another full backup schedule, a message appears, indicating that there is a conflict. Schedule occurrences can conflict with each other when the activity windows are identical or occur entirely within the same time range. To avoid full schedule conflicts in a policy, edit the activity windows. If you proceed with conflicting schedules, the backup of the lower priority schedule will be skipped. Schedule priority is ranked according to the following criteria:
Full schedules have a higher priority than Synthetic Full schedules.
For schedules of the same backup type, the schedules that run less frequently have a higher priority than schedules that run more frequently.
For schedules with the same backup type and frequency, the schedule with the longest activity window has the higher priority. If the activity windows are also identical, only one of these schedules will run.
NOTE When a schedule conflict between full backups occurs,
PowerProtect Data Manager retains the full backup with the longest retention period.
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.
After completing the
objective, you can change any details by clicking
Edit next to the
objective.
NOTE 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:
Click
Add under
Primary Retention.
The
Add Primary Retention dialog appears.
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 namehostnameunique 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.
The retention lock mode setting comes from the configuration of the selected storage unit. When you enable retention locking, the
Retention Lock Mode field displays the corresponding storage unit setting.
Setting a retention lock 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.
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.
When a different retention time for all backup types is set, you can create additional full backup patterns with different retention times. For example, you can add a full backup pattern
Retain full backups created every week on the Monday and Tuesday for 2 months.
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.
The
Objectives page updates to display the name and location of the target storage system under
Primary Retention.
After completing the
objective, you can change any details by clicking
Edit next to the
objective.
When a different retention time for all backup types is set, you can create additional full backup patterns with different retention times. For example, you can add a full backup pattern
Retain full backups created every week on the Monday and Tuesday for 2 months.
Optionally, replicate the backups:
NOTE
To enable replication, ensure that you add remote
protection storage as the replication location. The
PowerProtect Data Manager Administration and User Guide provides detailed instructions about adding remote
protection storage.
When creating multiple replicas for the same protection policy, it is recommended to select a different storage system for each copy. If you select a storage unit that is the target of another
objective for the same policy, the UI issues a warning. The
PowerProtect Data Manager Administration and User Guide provides information about replicating to shared
protection storage to support
PowerProtect Cyber Recovery. Verify the storage targets and the use case before you continue.
When you create a replication objective, you can specify either scheduled replication or replication after backup completion.
NOTE For replication after backup completion,
PowerProtect Data Manager 19.12 or later and application agents 19.10 or later are required. It is recommended that you update the application agents to the latest version.
For replicas of centralized backups, when you set retention periods for different backup types, any undefined types use the full backup retention period. For example, if you do not define a log backup in the primary
objective, the log backup for the replication
objective is also undefined. After you run a manual log backup, replicas of that log backup use the same retention period as the full backup.
Click
Replicate next to
Primary Backup or
Primary Retention. An entry for
Replicate is created to the right of the primary backup
objective.
Under
Replicate, click
Add.
The
Add Replication dialog appears, with information in the left pane for each schedule that has been added for the primary backup objective of this protection policy.
NOTE Backups for all of the listed schedules will be replicated. You cannot select individual schedules for replication.
Select a storage target:
Storage Name—Select a destination from the list of
protection storage. Or, select
Add to add a
protection storage system and complete the details in the
Storage Target window.
Storage Unit—Select an existing storage unit on the
protection storage system. Or, select
New to automatically create a storage unit.
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 replicas.
The retention lock mode setting comes from the configuration of the selected storage unit. When you enable retention locking, the
Retention Lock Mode field displays the corresponding storage unit setting.
SLA—Select an existing replication service level agreement that you want to apply to this schedule from the list. Or, select
Add to create a replication SLA within the
Add Service Level Agreement wizard.
The
PowerProtect Data Manager Administration and User Guide provides more information about replication targets, such as SLAs.
To replicate after the backup finishes, move the
Replicate immediately upon backup completion slider to on.
For scheduled replication, move the
Replicate immediately upon backup completion slider to off, and then complete the schedule details in the
Add Replication dialog.
For replication of the primary backup, the schedule frequency can be every day, week, month, or
x hours.
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 from 1 to 12 hours.
By default, all replicas of the primary backup
objective inherit the retention period from the
Retain For value of the synthetic full and full backup schedules.
To specify a different retention period for individual synthetic full and full replicas, clear
Set the same retention time for all replicated copies, click
Edit in the row of each schedule that you want to change, update the value in the
Retain For field, and then click
Save.
CAUTION Setting a retention period for the replicas of other backup types (such as log backups, incremental, and differential backups, where applicable) that is shorter than the retention period of the corresponding full backup may result in being unable to recover from those replicas.
Click
Save to save your changes and return to the
Objectives page.
Optionally, to move backups from
protection storage to
Cloud Tier, add a Cloud
objective for the primary or replication
objective:
NOTE To move a backup or replica to
Cloud Tier,
objectives must have a retention time of 14 days or more.
PowerProtect Data Manager also requires the discovery of
protection storage with a configured Cloud unit.
Click
Cloud Tier next to
Primary Backup or
Primary Retention. Or, if adding a Cloud
objective for a replication
objective that you have added, click
Cloud Tier under
Replicate.
An entry for
Cloud Tier is created to the right of the primary
objective, or below the replication
objective.
Under the entry for
Cloud Tier, click
Add.
The
Add Cloud Tier Backup dialog appears, with summary information for the parent
objective to indicate whether you are adding this
Cloud Tierobjective for the primary
objective or the replication
objective.
Keep the
All applicable full backups slider to the right if you want to tier the backups from all of the full primary backup or replication schedules of this policy. Otherwise, move the slider to the left and select the full schedule(s) that you want to tier.
NOTE If the retention period of a schedule is less than the minimum 14 days required before tiering occurs, or is less than the value in the
Tier After field, you can still select this schedule for tiering. However, if you do not edit the retention period of this schedule or its backup or replication copy to a value greater than the
Tier After field before the retention period of the copy expires, the backup or replication copy of this schedule will not be cloud tiered.
Complete the
objective details in the
Add Cloud Tier Backup dialog, and then click
Save to save your changes and return to the
Objectives page.
The
PowerProtect Data Manager Administration and User Guide provides detailed instructions for adding a Cloud
objective for a primary or replication
objective.
Click
Next.
The
Options page appears.
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.
Click
Next.
The
Summary page appears.
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.
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.
Results
From the
PowerProtect Data Manager UI left navigation pane, you can select
Jobs > Protection Jobs to view the
Protection Jobs window, which displays the protection job group status. You can also click the job ID in the
Protection Jobs window to view the
Job ID Summary window, which displays the status of each asset job.
The status of an asset job is
Skipped when the asset is present in the Microsoft SQL Server host but unavailable for backup because it is offline or in a restoring, recovery pending, or suspect state. You can see the reason for the
Skipped status in the details section of the
Job ID Summary window.
When all the assets in a job group are skipped, the job group status appears as
Skipped in the
Protection Jobs window. When some but not all assets in a job group are skipped, the job group status appears as
Completed with Exceptions.
NOTE
When at least one asset in a job group has the
Failed status, the job group status appears as
Failed.
When a backup fails or a backup is skipped, the backup job steps appear as canceled for the particular database. The backup job steps are displayed on the
Step Log tab in the details section of the
Job ID Summary window.
Data is not available for the Topic
Please provide ratings (1-5 stars).
Please provide ratings (1-5 stars).
Please provide ratings (1-5 stars).
Please select whether the article was helpful or not.
Comments cannot contain these special characters: <>()\