OK, we’ve had the Christmas break and if you are anything like me you will need some serious exercise strategies for the New Year. Soon we will all be making our New Year resolutions, joining health clubs or purchasing Wii Fit to supplement the soccer, football, Super Mario or Guitar Hero games we’ve been playing over the holidays. Whoops! This is a Microsoft blog so I should be talking about the XBox 360. Actually, Guitar Hero is available on both platforms and being slated as a fitness program depending on how you play it. Some of those guitar solos can give you quite a workout! Bit of a stretch, I know. Here’s another stretch: since we’ll be looking for some serious personal “compression” strategies for 2009, let’s take a look at SQL Server 2008 Backup Compression.
We looked at Data Compression in an earlier blog that gave us space savings and performance benefits together. Also in SQL Server 2008, is another compression technology: Backup Compression. It is important to realize that this is a separate technology to Data Compression but both can be used together to yield more storage and performance savings. Since Data Compression reduces the number of Data Pages needed, naturally the backups will take less space and time too. But there’s more to it than that.
At the SQL PASS conference last month in Seattle, we were treated to a testimony from the ServiceU.com folks with regard to SQL Server 2008 Backup Compression. They are into the Event Management and Ticketing Software business out of Memphis, Tennessee. One example was a 300GB database being backed up to a 45GB backup file. That’s 85% compression. Not only that, the backup took half the normal time. Not bad. So what’s the trade off? It does take more CPU cycles during the backup, so that needs to be considered when other processes are running concurrently. And of course, you will need the Enterprise Edition to create a compressed backup; however, any edition can be used to restore a compressed backup as long as it is SQL Server 2008. Since the backups are smaller, the restore times are reduced too.
You can set the default backup compression option at the server-level using sp_configure or using SQL Server Management Studio. The default is set to 0 (OFF) for new installations. Setting the default to 1 (ON) will ensure that all backups will use compression unless otherwise specified. You can always override the default on a backup-by-backup basis using the WITH COMPRESSION or the WITH NO_COMPRESSION option on the T-SQL BACKUP statement or using the new drop-down on the Backup Options tab in SSMS. My tests with AdventureWorks2008 showed a full backup went from 185MB down to 45MB compressed. Interestingly, I saw no compression if I used the new transparent database encryption option on the database so that’s worth noting. Security has its price, I guess.
To determine the accurate compression ratio (I thought that was a car engine thing?) you can use the following T-SQL statement:
SELECT backup_size/compressed_backup_size FROM msdb..backupset;
That showed that my AdventureWorks2008 backup had a compression ratio of 4.15.
Another interesting thing is that you cannot mix and match compressed and uncompressed backups on the same backup media set. Even if you use the overwrite option (INIT) on a disk backup, it will fail if the previous backup was not the same compression option. I had to use the FORMAT option to completely reformat the backup set. In my primitive tests, the backup time went from 20 seconds down to 12 seconds. The restore time also came down from 19 seconds to 14. Good news all round.
In terms of optimizing the performance gains, Microsoft advises testing with multiple backup devices. For instance, the SQLCAT.com team tested a 1.27TB database with 12 database files on 12 LUNs with 8, 16, 26 and 32 backup devices and found that the optimal number was actually 16. The backup over their 8 x 1GB Ethernet network took 24 minutes with a throughput of 826MB/sec. Very impressive. Varying the BUFFERCOUNT parameter can also yield some improved results although the higher the number of buffers the more memory is needed. The Microsoft tests above used BUFFERCOUNT=512. If you want to do your own performance tests without having to waste disk space, you can use the DISK = ‘NUL’ option on the backup statement. That tells SQL Server to throw away the backup once made but the performance and compression figures are still valid.
But that’s kind of like playing Guitar Hero while sitting on the couch. You will need to actually exercise for real to get the benefits of compression. Happy New Year!
Cheers
Brian
Recent Posts:
SQL Data Compression is no Scrooge
Kilimanjaro or Krakatoa East of Java?