BizTalk Server 2010 Backup Compression

BizTalk Server 2010 now supports compression for its SQL Server database backups. The benefits are obvious: less space needed for the backup files, fewer I/O operations for the backup and restore, faster backup and restore, less network usage to transfer the files to your disaster recovery site, etc. One of the main drawbacks is higher CPU utilization.

Everyone should be aware by now that for BizTalk Server database backups and disaster recovery SQL Server Agent job ‘Backup BizTalk Server’ should be used instead of any other backup methods. What has now changed in BizTalk 2010 is that a new step in the ‘Backup BizTalk Server’ job has been created called ‘Set Compression Option’:

This job step calls a stored procedure named sp_SetBackupCompression on the BizTalk management database (BizTalkMgmtDb by default) to set the value on the adm_BackupSettings table. The backup stored procedures called by the other job steps will check this value to either enable or disable compression during backup. The stored procedure has only one parameter: @bCompression. Setting it to 0, the default value, tells BizTalk not to use compression. Setting it to 1 enables backup compression. Given that there are consequences, especially around CPU usage, and that not all versions of SQL Server support it, it is understandable that Microsoft have left the setting default to 0.

This is the size of the tracking, management and message box databases on my environment:

When compression is set to 0 (exec [dbo].[sp_SetBackupCompression] @bCompression = 0), this is the size of the full backups created by the ‘Backup BizTalk Server’ job:

Note that the message box database backup above is much smaller than the message box database file size because it is pretty much empty space.

When compression is set to 1 however (exec [dbo].[sp_SetBackupCompression] @bCompression = 1),  the full backup sizes are much smaller when run against the exact same database data files sizes:

A few things to note:

• I only show the full database backup file sizes above, but the setting also enables compression on the transaction log backup files
• In SQL Server 2008 the backup compression feature requires Enterprise Edition. In SQL Server 2008 R2 it is supported by Standard edition and higher
• Backup compression increases the CPU usage significantly. Consider this, test and decide if the benefits are worth it
• The instructions to configure the destination system for log shipping and restoring the database and log backups remains the same http://msdn.microsoft.com/en-us/library/aa561125(BTS.70).aspx. The SQL engine realizes it’s a compressed backup and decompresses it on restore
• If you are running BizTalk Server 2009 over SQL Server 2008 Enterprise Edition you can enable the default of the server to be with compression http://msdn.microsoft.com/en-us/library/bb677250.aspx so the BizTalk database backups (and other backups from the server) would be compressed as well.
• As always, test the changes on a test environment first

Regards,

Thiago