SQL Server 2008 Backup Compression
With SQL Server 2008, Microsoft introduced native backup compression. Prior to SQL Server 2008, the only way to get real-time backup compression was to buy a third-party tool. There were – and still are – several third-party tools on the market. I have used a few of them, and they each have their pros and cons. But, this post is not to debate the merits of the different tools – it is to discuss the native functionality that ships with SQL Server 2008.
If you’ve ever used an off-the-shelf compression utility (such as WinZip) on a SQL Server native backup, you’ve probably noticed that they usually compress down quite well. I’ve seen certain backups compress down 75% or more. The problem with this method is that you need a lot of disk to make it happen. If you have a 10GB database, a full backup will take 10GB of disk space. If that backup achieves 75% compression, if will end up taking up 2.5GB of disk space. The problem is that you really need 12.5GB of disk to get to a 2.5GB compressed backup (10GB for the initial backup then an additional 2.5GB for the compression). Now, increase the size of your database by 10 or 100 times. Do you really have an extra 1TB of disk lying around so that you can end up with a 250GB backup? I certainly don’t.
This is where SQL Server’s native backup compression can really help. The compression happens as the data is being written to disk, not after it is there. So, if you have a 10GB database that will achieve 75% compression, you only need 2.5GB of disk to perform the backup – not 10GB like you would without compression.
From my experience, the native backup compression performs quite well, although your mileage may vary. You will have additional processor overhead above and beyond the overhead required for a standard backup, but in my testing the overhead was comparable to what I was getting with several of the third-party tools.
I don’t want it to sound like I’m against the third-party tools. I still use a third-party backup utility where it makes sense. Remember that the native backup compression won’t fit all situations – most of the third-party tools include encryption and other features that are not included in the native solution – and there isn’t a nice management console like what you get with the third party tools. But, if you can live without these added features, you might be able to save some money by using the native backups.
The best advice that I can give you is to give it a try and gather metrics to compare your third-party tool to the native compressed backups. Look at the backup time, restore time, processor utilization and final compressed size. If your experiences are similar to mine, I am guessing that you will be quite impressed.
Chuck