Q:

SQL Server Backups on DataDomain

Our sysadmins keep pushing us to use data domain and DD boost for taking our SQL Backups.  Has anyone successfully used this solution?  I know what Brent Ozar says about Data domain and sql backups. But apparently the DD boost makes it a better solution.  Has anyone used DD boost and also used SQL Backup by redgate? Just wanted to get thoughts on what you would say to your sysadmins and managers apart from this article from Brent Ozar.

A:

Data Domains use their own deduplication and compression features, which don't always play well with SQL Server's own compression. That's the main point. 
Whether this is problematic or not depends on many factors. If you're not compressing your backups with SQL Server, the Data Domain will be able to deduplicate the data more efficiently, but more data will have to travel through the network. If you're compressing your backups, the Data Domain will deduplicate less efficiently, so your sysadmin will likely push you in the uncompressed route.
Another possible issue is an higher amount of time for restores, since the DD will have to apply its magic to the data before making it available to you.
In the end, there is only one way to know for sure: test it. Take backups with and without DD for a significant amount of time (take at least the number of backups you will normally keep online - say 1 month), with and without compression. Restore those backups to a test server regularly, compare the backup/restore times with and without DD and draw your conclusions.
We tested the impact of DD on our backups and it turned out to have an acceptable impact on performance in our case, especially taking into account the advantages offered by DD. YMMV: your RTO may or may not not allow such impact.

A:

Yes, we've successfully implemented DDBoost SQL Agent Backups to a Data Domain backup device and seen fast backups and restores with good de-dup ratios. It's not perfect, and definitely should be well tested in your environment, but with the right settings, seems to work well.
Brett's 2009 article is describing a scenario where that deduplication takes place immediately before data is written to the storage device. In that case, he is absolutely right about the dilemma:

Bad Option A: Use SQL native (or 3rd-party) compression to send compressed backups over the network, annoying your storage admin since already compressed data dedups badly.
Bad Option B: Don't use backup compression, which means backups and restores take longer, since the entire (uncompressed) database has to travel across the network.

EMC's DDBoost MS agent (DDBMA is their acronym) in some ways works like SQL native compression (and 3rd party backup agents), it compresses the backup on the client side before sending over the network. 
In addition, though, the client-side compression/de-duping is done in a way that is compatible with how the de-duped blocks are stored on their appliance, and they claim they can do some sort of weird "block lookup" thing to see if it even needs to compress and send the bits across the network at all! Sounds kind of like black magic to me (obviously something has to go over the network), here is their super vague marketing diagram:

To be clear, DDBMA is already a 3rd-party SQL backup agent, so wouldn't be compatible with other 3rd-party backup agents like Redgate Backup or Litespeed or whatever. That means, though, that we can just test it against native SQL compression, or against Litespeed, in an apples-to-apples comparison.
(Caveats: all environments are different, test with your own databases, results may vary, do not taunt happy fun ball.)
In our testing, we found that backups and restores were slightly but not intolerably slower using DDBMA than SQL native compressed backup and restores to the same device. In our case, the resulting de-dup space savings (especially across time, and across multiple environments that have similar copies of the same huge databases) was enough to tip the scale.
The key to getting good performance, we found, was to use the "-S" switch to enable backup striping to multiple files. Now, you can do this with native backups as well (BACKUP DATABASE foo TO DISK='foo_1.bak', DISK='foo_2.bak'), but I seldom see people using this feature.
Here were our backup test times for a 1TB test database:

(The longer times for SQL compressed were a quirk of this one DB, we think it had to do with internal row/table compression.)
The single-stripe DDBMA backup times were extremely concerning at first, until we found the -S switch.
Restore times (of a different db) showed a similar trend (title should be 300 GB, not 300 MB):

I should note that the latest version of DDBMA (v3.5) supports a compressed restore option (which I have not extensively tested), so these times might improve even more.
In our environment, the time savings (vs uncompressed, which is what we were doing previously) were significant enough that we no longer do nightly differentials, even on our largest servers; we do nightly fulls everywhere. Our weekend full backups used to take 12-18 hours on some boxes, and now run in 1-2 hours.
All that said, it's not a perfect tool. I find the UI quite awkward and the TSQL script syntax annoying. Versions prior to v3.5 had a bug that made some UI actions (and scripted tran log backups!) super slow, which is now fixed. v3.5 has a new bug that tries to restore files to the server default file path when they should go to the original file path. 
I can't speak to cost/value/ROI or anything, I was just tasked with testing different SQL backup methods to this device.

