There are quite a few products out there that help you compress your backups. Acronis, RedGate, and many more. This got me to thinking about whether or not I should be compressing my own backups. I know I’m not going to get any additional budget for it this year, so I’m not going to invest the time into trying out 3rd party tools to compress my backups. I’m going to try out the built in compression options in SQL Server 2008R2.
I know by the very definition of compression that I was going to save space on my backup LUN, but I didn’t know how much I was going to save. So with a healthy baseline (all my databases had been backed up using the standard method for the previous two days), I switched my maintenance plans over to enable compression on both my full backups and my transaction log backups.
I ran the first job manually, I wanted to be sure it ran, and if there were any issues I wanted to be standing by! I didn’t want to get an alert page. So during our standard maintenance window, I ran the new maintenance plan. I was actually surprised by the results. With 25 databases being backed up, I saw all the backups shrink to somewhere between 14.7 % and 27.16% of their original size. Overall I was able to compress the backups to 24.17% of their original size. This means my current storage of 132GB for backups will now go four times as far! That’s the better compression than you’d find on the detention level!
As a result, I’m planning on upping my on server storage from 24 hours to 48…at least until I have to give up that space to other needs. By the way, after backups, I use a small Powershell script to copy pull the backups down to our QA server so we can test updates against day old data. It’s a huge step forward from the previous environment where you really didn’t know how old your test data was!
After seeing huge savings in my storage space, I looked at the amount of time the backups would take, now that I’m compressing them. To my surprise I was down from 21:52 to 13:43! I actually expected it to take longer. But after several run throughs my findings are consistent, each run is at least 5 minutes quicker than the uncompressed form!
Now that I’ve shared the pros of the compression, I want to share with you a word of warning. Compression does have an additional CPU cost. In my environment I saw slightly more than a 10% increase in CPU usage according to SQL Sentry Performance Advisor. While in my case it’s not a huge increase, 10% could be the difference between acceptible and an alert being fired.
Before turning on compression on your production boxes, make sure you have a period when CPU is LOW, and access to the server is LOW, then test this option. In fact, if you have access to a test server that mirrors your production server (at least in terms of hardware), test compression there first…then try it on production!
I think I’m going to put this down as a best practice, the only time I will run without it, will be the server is nearing it’s processor limit. And if that’s happening, I should be well on my way to resolving the high CPU usage, through moving some of the load off that server, or upgrading the server to handle that load.
So, what do you think about making compression a best practice? Do you compress your backups now? If so, are you using the built in tools, or are you running a 3rd party tool? I’d like to find out more about how you do things. Feel free to share your findings below!