SQL Server 2008 Backup Compression
With SQL Server 2008 Enterprise Edition, you can create compressed backups. Compressing your database backups has both its advantages and disadvantages – the tradeoffs. Most of its advantages are tied in with the reduction of the amount of data stored on the disk and with the speed of the backup and restore processes. It may have adverse impact on CPU usage, though. One suggested workaround on the CPU usage problem on backup compression is to run the backup under a user login and limit that login CPU usage by using Resource Governor.
Let’s see how backup compression works in SQL Server 2008.
Native Backup Compression
I have here a 507MB database called “Compression_Test”. What we’re going to do is back it up first without compression and compare the backup file size later with a compressed one.
That 507MB database took about 1.444 seconds to process and gave us about 16MB backup file – processing about 11MB of data per second.
Now, let’s backup the same database (507MB) with the built-in compression mechanism of SQL Server 2008.
As you can see, it only took about 0.935 seconds – at rate 16.986MB of data processed per second. This also saved us some storage as the the backup file is only about 3MB (compared to 16MB+ without compression).
Let us see if the compressed backup file is “really compressed”.
Third Party Backup Compression Tool
It was announced yesterday at twitter via Brent Ozar ( Blog | Twitter ) that Quest Software LiteSpeed Engine for SQL Server has been released. A trial version is also available if you want to give it a shot.
LiteSpeed Engine for SQL Server is a ” driver based technology that allows database backup administrators to seamlessly take advantage of LiteSpeed’s compression and encryption capabilities without having to modify existing SQL scripts.” You have to configure it after installation to use it as the backup engine for SQL Server 2008 (built-in compression). A video tutorial on configuring and overview of LiteSpeed Engine for SQL Server can be found in this video by Brent Ozar.
I don’t really have a huge database to test except that same 507MB database we’ve used to test compression in the above examples.
The backup process using LiteSpeed Engine for SQL Server shows an improvement if compared to the native backup compression of SQL Server 2008. I was able to backup the 507MB database faster in about 0.886 second which is about 17.926MB/sec. You can just imagine just how much this would make a difference if your backing up terabytes of data.
We’ve seen that, performance-wise, LiteSpeed Engine for SQL Server is more efficient. But the size of the backup file is a little bit different story.
The LiteSpeed Engine for SQL Server processed a backup file that is slightly bigger than that of the native backup compression of SQL Server 2008. Reconfiguring it to compress at Level 2 decreased the backup file size dramatically, even smaller than the native compression.
These tests show that LiteSpeed Engine For SQL Server clearly delivers its promise of “reduced backup times”and has truly a “zero impact to existing backup strategies” (it does not mark the backup file as “compressed”, though).
Summing it all up, the native backup compression of SQL Server 2008 is good enough for backup purposes. But if speed and backup times are critical to your operations, then using third party backup compression tools, such as the LiteSpeed Engine for SQL Server, should be considered.