SQLServerCentral Article

Microsoft DPM



Microsoft Data Protection Manager V2 gives more granular control over your recovery time objective (RTO) and recovery point objective (RPO) on selected SQL Server Databases. Microsoft System Center Data Protection Manager (DPM) is a server software application that enables disk-based data protection and recovery for file servers in an Active Directory domain. DPM performs replication, synchronization, and shadow copy creation to provide reliable protection and rapid recovery of data for SQL DBA, system administrators and users.

How SQL Server DBA benefits out of it

Some of the things that I will like to discuss ahead before we dive into some important details.

Microsoft DPM v2 System requirements: Beta 2 release planned for May 2007

  • Windows Server 2003 with Service Pack 1 (SP1) or later
  • Windows Storage Server 2003 with Service Pack 1 (SP1) or later.
  • Windows Server 2003 R2.
  • Windows Storage Server 2003 R2.

Where DPM v2 can be use to protect servers

  • SQL Servers 2000 & 2005 Clusters & non-Clusters
  • Share Point Servers
  • Exchange Servers
  • File Servers

SQL Servers requirements

  • SQL Servers 2000 on SP4
  • SQL Servers 2005 on SP1

Microsoft DPM Storage Pool:-

  • DAS Direct Attach Storage
  • SAN Storage Area Network
  • iSCSI Windows Certified Internet SCSI

Installation, Documentation & Configuration Information: www.microsoft.com\DPM

Below information is strictly referencing to SQL Servers: - Important factors\points

After installing & configuring DPM 2007. DPM will scan Active Directory to

find servers that it can protect. But important part is that DPM server & all

SQL Servers 2000 & 2005 should be the members of same Domain or Domains with

cross trust.(does 3rd party backup software requires this or concern

about domain). If either one is not on same Domain or Domains then you cannot

protect those SQL Servers. Simply choose the servers that you want to protect

from the list presented in the DPM "install agents" interface, you also need to

make sure that you will need to deploy the DPM protection agent on the sql

servers to be protected.

DPM uses a combination of transaction log replication and block-level

synchronization in conjunction with the SQL VSS Writer to help ensure your

ability to recover SQL Server databases. After the initial baseline copy of

data, two parallel processes enable continuous data protection with


An "express full" uses the SQL Server VSS Writer to identify which blocks have changed in the entire production database, and send just the updated blocks or fragments. This provides a complete and consistent image of the data files on the DPM v2 server. DPM v2 maintains up to 512 shadow copies of the full SQL Server database(s) by storing only the differences between any two images.

Transaction logs are continuously synchronized to the DPM server, as often as every 15 minutes.

Databases are recovered back where they came from into the active SQL Server, with no additional work for the SQL administrator to do afterwards. This improves recovery time and reduces the number of people needed during a crisis recovery.

Restore to a "recovery database" on the original server is an option that provides an alternative where the active SQL database is untouched, but the older data can be restored onto the same SQL server but in an alternate database – allowing both versions of the data to be accessed independently but concurrently.

DPM v2 provides a new feature: "lossless recovery" of SQL Server data. What that means is after DPM v2 restores your database to the latest 15-minute recovery point; it can automatically reapply the surviving logs from the production server to the very last committed transaction. Thus, the last completed database transaction is recovered and ready for business.

DPM is very sensitive to volume mount points in cluster environment where you have to take an extra precaution’s & it doesn’t give you that comfort. Once you have all SQL Servers in you DPM Protected Group, you need to set alerts for those jobs schedule for individual SQL Servers database to send alert emails to SQL DBA if it fails. This is very important because DPM does consistency check to make sure that backup file on DPM & on SQL Servers are same\consistent, if not then you know the meaning of redo, we have to do it again.

This one I took from the Microsoft DPM FAQ (courtesy to Microsoft) how much

data can I protect with DPM.

Microsoft states that DPM has been tested successfully in our labs with a single DPM server protecting 30 servers with 6 terabytes (TB) of data. However, this is not a hard limit on what DPM can support in terms of capacity. Some customers are using a single DPM server to protect more than 10TB of data spread between 50 servers. The exact number of servers that can be protected by a single instance of DPM will vary with server configuration, network bandwidth and the data profile (distribution & churn).

Some considerations that I should keep in mind?

As far as the protection process is concerned, the Microsoft DPM v2 product uses Volume Shadow Copy Service (VSS) to maintain a centralized version of the database backup from either a SQL Server 2000 or 2005 instance.

The DPM storage pool may be either DAS, SAN, iSCSI.

As far as scheduling the DMP process, you can schedule jobs on the DPM server to take an Express Full backup, differential or transaction log backup every 15 minutes and it will copy the available files based on the native backup schedule.

Once you have all SQL Servers in your DPM Protected Group, you need to set alerts for those jobs to notify the SQL Server DBAs via email if the DPM process fails. This is very important because the DPM product performs consistency checks to ensure that the backup files on the DPM storage and on the local SQL Server are consistent. If not, then the copy process will need to be re-executed to ensure the files are consistent.

If the network goes down, the agent that is local on the production server continues to cache all the changes in the production server until the network is working again.

DPM has a two-stage error correction process. First, DPM automatically validates the replica against the production server to ensure that the replication is consistent and has occurred as planned. Second, if inconsistencies between a data source and its replica are found during validation, the fix-up activity re-sends the object(s) from the data source to the replica.

When initially installed, DPM automatically discovers all the production servers in the domain that need to be protected. As new servers are added to the network, DPM discovers and alerts the administrator that there are unprotected servers in the environment, creating a seamless protection, recovery, and maintenance solution.

DPM makes it possible for IT/SQL administrators to maintain 30days worth of shadow copies on disk and to create tape backups for disaster recovery scenarios on a less frequent basis.

Every DPM action is logged on the DPM server, using SQL Server 2000 as the logging engine. This provides robust performance and reliability.

What are some of the advertised benefits of the Microsoft Data Protection Manager?

  • Continuous Data Protection
  • Lossless Restores for Applications
  • Superior Application Integration for Exchange Server, SQL Server, and SharePoint
  • Rapid Recovery
  • Reliable Recovery
  • Seamless Disk and Tape Integration
  • Unified Protection Policies Across Data Types
  • SLA-driven Backup Process
  • Block Filter

In addition, I am including a few resources as links from this article.

DPM 2007 & SQL datasheet - http://download.microsoft.com/download/5/1/e/51e28f3b-4626-474a-bf88-070f33886c88/DPM_v2_Datasheet_Protecting_SQL_Server.pdf

DPM 2007 & SQL whitepaper - http://download.microsoft.com/download/f/2/0/f209036f-27b4-4033-8b5f-d8afeb5c23e8/DPMv2_Whitepaper-Protecting_SQL_Server_with_DPM_v2.pdf

This takes you through the entire solution, including how the replication

works specifically for SQL Server: http://msevents.microsoft.com/cui/eventdetail.aspx?eventID=1032322230&Culture=en-US

Below is the DPM diagram which will help to understand where it can fit in

your Backup\DR environment.