Backup Compression in SQL Server 2008

As a DBA who services numerous customers and clients, an issue that crops up relatively often is the need for more disk space. As an organization you do have options:

1. Trim your data
2. Buy new disks
3. Truncate your data (Yeah right!!)

For most organizations these are truly not options at all. With the release of SQL Server 2008, one feature that can immediately address this concern with minimal side effects is backup compression. Here are some stats collected against the adventureworks database:

Uncompressed

Processed 23016 pages for database ‘AdventureWorks2008′,
file ‘AdventureWorks2008_Data’ on file 1.

Processed 36 pages for database ‘AdventureWorks2008′,
file ‘FileStreamDocuments’ on file 1.

Processed 1 pages for database ‘AdventureWorks2008′,
file ‘AdventureWorks2008_Log’ on file 1.

100 percent processed.

BACKUP DATABASE successfully processed 23053 pages in 10.842 seconds (16.610 MB/sec).

Compressed

Processed 23016 pages for database ‘AdventureWorks2008′,
file ‘AdventureWorks2008_Data’ on file 1.

Processed 36 pages for database ‘AdventureWorks2008′,
file ‘FileStreamDocuments’ on file 1.

Processed 2 pages for database ‘AdventureWorks2008′,
file ‘AdventureWorks2008_Log’ on file 1.

100 percent processed.

BACKUP DATABASE successfully processed 23054 pages in 5.975 seconds (30.142 MB/sec).

The compressed backup took 4 seconds less and was 75% smaller.

Think about the potential gain once this technology is applied to vldb (very large databases). There is an extreme amount of potential savings in disk space just by utilizing backup compression. With the release of SQL Server 2008 R2, not only are these features available in the Enterprise Edition but these features are also available in Standard Edition.

Thanks,

James Shropshire MCDBA, MCITP SQL Server