Log Shipping Compression

  • Hello, does anybody know if any versions of SQL have the ability to compress log files before shipping. The plan was to log ship from the local data centre to a SQL instance in EC2 (Amazon Cloud) but due to the high latency we get from here (NZ) to Amazon (Singapore) a large file takes an unrealistic time to copy. Therefore compression would be a help.

    We are currently using SQL 2008 STD.

    I think I have read the SQL 2008 Enterprise editions have this feature but I am unsure if SQL 2008 R2 STD or SQL 2012 STD also have this feature as I think normal backup compression was added to these additions....thanks

  • sql 2008 enterprise has this feature. From Sql 2008R2 it was available in the standard edition.

    If you are on 2008 standard there are a number of third party tools that will provide backup compression for you.

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

  • thanks, and as far as you know the backup compression on R2 STD and 2012 STD compresses log files created from a standard log backup regime or via a log shipping backup regime?

  • It is configurable. Run sp_help_log_shipping_primary_database against master on your primary instance and look for the backup_compression column

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Given that you are shipping across the internet I would encrypt my backup too. that you cannot do with SQL Server built-in stuff. Either get third party tool (which may not be functional on AWS instance) or use 7zip et al to handle compression/encryption and build a simple process to handle decryption/compression in cloud (should be doable, but not haven't tried it).

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 6 posts - 1 through 5 (of 5 total)

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