Share via

Data Protection Manager - an ultimate solution for protecting SQL Server data

  • Article

I learned about new tool from Microsoft System Center family called Data Protection Manager 2007.   Here is some details on how it works and how can it save big bucks when comparing with other backup compression tool in market such as LiteSpeed.

Below is its overall architecture:

DPM Architecture

How does SQL Server protection with DPM 2007 work?

DPM 2007 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 integrity:

  • Transaction logs are continuously synchronized to the DPM 2007 server, as often as every 15 minutes.
  • 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 server or appliance. DPM 2007 maintains up to 512 shadow copies of the full SQL Server database(s) by storing only the differences between any two images.

Assuming one “express full” per week, stored as one of 512 shadow copy differentials between one week and the next, plus 7 days x 24 hours x 4 (every fifteen minutes), DPM 2007 can provide over 344,000 data consistent recovery points for SQL.

 

How does Restore work?

With only a few mouse clicks and DPM 2007, you can:

  • Restore a SQL database directly back to the original server
    • 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
    • This 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.
  • Copy database files to an alternate server,
    • This option enables disaster recovery, compliance auditing, or software testing – without affecting the production environment
  • Copy the database file to tape,
    • Use this option to create a long-term archive or portable media of the data at any recovery point, even after the fact. For example, if the accounting department closes the quarterly books at 10:45 AM on a Tuesday, you can “recover” the data from that point, directly to tape – without backing up anything else or impacting the production server that is already serving new data.

Comparison of DPM 2007 with other Compressed Backup Utility:

Customer Scenario – Protect 4 Databases totalling 250 GB for 14 days:

  • With good compression efficiency of 70%, like LiteSpeed:
  • 30% additional disk space required on a production server’s local disk, for nightly compressed backups – which are then copied to the backup server at 75GB per night.
  • Total additional disk to protect 250 GB for 14 days with LiteSpeed = 1.12 TB (75 GB on local production server plust 1.05 TB for 2 weeks, 75 GB each day)
  • With DPM2007, you don’t need additional local disk space.
  • On DPM server, DPM replica “Express Full” which is exact replica of databases will consume 250 GB. In addition to that with DPM daily transaction logs at 10% change rate, it will require 25 GB on daily basis. So, to keep two weeks (14 days) of data on DPM server you need 575 GB (250 + (25 * 13)) as compared to 1.12 TB for LiteSpeed.

DPM - comparison1

         DPM - comparison2

DPM - comparison3

Comments