SQL Server Backup Compression Shootout

Editor's Note: Idera disagrees with the findings of this comparison review, and has written a letter to SQL Server Magazine in response. You can read Idera's letter to the editor by clicking here.

The market for SQL Server backup compression tools has become crowded over the past few years, ever since Quest Software’s LiteSpeed for SQL Server appeared. Prior to the availability of compression tools such as LiteSpeed, many DBAs were using methods such as ZIP compression on their SQL Server backup files. But such compression methods are inefficient and don’t eliminate the necessity of making an original, large backup; they also consume nearly double the disk space (and time) as the ZIP file is being generated. Today, databases are not only much larger than they’ve ever been before, but they’re also more mission-critical. How do you back up a 500GB, 1TB, or larger database effectively and quickly? Then, how do you copy that large backup to another location rapidly? The short answer is that you can’t. Both backup and copy operations can take a considerable amount of time when you’re dealing with hundreds of gigabytes or terabytes.

The benefits of compression are obvious: A backup file takes up less physical disk space (reducing storage costs), and when archived needs less space on tape or in your vault. Smaller files mean faster copies and quicker restore times when you’re pulling a backup in a disaster-recovery scenario. Another reason for the increased importance of backup compression tools is compliance with regulations such as the Sarbanes-Oxley (SOX) Act and the Health Insurance Portability and Accountability Act (HIPAA): Companies might need to keep more data around longer, which means backups also get larger. Although the cost of disks has come down over the years, disks can’t solve all the storage problems related to larger data sets, which require even more disks (and attendant increases in hardware costs), as well as increases in related costs such as for air conditioning in server rooms that house growing numbers of disks and servers.

Backup Compression and You
A backup compression tool’s main purpose is to compress a backup as it’s being made and give you the ability to restore it. Individual tools add their own feature sets on top of that basic functionality, but all integrate with SQL Server in different ways, and all are relatively inexpensive. Choosing the program that’s right for you comes down to evaluating features, effectiveness, cost, and ease of use. Ask the following questions as you evaluate compression tools:

• Are there backup problems that a compression tool will solve, such as the inability to retain backups on disk or a lack of disk space for backups?
  
• Are there immediate cost savings that you can realize with compressed backups (e.g., when the internal storage team is charging for storage space by the gigabyte)?
  
• Are there problems with your current disaster recovery plan in regard to backups that smaller, more agile backup files could help solve?
  
• Does the tool under consideration offer anything beyond compression that you can use in your environment, or is compression good enough?
  
• Does the licensing model cover the number of SQL Server servers and instances in my environment?
  
• How will changing the way backups are done affect your environment? How long will changes take to implement?

In this article, I compare three popular SQL Server backup compression tools: HyperBac Technologies’ HyperBac for SQL Server, Red Gate Software’s SQL Backup, and Idera’s SQLsafe. (Table 1 lists the products’ feature sets.) You might be wondering why LiteSpeed for SQL Server isn’t included here. Because the newest version of LiteSpeed for SQL Server (version 5.0) wasn’t complete in time to be tested along with the other products, Quest opted not to participate in this comparison. (Quest is adding new features to LiteSpeed for SQL Server, including virtualized backups, support for non-SQL Server platforms, and policy-based management. SQL Server Magazine will review the updated LiteSpeed for SQL Server in a subsequent issue.) To read my take on how SQL Server 2008 will change the backup compression landscape, see the Web-exclusive sidebar “Backup Compression and SQL Server 2008,” InstantDoc ID 98179.

HyperBac for SQL Server
HyperBac Technologies’ HyperBac for SQL Server is the new kid on the block, hitting the market in 2007. In its design, HyperBac takes a novel approach: Nothing is installed in SQL Server. In fact, the tool has no real integration directly with SQL Server because it doesn’t integrate with SQL Server’s Virtual Device Interface, as most backup software does. HyperBac for SQL Server installs at the OS level and intercepts calls (filtering for SQL Server calls) that SQL Server makes to the OS regarding writing to disk, then streams the file to the file system, cutting out the proverbial middleman. HyperBac Technologies worked with Microsoft to ensure that all of this works properly.

Installing HyperBac for SQL Server is a breeze: The install package is tiny and very fast. All it installs are the required services and an applet in the system tray, which makes the service easy to enable or disable. The applet also handles any configuration. This is refreshing in its simplicity.

Does intercepting those calls at the OS really work? Yes, and it works well: HyperBac for SQL Server is transparent once enabled. For a DBA, this is a dream tool: Unlike the approach most vendors take, in which you must use either the vendor’s interface or extended stored procedures, HyperBac doesn’t require DBAs to modify one shred of code or change how they do things. HyperBac for SQL Server intercepts the native SQL Server backup and restore commands, which lets DBAs continue to use their accustomed method for backups and restores and removes the need for HyperBac to provide a tool for managing backups and restores, command-line tools, or extended stored procedures. The tool works as promised, seamlessly and transparently integrating with existing SQL Server functionality. Because the only thing you need to do is install HyperBac for SQL Server and enable the service, you can be up and running in minutes.

