One of the many new features in SQL Server 2008 is the ability to perform compressed backups of a database. Traditionally, that feature has been offered by several third-party vendors, but now it is a native tool. While SQL Server 2008 data and backup compression does not provide as many bells and whistles as third-party programs, the feature will be of great interest to companies with very large databases – they'll perform backups and restores much faster than with native backup methods in earlier versions.
Before we get into details, I'll discuss the benefits of backup compression. The primary benefit is the speed of the backup. Even though data compression needs a substantial amount of CPU processing, most databases will back up faster because a compressed backup file is much smaller than a regular one and requires fewer disk I/O operations. The second benefit, and the more obvious one, is the reduced size of the compressed file. This results in many advantages: You can keep more copies; network backups will run faster; you will need fewer tapes and so on.
Creating a compressed backup in SQL Server 2008 is easy -- you can back up with T-SQL or just include the word COMPRESSION in the WITH clause. If using SQL Server Management Studio, just select the "Compress backup" option on the Options tab in the Backup Database dialog. The following example backs up the AdventureWorks database:
-- Compressed backup
BACKUP DATABASE [AdventureWorks] TO DISK = N'D:\SQL Server
Backups\AdventureWorks Compressed Backup.bak'
WITH NOFORMAT, INIT, NAME = N'AdventureWorks-Full Database
Backup',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
What can you expect from using backup compression? I did some benchmarks on the sample AdventureWorks database and was pretty pleased with the results. A regular backup took about 25 seconds to execute, while a compressed backup took only about ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
14 seconds. The size of the backup file was 188 MB for a regular backup and 45 MB for a compressed backup. This translates to almost 50% reduced execution time. In addition, the size of the backup file takes up only 25% of the space required for the regular SQL Server backup, which reduces data storage needs. These results vary from database to database. You will get greater compression ratios if your database contains a lot of text data, such as char and varchar columns. While some third parties can offer you better results, the backup feature that ships with SQL Server 2008 is pretty impressive -- and useful.
The default setting out of the box is to use no compression (NO_COMPRESSION directive in T-SQL) so your backups will not be compressed. You can change the default option to use compression by running the following stored procedure:
use master
Go
EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
After executing this code, all backups will be compressed unless NO_COMPRESSION is used explicitly. Whether that's a suitable setting or not depends on your organization and your staff. I will discuss some of the caveats of backup compression later in the article.
There are a few more things you should be aware of before deciding if compression is suitable for your environment. The main issue to watch for is CPU usage during the backup and restore. While the reduced I/O certainly contributes to a smaller file size and faster overall backup time, it is done at the expense of increased CPU. I observed on my test system that CPU spiked to about 90% while running a compressed backup and to 60% when restoring the same backup. Just to give you a point of comparison, when I was doing the same with an uncompressed backup file, the CPU during backup was around 20% and during the restore process, it was around 15%.
Should you be concerned about this? It depends. Most databases I've seen – including the ones with heavy usage – did not experience CPU bottlenecks when indexes were properly tuned. Instead, the disk I/O is usually the first place where the server starts slowing down. I suspect the same would apply to most of your databases, especially these days with multi-processor, dual-core machines. You should definitely monitor your environment and make sure your processors can handle the load, but in most cases that should not be a problem.
The other problem with this feature is that it is enabled only in the Enterprise Edition of SQL Server 2008. Side note: Sometimes Microsoft changes the feature support before the final release so there is a slight chance that this feature might be supported in other editions. However, you can restore a compressed backup in all other editions of SQL Server 2008. That functionality might come in handy if you need to bring a production database to another environment.
As you can see, backup compression can provide many benefits when used properly, and this feature makes another compelling reason to consider upgrading to SQL Server 2008 as soon as possible.
ABOUT THE AUTHOR:
[IMAGE]Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He regularly contributes SQL Server articles to Visual Studio Magazine, SQL Server Magazine and other technical publications and presents at user groups and conferences in the U.S. and Canada. He is an active member and volunteer for the Professional Association for SQL Server. Roman also serves as Tech Chair for the SQL Server track at the annual DevTeach conferences in Canada and is president of the Vermont SQL Server User Group.
MEMBER FEEDBACK TO THIS TIP
Do you have comments on this tip? Let us know.
