Monthly Archives: September 2009

Backup compression on SQL 2005

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!

Column length in SQL 2005 Analysis Services cube

I’ve been dark for a while as I’ve been on holiday in Turkey on the ‘Tourqoise Coast’. Great people, hot and sunny, amazing diving water, thoroughly recommended.
Anyway. I had a new one today with a cube. A few weeks ago, in the underlying database, a varchar column’s max length was increased from 50 to 100. The cube continued to build fine until somebody created a new row with a value longer than 50 characters. The cube failed to build with an error about binding. Annoyingly, refreshing the data source view wasn’t enough. I had to go into the dimension and actually update the properties there too.
Worth bearing in mind.