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.
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.
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
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.
We use DDBoost for Veeam backups and not with our SQL Server environment, yet. We were able to test native SQL backups/restores versus native SQL compressed backups/restores and found the best option. We use SQL 2012 compressed backup and transmit from one DataDomain 2500 to another DataDomain 2500. The restore for the SQL 2012 database was slow until I found the best performance using this SQL query.
RESTORE DATABASE foo FROM DISK = '\\datadomain-01a\site-test\test_DB\foo_ddmmyy.bak'
WITH REPLACE, blocksize = 1024, buffercount = 2, maxtransfersize = 524288
We recently upgraded from SQL 2012 to SQL 2014 and the compression ratio on the DataDomain 2500 (OS: 18.104.22.168-532316) decreased from around 7x to 1.3x, equating from transmitting 50GB to transmitting 200GB.
its not black magic and it seems that Brent Ozar don't know a lot about Deduplication or maybe his post is very old! with right configuration its 10 times faster than native sql backup and backups will consume extremely less storage between 10 to 30 time less storage needed for storing backup.
just add to that SQL backups can bansported using FC not Copper and data can be Deduplicated at source not Destination! you can have full backups with speed of tlog backups. also rehydration of data in DataDomain is 100% transparent its so fast no one can feel it.
recently 2020-2021 backup solutions add incremental restore feature in their products, they don't even need to send full backup data to client for restore just changed blocks from last backup! i don't know how this gonna work for SQL but for VM its a winner.