Like the other tools in this comparison, HyperBac for SQL Server can encrypt backups, supporting as much as 256-bit Advanced Encryption Standard (AES). But the product sports a unique feature—the ability to back up to and restore from a ZIP file. When you employ this feature, the ZIP archive contains the standard .bak file, but the file is made with the same speed and compression of a standard HyperBac for SQL Server backup. This means that in five years, if you need to restore the database, the backup file will be in a standard format, so you won’t have to search for a copy of HyperBac for SQL Server to restore the database—you’ll need only to unzip it from the archive. I tested this functionality, and it works like a charm—it’s one of the nicer options I’ve seen in some time from a backup utility. (Keep in mind, though, that decompressing a large .zip file requires a tool like WinZIP or WinRAR and can take many hours—decompressing the test database for this review took 21 hours.) HyperBac for SQL Server also includes a utility to convert HyperBac backups into a standard SQL Server format that a SQL server can use if HyperBac for SQL Server is not on the server or the backing-up-to-ZIP feature wasn’t employed.

HyperBac for SQL Server also can compress bcp and DTS and SQL Server Integration Services (SSIS) tasks that involve writing to and reading from files. This is another feature unique to HyperBac.

HyperBac for SQL Server isn’t perfect, though. It has only two levels of compression, which weren’t well documented in the version I tested. (HyperBac has assured me that this documentation has been made more clear in an update.) In my experience, few DBAs tweak the compression parameters of a tool like HyperBac for SQL Server, and I think the HyperBac developers did a good-enough job of implementing the product’s compression algorithms. So having “tweakability” might not be necessary with this tool.

SQL Backup
Red Gate Software’s SQL Backup is available in two versions: Lite and Pro. I tested the Pro version, which is part of Red Gate’s SQL Toolbelt collection of SQL Server tools.

SQL Backup can generate backups or perform restores via its management tool, a command-line utility, or extended stored procedures. The product includes a utility to convert SQL Backup backups to a standard SQL Server format. To use SQL Backup for row-level restores, you need to purchase another Red Gate product, SQL Data Compare. SQL Backup also features log shipping, but since Microsoft has provided this feature from SQL Server 2000 forward, it might not have a wide appeal.

To install SQL Backup, you must first install the management tool; then, as you add instances of SQL Server, you push the service and/or extended stored procedures out to those servers and instances. Although this process isn’t the worst I’ve seen, when you’re importing an instance of SQL Server into the management tool, it would be nice to have the option to install the SQL Backup components at that time, rather than in a separate step. SQL Backup doesn’t configure its own database in a SQL Server instance to store data about its backup and restore operations. It merely reads the information related to backups directly from the instances.

SQL Backup has a very nice interface that I liked. It has a color-coded graphical timeline of backups and jobs and lets you see potential conflicts or problems in addition to the backup. If you’re managing multiple SQL Server instances with SQL Backup, this is a handy feature. The interface is logical and DBA-friendly. I noticed one bug with SQL Backup’s management tool: When I created and then tried to delete a backup job, the confirmation dialog appeared behind the context-sensitive menu, and if the context-sensitive menu wasn’t positioned in just the right way, I couldn’t click Yes. Another minor gripe is that SQL Backup shows an operation’s progress not as an overall percentage but in gigabytes. I would rather see the percentage.

SQL Backup offers three levels of compression. A feature called Compression Analyzer lets you see your possible size savings. Depending on the size of your database, Compression Analyzer could take a while to run, but it will display estimated file sizes within each compression level. Another handy feature is in the wizard that walks through the backup process: It includes an option to copy the backup file being made to a network or other location when the backup is complete. Log shipping is also offered as an option.

Although the management tool is great for managing backups, it doesn’t do as well with restores. You can do an on-demand restore, but you can’t create a job to do a restore. This is frustrating. I see an increasing need with my clients to refresh development or test data with production data. A tool like SQL Backup should be able to automate this process, but it can’t do so currently.

I have other problems with the management tool. It has no provision to kill any connections in the database before you attempt a restore. And the default path for the database restore isn’t the path that’s actually in the backup file; rather, it’s the default path as defined for the SQL Server instance. This is surprising because even if you restore a database backup on the instance on which it originated, it will default to the wrong location. On the positive side, after you run the Restore Wizard, if you don’t want to run the command with the GUI, you can copy and paste either the command line or extended stored procedure code that the wizard generates. This is a great feature for easing repetition and creating your own scripts.

It concerns me that only SQL Backup Pro supports 64-bit SQL Servers. Most of the clients I work with are considering a 64-bit platform for SQL Server, and not every SQL Server deployment uses Enterprise Edition. It seems like an oversight by Red Gate not to include the same platform support in SQL Backup Lite, which is attractively priced for small- to-midsized shops that might want to step up to 64-bit.

One final point is that SQL Backup measures its compression savings against total data size, including unused space. This can artificially inflate the savings number. The numbers aren’t egregiously off, but for many IT organizations, under- or overestimating space can cost money if doing so creates a capacity-management problem. Compression savings should be measured against the potential native backup size, not a total database size that includes things that don’t factor into the backup.

Continued on page 2