Blog

I spent some time last week digging into Microsoft Data Protection Manager 2007, Microsoft’s solution for SQL Server backups, and I’m going to share some of my findings here with you, dear reader.

“But You Work For Quest Software! You can’t write a Microsoft DPM review!”

Yeah, disclaimer time – I work for the people who make Quest LiteSpeed, a big player in the SQL Server backup market.  What you’re about to read is not sanctioned by Quest, is not the opinion of Quest, has not been edited by Quest, etc.  This is just Brent talking to DBAs out there.

This review is not going to be the pros versus cons.  This review is only going to cover the things I uncovered that surprised me – things I didn’t expect to find, and things that I would have really wanted to know as a DBA before I bought the product.  There are plenty of places where you can find gushing, glowing reviews of DPM that say things like DPM is the “ultimate solution for protecting SQL Server data.” You will not find that language here.

I’m not saying DPM sucks, and I’m not saying you shouldn’t buy it.  DPM struck me as a really cool solution for its target audience, but DBAs that fall outside that target audience need to understand some of the limitations.

Microsoft DPM installation involves agents and reboots.

Software companies, Quest included, get in trouble when we use agents because there’s some agents out there that slow servers down.  Some DBAs have big problems with installing additional agents on their servers, and those DBAs are going to have a problem with the DPM agent.

The DPM agent relies on the Volume Shadow Copy Service (VSS), and before you even get started with DPM, you have to apply Microsoft hotfix #940349 to all DPM-protected servers.  That hotfix requires a reboot, and only then can you install the DPM agent – which also requires a reboot.

Further DPM upgrades may also require a reboot.  In the case of DPM 2007 and the updated Feature Pack that came out afterwards to address some issues, the agent updates required reboots for me.  The release notes aren’t clear about whether or not a reboot is required for the agents, but for me, it was.  Your mileage may vary.

As a DBA, this bothers me because scheduling reboots is such a pain.  You can’t schedule the DPM agent update & deployment ahead of time – you have to reboot to get it to take effect – so it means the DBA is sitting at a console on a Saturday night, pushing out agents and doing reboots.  This is a tough sell for me because other conventional backup software doesn’t require reboots.  I’m thinking back to when I used Idera SQLsafe or Veritas NetBackup, for example, and I don’t think those required reboots for agent updates.

Microsoft DPM backup jobs don’t show up in SQL Server Agent.

Backup jobs are controlled by the DPM service, not by SQL Server Agent.  This isn’t good or bad, it’s just different.  It means that DBAs can’t look at SQL Server Agent to see when backups are running, how long they’re taking, whether they were successful, or when the most recent backup was.  Instead, to get any information about backups, the DBA has to open Microsoft DPM.

I love having backups controlled inside Agent because if someone complains that the server is running slow, I like going into Agent to see what jobs are currently running.  SP_Who2 is great as well, but I like Agent’s status because I can tell what regularly scheduled jobs are going on.  DPM’s jobs aren’t there, though, so I have to resort to sp_who2 and poking around.

On the other hand, I gotta tell you that DPM’s management console interface is pretty nice for Wintel admins.  It does a great job of showing what jobs are currently running across your enterprise, and the restore process is really intuitive.  Shops that have a few Wintel admins, no DBA, and a few SQL Servers to back up will be comfortable working with the DPM user interface.

Microsoft DPM can’t back up faster than once every 15 minutes.

Vipul’s DPM article says “Transaction logs are continuously synchronized to the DPM 2007 server, as often as every 15 minutes.”

That is not “continuously”.

If you manage financial data, sales data, healthcare data, security & auditing data, etc, and you lose 15 minutes of data, you can lose your job.

I’ll give you another example – before application upgrades, server firmware upgrades or SQL Server patches, I like to take a quick t-log backup before I make changes.  I hop into SQL Server Agent, right-click on the t-log backup job, start it, and wait for it to finish.  It’s an easy and quick insurance policy, but you can’t do that with DPM.

DPM is sometimes called Continuous Data Protection (CDP), but that only works if you can access the server’s live log file.  The theory is that if you have a crash between t-log backups, you restore all of the t-log backups, and then apply the live transactions from the SQL Server log file (LDF).

