SQL Backups: Compression vs Deduplication

SQL Server Administration
1

My company is pretty much going to force me to go away from flat file backups and use a third party solution (similar to CommVault) that uses deduplication for database backups. From what I understand, deduplication only backs up changes since the last backup and in the event that a previous backup gets deleted, it keeps the blocks needed to make the current backup valid...and so on. I see a problem here in the event that disk corruption occurs and you can't get the blocks needed that may have been created long ago. You would also be dependent on the third party software to manage all that properly. I understand deduplication may save time and space but it seems like you are giving up a great deal of reliability to do so. I read Brent Ozar's article from 2009 as well as some other articles about it. I'm wondering if the deduplication process and method has gotten better (more reliable) since then.

Can someone educate me more on the topic? Am I living in the past wanting to do flat file (compressed) backups?

Thanks

2

Yes, you're basically at the mercy of the vendor to insure that they have all needed blocks.

Most vendors that I know of will back up their own decompression dictionaries to make sure that data is never lost.

3

Would anyone on here recommend deduplication over flat file backups (with compression)? Benefits to deduplication...Pros, Cons? Do those benefits outweigh the risks/cons?

Thanks

4

The real question should be how long will it take to recover from those backups? I have found that almost all of these types of solutions take at least 3 times as long to restore as they do to back up the system.

Whether or not these solutions work really depends on the RTO and RPO for that system.