Export (0) Print
Expand All

Protecting SQL Server Data

Updated: November 1, 2013

Applies To: System Center 2012 - Data Protection Manager, System Center 2012 R2 Data Protection Manager, System Center 2012 SP1 - Data Protection Manager

This topic provides important information that you should consider when you plan to protect SQL Server data with System Center 2012 – Data Protection Manager (DPM).

Protect data in file shares

If you have a database with files on a remote file share, protection will fail with Error ID 104. DPM does not support protection for SQL Server data on a remote file share.

Protect SQL Server 2012

Consider the following scenarios that are specific to SQL Server 2012 protection.

Things to remember

  • You must explicitly add the system account NTAuthority\System to the Sysadmin group on SQL Server.

  • DPM cannot protect databases that are stored on remote SMB shares.

  • Ensure that the availability group replicas are configured as read-only.

  • When you protect databases that use the AlwaysOn feature, DPM has the following limitations:

    • DPM will honor the backup policy for availability groups that is set in SQL Server based on the backup preferences, as follows:

      • Prefer secondary—Backups should occur on a secondary replica except when the primary replica is the only replica online. If there are multiple secondary replicas available then the node with the highest backup priority will be selected for backup. In the case that only primary replica is available then backup should occur on the primary replica.

      • Secondary only—Backup shouldn’t be performed on the primary replica. If the primary replica is the only one online, the backup shouldn’t occur.

      • Primary—Backups should always occur on the primary replica.

      • Any Replica—Backups can happen on any of the availability replicas in the availability group. The node to be backed up from will be based on the backup priorities for each of the nodes.

      Note the following:

      • Backups can happen from any readable replica i.e. primary, synchronous secondary, asynchronous secondary.

      • If any replica is excluded from backup, for example Exclude Replica is enabled or is marked as not readable, then that replica will not be selected for backup under any of the options.

      • If multiple replicas are available and readable then the node with the highest backup priority will be selected for backup.

      • If the backup fails on the selected node then the backup operation fails.

      • Recovery to the original location is not supported.

  • When you perform an alternate location recovery for a partially contained database, you must ensure that the target SQL instance has the Contained Databases feature enabled.

Protect SQL Server with the AlwaysOn feature enabled

SQL Server 2012 introduces a new high availability feature, named AlwaysOn. You can add your databases to Availability Groups, which are basically containers for databases that are configured for failover. System Center 2012 SP1 DPM supports protection of databases that are part of Availability Groups. The salient features of the DPM support for the AlwaysOn feature are:

  • DPM detects Availability Groups when running inquiry at protection group creation.

  • DPM detects a failover and continues protection of the database.

  • DPM supports multi-site cluster configurations for an instance of SQL Server.

Protect an Availability Group

The New Protection Group wizard allows you to create protection groups that contain Availability Groups. DPM shows the Availability Groups under Cluster Group.

To protect the whole group, select the group name. This way, any databases that you added to the group are automatically protected.

To protect the selected databases in a group, expand the group name and select the individual databases that you want to protect.

For additional resources, see Information and Support for System Center 2012.

Tip: Use this query to find online documentation in the TechNet Library for System Center 2012. For instructions and examples, see Search the System Center 2012 Documentation Library.
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2014 Microsoft