SQL 2008 - Backup Compression feature

All these days we spent time and resources on identifying the best SQL Backup utility that has compression mechanism. Finally we heard that MS-IT uses SQL Litespeed for its operations and most of the people out there bought LiteSpeed, those who couldn't afford settled with other softwares like Red-Gate etc. Put some time in implementing it with custom code to fire backup commands and to clean the old backup files as these tools are not tightly integrated with SQL Maintenance Plans.

In SQL-2008 MS has introduced "Compressed Backups" - yes all your energy is (that we talked about in above paragraph) is wasted. Instead of saying wasted just assume that you got some knoledge and working of some product like LiteSpeed and Red-Gate etc. Also this "compression of backups" feature is available in "Enterprise Edision" only. And certainly you can resore the "Compressed backups" onto other edition like Standard, & Developer etc.

Now, Lets look what actually is this new feature in SQL 2008 is doing. Ok - it’s the same compression technique, basically it will compress the data in the memory and dump it into a file in that format which reduced the disk space used, plus the time taken to complete the backup - b'cos the disk I/O is less in this case. I don't see encryption feature in here which is mostly available with third party tools. And the other point is that there are couple of restrictions on the having compressed backups, which are,

• This feature is only available on SQL 2008 - "Enterprise Edition"
• Compressed and Un-compressed backups cannot be in the same media set (i.e SQL 2008 - "Enterprise Edition" will understand the media set, however the older versions of SQL Server will fail to read the media set.)
• NTbackups cannot share a tape with compressed SQL Server backups (I am NOT sure why - may be bc'os of the above reason)



So how do we enable this feature on SQL 2008 ? - This is done via sp_configure system stored procedure. By default when instance is installed this value is set to OFF (0), so in order to enable it you need to run

USE master
GO
EXEC sp_configure 'backup compression default', '1'
RECONFIGURE WITH OVERRIDE

Also, here is the command that you can use in case if you don't want to enable the compression at server level, but want only for specific backup command then,

BACKUP  DATABASE
        AdventureWorks
        TO DISK='Z:\SQLServerBackups\AdvWorksData.bak'
WITH   
        COMPRESSION

After reading all this I was curious to think - is there any change in backupset system table in MSDB. And yes, there is a new column in backupset table named  "compressed_backup_size". The value in this column will tell you the byte count of the file (compressed) stored in the disk. If this value is NULL then that backup set is NOT_COMPRESSED.

Performance wise - Certainly compression demands extra CPU cycles, good that MS has provided two more counter to help use deal with performance related to compression. The counter are Device Throughput Bytes/sec (in SQLServer:Backup Device object) and Backup/Restore Throughput/sec counter (in SQLServer:Databases object) with these two I think we should be able to deal with perf. Issue related to backup process involving compression.

Compression Ratio - This is something that depends on couple of factors like is the data repeated too much in that case you can achieve high level of compression. As you migh know binary data cannot be compressed much so in those situation as well as in case of encrypted data character repeation happen very less, so you get less compression ratios.