Last night, after a lot of preparation, I finally flicked the switch on the backup compression solution I’ve been implementing. The results were dramatic but in a good way:
- backup jobs 60% faster
- 50% disk space saving
- Reporting & BI build 30% faster
- Reporting & BI ready 3 hours earlier than before
Each night my SQL Agent job runs a full backup of the live databases and copies the backup files to a file server folder from where they are later backed up to tape. The last step kicks off another job on my reporting server which restores from the file server’s copy of the backup files and then rebuilds the data warehouse database and Analysis Services cubes. The whole process was only just finishing in time for the start of the business day. This morning it was all over by 04:15.
Backup compression substantially speeds up backups and restores by using more CPU and RAM and less disk I/Os. Because the disk is the bottleneck you get a faster run time and a smaller backup file. SQL 2005 doesn’t have native backup compression, unlike SQL 2008, so you need a third party utility. I used Idera’s SQLSafe Freeware Edition. I have no connection with Idera. I simply spotted the product when I was looking into how much compression might cost. The price was great ($0!), so I tried the product and liked it.
And it all worked first time!
David Wimbush 