But wait – didn’t we have a crash?

How do we access the SQL Server’s LDF files if the server crashed?

If the LDF files are still available, then the SQL Server is still available.  So you only have CDP if there’s data corruption in the MDF file, or if there’s some kind of problem that stops SQL Server but still lets the log files be read and copied somewhere else.  I’m not saying that never happens, but it’s pretty rare.  Usually, when I have a crash, I can’t even get the server to boot, like I’ve had a serious hardware issue or a Windows issue.  In those events, DPM isn’t continuous data protection, and I lost the data since the last 15-minute backup.

That isn’t a showstopper problem for most shops, but it’s just something to be aware of.

Microsoft DPM backups can only be written to DPM servers.

DPM is a service-based backup: the agent on your SQL Server communicates directly with the DPM Server, and the backups go straight to the DPM server.  Makes sense, right?

Now what happens when you need to do a bunch of backups at once, like if your servers have similar maintenance windows?  Suddenly the DPM server becomes a bottleneck, because it’s only got so much network throughput and IO speed.  With backup software like LiteSpeed, you can put your backups anywhere you want.  You can write to different file servers, you can write to CIFS appliances like NetApp or EMC SAN controllers, you can write to local disk, you can write to DR servers, etc.

And now what happens if your DPM server goes offline?  The sad reality is that no server is ever 100% reliable.  With LiteSpeed, if your file share server goes offline, you can point your backups somewhere else.  With DPM, when your DPM server is down, you’re unprotected – no backups, and even scarier for me, no restores.

Ideally, DPM would use a farm-style architecture like Veritas NetBackup.  With NetBackup, you can put backup servers in pools, and when you set up backups, you can point backups at entire pools of servers.  That way NetBackup can automatically load-balance the backup activity across multiple media servers.

You can’t see DPM backups in Windows Explorer.

Call me old school, but I like to go into Windows Explorer and see that my backup files exist.  That’s not possible with DPM – it uses raw disk space to create storage pool partitions, and you can’t access these.  You can’t back the backups up to tape with your enterprise backup software like TSM or NetBackup or Backup Exec.

This isn’t necessarily better or worse – DPM is doing you a favor in the sense that it’s masking a lot of complexity from you.  But for those of us who are used to poking around behind the scenes, running verify-only backups, or just plain looking at file datestamps and sizes, that’s no longer an option.

DPM’s Active Directory reliance means problems for DMZ servers, workgroups.

Yes, it’s bad practice, but I had to manage SQL Servers in the DMZ, in workgroups, and in other domains.  DPM relies on Active Directory, so setting up these servers is more complex than typical backup software setup.

I didn’t build out a multi-domain lab, but if you have multiple domains, test the deployment of this.  I’d be curious to hear if any of my readers (that’s right, EITHER of you) have tried this.

DPM doesn’t allow log shipping.

DPM backup files aren’t traditional backup files per se.  If you want to do log shipping, you have to use something else – either native SQL Server backup files or a third party product like LiteSpeed.

That means shops with log shipping will have to manage their backups under two systems: both under DPM, and under their log shipping.  One of the selling points of DPM is that it reduces complexity, but if you use log shipping, you need to be aware that this is going to get tricky.

“My sources are unreliable, but their information is fascinating.” – Ashleigh Brilliant

I work for a company that in some ways competes with Microsoft DPM 2007, so take my info with a grain of salt.

If I had to choose a backup method for your database servers, and if I was seriously considering Microsoft DPM, I would spend two days evaluating traditional third-party backup products like Quest LiteSpeed, Red Gate SQL Backup, and Idera SQLsafe.  (Yep, that’s right, I’m linking to my competitors, and I’d also like to give a shout-out to Red Gate SQL Backup for winning SQL Magazine’s Gold Award this year.  Enjoy it while it lasts, fellas, because I’m gonna wipe the floor with you next year.  (Ha!  I kid.  (Not really.)))  These kinds of products basically operate the same way, but with different featuresets and user interfaces.  You can get a really good feel for any of these products within a few hours.

Then, I would spend three very solid days evaluating Microsoft DPM.  DPM is seriously, significantly different than conventional SQL backup software, and it’s going to take you a few days to dig deep enough into it to discover the ways that it’s different.  (Remember, it installs agents that require multiple reboots, so you can’t test this on your normal SQL boxes.)

