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.