Should We Compress SQL Server Backups? 3


 

Last week we covered five reasons why log shipping should be used.  I got a great question that I thought should be answered here in a short blog post.  The question is “how do you configure transactional log shipping to include compression when taking the transactional log backups?”

The Missing Question

First, a better question is “should we be compressing our SQL Server Backups?” In a second we will address log shipping, but first I wanted to just focus on compressing backups. The answer is “it depends” (typical DBA response).  There is CPU overhead for utilizing compression. We will not focus too much on this here as you can enable resource governor to limit CPU usage for your backups.

Today, we will focus on capacity and misleading storage saving results due to enabling backup compression.  Looking at a traditional DBA role, you might have exposure to view your server, drives, and the size of the backups. Therefore, taking a compressed backup leads you to likely see less storage being used for the backup file compared to other backups of the same database.  This is typically the goal for backup compression to reduce storage space consumed by backups.  

Another question you should be asking yourself is, “What storage system is being used for your SQL Server backups?” For example, storage access networks (SAN) might have its own compression and native SQL Server backup compression might hurt the impact of the SAN’s compression, which could cause more RAW storage to be used. Therefore, there really isn’t a silver bullet to always use SQL Server backup compression in every environment.  You need to understand if there is any compression built into the storage used for your backups, and understand how backup compression impacts the storage system before deciding to utilize backup compression.

Compress Log Shipping Backup

Okay, now that we got our disclaimer common real-world mistake out of the way.  Here is how you would enable backup compression for transactional log shipping.

You can access Log Shipping Settings from Database Properties

 

Log Shipping Backup Settings on Primary

Log Shipping Backup Settings on Primary

 

Here are Log Shipping backup compression options.

Here are Log Shipping backup compression options.

 

Enabling Compression with T-SQL Backups

Enabling backup compression with T-SQL is as simple as just adding the compression option.  The following is a common example of backing up a database with compression.

 BACKUP DATABASE [LSDemo] TO DISK = N'c:\somewhere\LSDemo.bak' WITH COMPRESSION 

Enable Compression by Default

If all of your backups for an instance of SQL Server go to storage where utilizing native SQL Server compression provides improvement, then you can consider enabling compression by default. Therefore, all backups taken on this instance of SQL Server would then use backup compression by default unless another option was given during the T-SQL execution of the backup command.

EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

 


Leave a Reply

3 thoughts on “Should We Compress SQL Server Backups?

    • John Sterrett

      Hi Fabricio,

      Sure thing I will give SQL Server 2008 R2 a spin and let you know about the results. Just being curious, have you tried the current SSMS 17 and if so do you see the same thing?

      Regards,
      John

    • JohnSterrett Post author

      Hi Fabrico,

      The GUI in SSMS for backups doesn’t look any different than what was shown above. The dropdown to set backup compression is there and you can pick between enable, disable or default for backup compression. You can alter log shipping after its created using the SSMS GUI and I also see backup compression working as expected while I add data. I don’t see any problems with selecting or changing backup compression on SQL 2008R2.

      Also, you use sp_change_log_shipping_primary_database to change backup compression by using the @backup_compression parameter. This was also tested flipping between enabled, disabled, default setting and I saw same results as expected with SSMS GUI.

      Regards,
      John