Protecting Mirrored Databases with DPM 2007 SP1

Considering the vast amount of mission critical data stored on SQL servers globally, the high availability of database mirroring with the frequent snapshots of DPM make protection and recovery scenarios much less fearful for administrators entrusted with protected SQL data.

Database mirroring protection can be thought of as a blend between SQL Failover Clusters and stand-alone SQL Servers. As with SQL Clusters, DPM is able to follow the database as it is failed over from one node to another. At the same time, DPM still provides the same level of protection scheduling options and recoverability as it does with stand-alone SQL databases. DPM is also capable of protecting a mirrored database when it is encrypted, via the use of certificates, or if the mirroring configuration spans domains or even forests.

Since we have a fundamental understanding of database mirroring based on the blog “SQL Database Mirroring for DPM Administrators”, let’s dive in and discuss what is needed to protect a mirrored database.

There are videos available for viewing as supplements to this blog which demonstrate the protection of a mirrored database which spans a Windows Failover Cluster on the Principal side and the Mirror server as a stand-alone SQL server. Another video covers a basic database mirror across a pair of stand-alone SQL Servers, while the third demonstrates what happens when a protected database is mirrored after protection has been running for some time.

Establishing Protection

When establishing protection of a SQL Server 2005 or SQL Server 2008 mirrored database, you will need to confirm that the DPM Agent has been installed on both the Principal and Mirrored servers. This is a requirement in order to implement protection. If one side of the mirror is a Windows Failover Cluster, then both nodes of the cluster and the SQL Mirror server must all have the DPM agent installed.

In the following example, we will protect the AdventureWorks database which has been mirrored across a stand-alone server and a 2-node failover cluster. All 3 servers involved have the DPM agent installed already.

As we see in the Create Protection Group wizard, the datasources for the cluster as well as the stand-alone server have been displayed. Notice that text has been appended to the name of the AdventureWorks database which tells which physical or virtual server the database mirror extends to.

Continue through the wizard setting up the disk and tape protection, the retention range, synchronization frequency, etc., until the wizard completes. At that point, the initial replica of the protected data will be taken if you have chosen the appropriate option in the Choose Replica Creation Method page of the wizard.

After a period of time, the Protection Status in the Protection tab will show OK for the newly created protection group. DPM is now able to protect the mirrored database you have selected. As with all things however, the database will not remain on the current Principal server for the remainder of eternity. So what happens when there is a failover of the mirror and the mirroring roles are switched?

Immediately, there will be no indication that DPM has a problem protecting the database. If the mirrored database fails back to the original node before the next recovery point or sync is run, then DPM will continue protecting the data source as if nothing happened. If the database is on a different server than expected by DPM, then DPM will fail to successfully create a recovery point.

The error will be specific in its detail and will read like the following.

Error 32015: DPM is unable to continue protecting the selected database because DPM detected a mirroring session failover for this database.

Recommended action: Run a synchronization job with consistency check.

Simply running a consistency check will take care of the problem and future recovery points should complete successfully. If the failover occurs and there is no one able to get to the server to force a consistency check within 30 minutes after the failure, DPM will automatically run one. This can be seen in the Monitoring tab of the DPM Admin Console under the Jobs tab. Look for the failed Recovery Point job that was run after a mirrored database failover. Look for a Consistency Check (CC) job to run 30 minutes later on the same data source. When that CC runs to completion, a Recovery Point will be created as well. This can be verified by reviewing the available Recovery Point in the Recovery tab for that data source.

Changes in State

In situations where a protected database’s mirrored status changes, DPM will not be able to create a Recovery Point until you stabilize the data source because DPM will detect the change in state. Anytime a database goes from non-mirrored to mirrored or vice versa, the data source will need to be removed and added to the protection group again.

The reason is that during the creation of the protection group, DPM scans the server on which the database resides to see if it is mirrored or clustered with other servers. Because of the enabling or disabling of mirroring on the database, DPM will need to either add or remove dependent servers from the database in question.

Once the SQL datasource has been updated and added to the protection group, the Initial Replication will run and a new recovery point will be created. A word of caution: when reviewing the recovery points for the database after a change in mirroring state has been made, you will see two entries for the same database and each will have recovery points available for restore.

DPM does not distinguish in the Recovery tab between the recovery point copies that were and were not mirrored.

Videos

Summary

In this blog, we discussed how to protect a mirrored database and how to stabilize DPM’s protection of the mirrored data source when failover occurs and we discussed what happens when mirroring is enabled or disabled on an already protected database. Take a look at the included video content for a demonstration of the actions covered here.

Vic Reavis
Support Escalation Engineer
Microsoft Enterprise Platforms Support

Comments

• Anonymous
  January 01, 2003
  I would request you to provide the videos