Data Protection Manager is not necessarily better or worse, it’s just dramatically different from the SQL Server backups that you’ve used in the past.  When you evaluate DPM, you need to start with a fresh, open mind and don’t take anything for granted.  Build yourself a lab and do several backups and restores to get a feel for what the process is like.  Make a list of all of your servers and how they get backed up, and make sure DPM can fit those needs – especially DMZ servers, mission-critical servers and log shipped servers.

DPM has some awesome features, like restoring Windows system state info and detecting which blocks got changed instead of doing a full backup, but in your excitement about those features, make sure you don’t miss things that you just assume it’s going to handle.

If You Liked This Review…

Check out my Microsoft SQL Server Best Practices for Backup article.

↑ Back to top
  1. Terrific review and article. A reality shot for any software product. Competition is a great thing.

  2. Thanks for the info Brent. DPM seems like one of those applications that is a nice idea, but not practical for most shops at this time.

  3. How much cheaper is DPM compared to NetBackup?

  4. David & SQLBatman – thanks! I try to be fair & honest. At least on the web site. The weight on my driver’s license, well, that’s another story.

    Scott – it depends so much on how you negotiate your software agreements. I think DPM is dramatically cheaper right now in the few installs I’ve seen, but I have really, really limited exposure on the pricing side. NetBackup’s SQL agent license was pretty expensive last time I looked about two years ago – so expensive that I think we were able to replace all of our NetBackup SQL agents with another third-party SQL compression solution and paid my salary for a year with the savings. :-D Nothing against NetBackup – I’ve used it for Windows file backups and it does work pretty well. The console totally blows compared to DPM, though.

  5. Interesting read, i’ve just setup a DPM Server 2007, and I seem to have run into a problem with multiple domains.

    From what i’ve read so far, you can access other domains that are not part of your DPM servers forrest. I.e. if you want to backup a clustomers server, that runs in a domain not owned by you, then you’re stuffed.

    Has anyone else had this issue, found a work-around?

  6. Skrusty – the workaround is to create a trust between the domains, but of course that won’t fly with everybody.

  7. Interesting, I’m currently trying to configure protection for DMZ machines and have obviously run into issues, when you say:

    DPM relies on Active Directory, so setting up these servers is more complex than typical backup software setup.

    By complex do you mean you have to configure ADAM on the DMZ servers? Or is there another way to configure protection for DMZ servers?

  8. You have to figure out how to give the DMZ permissions to write to file shares on your domain-based DPM boxes. I bailed out at that point, but I’m sure Microsoft will be able to help you out there. It’s gotta be a common request from shops with DMZs.

  9. Thanks Brent, I’ll look into that.

  10. Your review provides valuable information but it appears to be specific to first time install of DPM 2007, not an upgrade from 2003 to 2007, as we intend to do. Can you determine if the 2007 Upgrade process presents a significant challenge, or a minor one? Are the operational/functional processes in 2007 significantly changed from 2003 (screens, etc.)?

  11. No, sorry, I don’t have any experience with the upgrade process.

  12. Good review. My company that I work for have recently purchased DPM and have found to our amazement that we can only transfer files across DPM servers every 6 hours. Hence 15 minute recovery is only available on site. Off Site DR, is restricted to 6 hours. I just don’t get it and Microsoft don’t seem to have any answers? May be I am expecting too much.

  13. Re: Log shipping – the only way this will work with DPM is either to try and copy with rename the transaction log backup files that DPM creates in a nested folder structure under the backed up log file itself – and they are not held very long or to exclude the log shipped database from DPM’s protection or move to mirroring – log shipping and DPM does not work as they break each others restore chains.
    It is not totally fair to say that DPM backups are invisible to SQL – thye are certainly no more invisible tan the backups Commvault does and are observable in the SQL logs, MSDB system tables and the SQLWB GUI when you choose restore.
    I agree every 15 minutes is too long but worse still is the every 6 hours for replication between DPM serves meaning a great risk if using DPM for cross site replication.
    We are using DPM but have excluded are log shipped database from protection and plan to move to mirroring before adding it back in.

  14. Brent, you can in fact back up DPM to tape with NetBackup, or anything else that supports VSS volumes. We do this with our DPM installation where I work. MS has a detailed guide on TechNet describing how to set it up.

  15. Ben – absolutely, the key being “anything else that supports VSS volumes.” It’s up to your tape backup software to support that type of volume, and many (or most) don’t. At the shop where I worked, we would have had to upgrade our entire NetBackup infrastructure in order to support that, and we couldn’t.

  16. Hi,
    I have “interesting” problem. When DPM 2007 backups increment of sql database, if log file is bigger then free space in disk where log file is, then error appears. Question: why it is needed free space in disk where log file is and for what this space is needed?

    BR,
    Redas.

  17. DPM uses VSS snapshots to capture disk changes. Those changes have to go somewhere. If you’re writing log data faster than VSS can get the data off the SQL Server, then you will run out of drive space.

  18. Whould be totally nice to have also advice from you.

    We have such situation: we do full backup every night and increment every 2 hours. Every works fine till database is reindexed (we do it every second week). At that time after reindex log file increase a lot and error in DPM appers.

    Maybe you now how to solve this issue?

    BR,
    Redas.

  19. Yep – add more drive space, use another backup method, or back up more often.

  20. Brent, nice article, thanks. We can always use thoughtful commentary on these new MS products.
    FYI, your comment that you can’t see DPM backups in Windows Explorer on the DPM server is incorrect. It’s true that there’s no drive letter assigned to the DPM data disk, but all the volumes on it are actually mapped under C:\Program Files\Microsoft DPM\DPM\Volumes. If you use Windows Explorer to browse under that path you will see all the systems and their backup data (at least the most recent snapshots).

  21. just a small Q my company uses TSM … my question is what is better in DPM2007 then TSM & what r the major updates thnx

    • Hi, Ameer. The best people to answer that question would probably be Microsoft. Contact your local Microsoft representatives or software sales team, and they’d be happy to talk about the differences. I don’t have enough familiarity with TSM to address the question, unfortunately. Take care!

  22. I agree with him. I have tried DPM on a large SQL server with four Databases, size totaling two TB. DPM can’t take backup even on Gigabyte LAN (even direct LAN connection bypassing switches). Evey time it came with a random message like Replica inconsistent, storage threshold exceeded (DPM is on 16TB Raid). It was a lot of work to manage and understand how DPM works as the error messages are not explained any where in UI of DPM. Finally after trying for 10 days I killed the DPM server and returned to traditional daily nightly backups.

    Managing DPM is not so easy as shown in MS presentations. If say you have 40 SQL databases and a backup fails, you have to click on 40 items to resume, no multiple selection, no automation. In fact most of the time you will have bizarre error messages. I feel DPM is not a mature and tested at all on large scale data.

  23. Brent, et al,

    This article is a few years old; do you have an updated opinion or new insight on DPM? I’m getting ready to evaluate DPM against other enterprise solutions for database backup / restore software – any recommendations? Yes, I have Quest on my list ;-)

    Mike

  24. Brent – So, Are we eliminating the entire concept of SQL Server Native Backups/Restores mechanism, by using DPM??
    Am a Newbie to DPM! Please correct me If I got this wrong!

    In our shop, we(DBA team) do use LiteSpeed for all our SQL Server Backups and Storage guys use NetBackup for remote Tape/Disk Backups on our Servers.
    Where do you think we can be benefited using(Well, at least considering) DPM 2010? Any comments pls?

    • Sreekanth – in order for me to know whether you can benefit from DPM, I would need to know a lot more about your shop, your goals, and your servers. That’s something we can explore via email at help@brentozar.com if you’re interested in bringing us in to help with your disaster recovery planning.

  25. Great article as usual, Brent. I’m trying to figure out how much of this is still relevant for DPM 2010/2012 and if there are any new enhancements. Microsoft’s “What’s new with DPM 2010/2012″ articles aren’t giving me much information. We currently do native SQL backups to an EMC Data Domain.

    Anyone know of good SQL DPM articles for 2010/2012?

    Robert

  26. Pingback: Windows Hyper-V Book | SQLRockstar | Thomas LaRock

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php