Managing Mirrored SQL Servers

  • Article

Applies To: System Center Data Protection Manager 2010

System Center Data Protection Manager (DPM) protects SQL Server databases and clusters that use SQL Server mirroring technology. This support does not translate into any major changes in the procedure to protect or recover SQL Server databases in DPM. The following sections will call out any changes in procedure.

Prerequisites for Protecting Mirrored Databases

Before protecting a mirrored SQL Server database, ensure the following:

  • Install agents on both partners of the mirror.

  • Do not mirror the database on the same computer.

Protecting a Mirrored SQL Server Database

The procedure to protect a mirrored database is the same as protecting a SQL Server database.
When you select a mirrored database to add to the protection group in the Create New Protection Group wizard, DPM automatically detects that the database is mirrored and displays the mirror details on the Select Group Members page.

Protecting a Mirrored SQL Server Cluster

DPM also supports protection of mirrored SQL Server clusters. The procedure to protect SQL Server clusters that are mirrored is the same as protecting a mirrored SQL Server database.

Note

DPM agents must be installed on all the computers in the cluster.

DPM protects all the following configurations:

  • Principal is clustered, mirror is not.

  • Principal is not clustered, mirror is.

  • Both principal and mirror are clustered.

Common Scenarios

A protected SQL Server database gets mirrored

At the time of backup, DPM will detect that the database has been mirrored and raise an alert. You will need to remove protection (with retain data) for the database and then reprotect it.

Note

DPM will maintain a single replica for the mirror.

A mirror is broken

When the mirror is broken for a mirrored SQL Server database that is currently protected by DPM, backups will fail with alerts. Remove protection (with retain data) for the SQL Server database and reprotect it.

Principal partner in a mirror fails over and fails back before next backup

This scenario does not affect protection in any manner since DPM is not informed about the fail over, unless a backup of the mirror is in progress.

Principal partner fails and the mirror server takes over

DPM will detect that the mirror is now the principal partner, stops the backup job, and performs a consistency check after 30 minutes on the database that failed over.

Note

If during these 30 minutes the database fails back to the original principal, DPM will detect this and resume protection after performing a consistency check.

If you try to backup the mirrored database before the scheduled consistency check (after 30 minutes), an alert indicating that a consistency check is required will be created on the DPM Monitor tab and the backup will not start until a consistency check is done. To start the backup immediately, do a consistency check and retry the backup.

One protected database is made the mirror of another protected database

Where one SQL Server database protected by DPM is made the mirror of another protected database, protection will fail for both partners of the mirror. To continue protection, you must protect the principal partner.

Recovering a Mirrored SQL Server Database

When recovering a mirrored SQL Server database, always use the ‘Recover to alternate location’ option. Even if you want to recover to the original location, use the alternate location option and provide the path to either of the partners of the mirror.

Unsupported Scenarios

The following scenarios for mirrored SQL Server databases are not supported by DPM:

  • The database is mirrored on the same server.

  • The SQL Server mirroring session uses an explicitly-configured IP address.