Excellent Results With SQL Server Native 2008 Backup Compression

I upgraded one of my remaining large databases from SQL Server 2005 (on Windows Server 2003 R2) to SQL Server 2008 CU2 (on Windows Server 2008) this past Friday, with no issues. This is a production database, so I could not take a long outage for a backup-restore sequence. I used SQL Server Database Mirroring to mirror from the old SQL Server 2005 instance to the new SQL Server 2008 instance. I had the mirror running for a couple of weeks with no problem.

On Friday night, we changed the mirroring session to High Safety mode, then failed over from SQL Server 2005 to SQL Server 2008. After about 15 seconds, the Principal came up on the SQL Server 2008 instance, with the mirror status going to Suspended. This was expected, since this type of failover is a one way trip (you cannot failover back to SQL Server 2005). Performance on the new instance and operating system is noticeably better than on SQL Server 2005.

One immediate benefit that I have seen is the effect of SQL Server 2008 Native Backup Compression. With SQL Server 2005, this particular database took 93 minutes to complete a full backup, and the backup files ended up being 363GB in size. With SQL Server 2008 Native Backup Compression, the same database can finish a full backup in 51 minutes, and the backup files are only 76GB in size.  That is an 80% reduction in backup size and a 46% reduction in backup time, which is pretty impressive!

Both instances are running on identical hardware. Backup compression is not completely free, since you do incur some increased CPU utilization during the backup. In my case, it is about 5% more CPU (with a four socket, dual-core server). Going forward, I see CPU continuing to be a much cheaper resource than I/O capacity and storage space, so backup compression is a big win. One caveat is that backup compression is an Enterprise Edition only feature (although you can restore a compressed backup with any edition of SQL Server 2008).

 

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s