SQL - To Dedupe or not Dedupe - How are your ratio's looking?

Last post 06-28-2019, 8:30 AM by ameykarandikar. 5 replies.
Sort Posts: Previous Next
  • SQL - To Dedupe or not Dedupe - How are your ratio's looking?
    Posted: 06-25-2019, 6:52 PM

    Hi Commvault Forum,


    I am just wondering if anyone has any feedback on the whether to backup SQL using Dedupe or not.


    I have around 16TB for front end SQL data to backup. With a rate of change at aboiut 40%, I am looking at 25TB for a weekly cycle.


    I'm wondering whether to -


    1 - Use Dedupe

    2 - Don't use Dedupe


    I remember in V10 that dedupe ratio's weren't that great for SQL, and there was a good argument for not deduping SQL, as it wasn't worth the extra hardware for the DDB volume, and the extra overhead of facilitating deduplication. Not to mention troubleshooting dedupe (I hope you never had to!).


    However, I don't know if things have improved significantly or not in V11, with Dedupe release V4 (or is it V5 now?).


    So I'm wondering what everyone's ratio's are looking like, and secondly whether many of us have opted-out of SQL dedupe altogether?

    I am also aware that SQL compresses natively pretty well, which may also imapct overall dedupe ratios.


    I also have a similar argument for Oracle, but we have a lot more of that at around 90 TB.

    The rate of change appears to be around 1% (yes, one).  I'm thinking with such a tiny rate of chhange would dedupe help me here or not?




  • Re: SQL - To Dedupe or not Dedupe - How are your ratio's looking?
    Posted: 06-25-2019, 8:52 PM

    I have overseen an a CommCell that had a very high SQL Server backup frequency using three media agents across two sites processing 600,000 jobs per month with a backup success rate of 99.8% whilst having Dedupe and Compression enabled on v11.  For Transaction Log backups you shouldn't use a dedupe policy and just compress, but for your Fulls and Differentials it does make sense to Deduplicate even at non-change rate of 60% as this content is the most likely to be repeated over multiple cycles that you have on disk.

  • Re: SQL - To Dedupe or not Dedupe - How are your ratio's looking?
    Posted: 06-25-2019, 9:08 PM

    Thanks Anthony,


    Any idea if the underlying deduplication ratio's were much good, and would they shave much time off a large full?


    Likewise, do you think dedupe was a major factor in acheiving SLA's, RTO's and RPO's?


    I am always mindful of not applying dedupe if you don't need to, although it clearly has its benefits.


    Thanks again.





  • Re: SQL - To Dedupe or not Dedupe - How are your ratio's looking?
    Posted: 06-27-2019, 4:46 PM

    We do have deduplication enabled for our SQL backups and we have a large number of SQL clients and a large amount of SQL data to process nightly. According to what the software is showing we have 5.75pb of Total Application Size, 485.5tb of Total Data on Disk and a 91.75% Deduplication Savings for SQL. High percentage of dedupe is the good news, the bad news is in the near 4 years I have been responsible for our backup environment we have battled high Q & I times with the SQL DDB. The File System DDB has had it struggles too but normally the Q & I times, backup performance and completion times are all acceptable. Granted the FS DDB is smaller but it is unfortunately very normal for us to have high Q & I times, poor backup performance and very long completion times with our SQL backups. I have opened several tickets with CV support on this and we do implement the recommendations such as having the DDBs on separate volumes on local SSD drives. I also partitioned the DDBs between the our 2 MediaAgents. I am seriously considering disabling deduplication on SQL as it appears the software can’t handle the load despite implementing the suggestions from support which included replacing or adding hardware. We are currently several days behind in our Backup Copies on most of our SQL clients. My fear is disabling deduplication will solve some problems and create others. If we are truly obtaining 91.75% savings with deduplication I wonder how the software will deal with having to age off 91%.75% more data?

  • Re: SQL - To Dedupe or not Dedupe - How are your ratio's looking?
    Posted: 06-27-2019, 6:23 PM

    Thanks bstibal,


    That is exactly the kind of scenario I need to avoid.

    As much as dedupe can be great, it sucks time out of your life when it needs troubleshooting.

    A few things, and I'm sure this may have cropped up already -


    Have you sealed the store recently or periodically?

    Are your servers correctly speccced? Looking at that backend data size, I would say you are certainly in the "large" spec (x12 64 GB minimum)

    Is your primary storage on fast disk (not cloud\object\blob etc).


    Did you test your DDB volumes using iometer (correct raid group etc)

    Do you have Syn-Fulls smashing the crap out of the same disks you are trying to copy from (Dash Fulls are great, but I know some people are still using Syn Fulls which destroy shared  performance on plenty of shared disk libraries). If you have got Syn Fulls in play, can you either convert to Dash Full, or move to alternate storage\SP?

    What SP are you on? I know there was a new version of Dedupe released in SP14 or SP15.

    Disabling dedupe would not be the end of the world, but you need to identify your bottleneck.

    It is quite possible that the Q&I's are red-herrings. I have managed a number of smallish clients reporting high Q&I's and we just left it, as backups and copies were easily being met.

    Anyway mate - thansk for the reply, and good luck with your issues.



  • Re: SQL - To Dedupe or not Dedupe - How are your ratio's looking?
    Posted: 06-28-2019, 8:30 AM


    Commvault automatically skips dedup for SQL transaction log backups

The content of the forums, threads and posts reflects the thoughts and opinions of each author, and does not represent the thoughts, opinions, plans or strategies of Commvault Systems, Inc. ("Commvault") and Commvault undertakes no obligation to update, correct or modify any statements made in this forum. Any and all third party links, statements, comments, or feedback posted to, or otherwise provided by this forum, thread or post are not affiliated with, nor endorsed by, Commvault.
Commvault, Commvault and logo, the “CV” logo, Commvault Systems, Solving Forward, SIM, Singular Information Management, Simpana, Commvault Galaxy, Unified Data Management, QiNetix, Quick Recovery, QR, CommNet, GridStor, Vault Tracker, InnerVault, QuickSnap, QSnap, Recovery Director, CommServe, CommCell, SnapProtect, ROMS, and CommValue, are trademarks or registered trademarks of Commvault Systems, Inc. All other third party brands, products, service names, trademarks, or registered service marks are the property of and used to identify the products or services of their respective owners. All specifications are subject to change without notice.
Copyright © 2020 Commvault | All Rights Reserved. | Legal | Privacy Policy