Post reply

3rd party tools for backup compression

  • January 26, 2015 at 6:30 am

    #294009

    Hi,

    Currently we are using Sql 2008R2. Our database size around 320-350 GB now. We are using Native backup compression. We are taking backups to disks on different server.

    I am monitoring backup file size from a month, currently we are getting backup file size around same size of the database (backup file size is 3200-350GB).

    Business people are expecting database will grow till 1.5 TB also. For that large db is native compression backup is good or using 3rd party tools for backup compression is the better option?

    Current backup file compression is working or not? Backups file sizes is varying just 5 GB more per week.

    Currently we have tran backups for every 1hr

    How do you manage backups for very large databases?

    Please suggest me 3rd party tools, which will give more compression

  • January 26, 2015 at 8:39 am

    #1773137

    which edition\version of sql server are you using?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • January 26, 2015 at 9:09 am

    #1773145

    2008r2 enterprise edition

  • January 26, 2015 at 9:14 am

    #1773147

    As far as 3rd party tools are concerned, SQL Backup Pro[/url] has worked well for me. It has a lot of other useful functionality in addition to compression.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • January 26, 2015 at 9:15 am

    #1773149

    I haven't seen much improvement in compression using third party backup tools compared to the native SQL Server 2008R2 backup compression.

    Some of the third party backup tools have features you might find desirable, like backup encryption, but if compression is your main need, just stick with the native backup compression.

    Of course the tool vendors may have a different opinion :-), but make sure that you test backup compression using your actual database before buying a tool. I think the main ones have free trials available.

  • January 26, 2015 at 9:41 am

    #1773165

    Michael Valentine Jones (1/26/2015)

    I haven't seen much improvement in compression using third party backup tools compared to the native SQL Server 2008R2 backup compression.

    Some of the third party backup tools have features you might find desirable, like backup encryption, but if compression is your main need, just stick with the native backup compression.

    I don't work for Redgate or benefit in any way from sales of their products. I have had cases where Native SQL Backup compression did not yield great results but Redgate Backup Pro did. It's been a couple years since I have used it but I remember being very impressed. They have several different compression algorithms whereas SQL 2008R2 compression gives you the option (if I remember correctly) of "Compress" or "Do Not Compress".

    I suggest the OP download it (you can get it free for a trial period) and try it themselves.

    Also, the object-level restore option is killer too.

    Redgate Backup Pro Compression Levels

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • January 26, 2015 at 12:56 pm

    #1773213

    One advantage of the third-party tools is the ability to stripe the backups into a single-compressed file. Striping this way can reduce the time it takes to actually perform the backup.

    I use Quest Litespeed on my larger systems and it works very well - not only reducing the overall backup size but also reducing the time from 3.5 TB to an 800GB backup file in about 3 hours.

    Litespeed also has variable levels of compression - where you can trade off performance for greater compression or speed for less compression. Litespeed can also be installed in a way that overrides the native backups - which I haven't tried myself.

    To the original post - if you are using native compression and not seeing any results, you may be in a situation where the data stored in that database is not compressible. If so, using a third-party tool won't change that...

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • January 26, 2015 at 2:01 pm

    #1773231

    There's also SQL Safe by Idera, it works pretty good and has the "instant restore" piece built in. Compression with SQL Safe and the Redgate tool are both much better than native, and they both have aes 256 encryption built in which is a very nice to have feature.

    https://www.idera.com/productssolutions/sqlserver/sqlsafebackup

    Are you running your backups to a de-duplication device?

  • January 27, 2015 at 9:13 am

    #1773413

    ramana3327 (1/26/2015)

    2008r2 enterprise edition

    run the following against one of the backup files and check the value returned in the compressed column

    restore headeronly from disk = 'drive:\path\path\backupfilename.bak'

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 11, 2015 at 7:21 am

    #1776926

    Thank you.

    TDE enabled on that database.

    Is TDE will effect the compression size?

  • February 11, 2015 at 10:18 am

    #1777007

    ramana3327 (2/11/2015)

    Thank you.

    TDE enabled on that database.

    Is TDE will effect the compression size?

    Absolutely!

    From Understanding Transparent Data Encryption (TDE) (the Considerations section) on Technet:

    Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended.

    Even a third-party compression application is unlikely to compress the backups much, if at all.

  • February 12, 2015 at 10:52 am

    #1777306

    Thank you

  • February 13, 2015 at 6:06 pm

    #1777644

    I use LiteSpeed for over 10 years and quite happy with it. One of our databases is about 350GB and the compressed backup size is about 30GB. Of course it all dependent on compression ratio, type of data you store in the database... But you have the idea.

  • February 13, 2015 at 7:51 pm

    #1777647

    As previously mentioned, TDE databases don't compress much.

    With that in mind, what are your 3 largest tables and what are they used for? I'm betting that at least one of them is temporal and mostly static in nature like an Audit table or an InvoiceDetail table (for example).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply