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:

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

SQLsafe

Like SQL Backup, Idera’s SQLsafe is part of its own suite of SQL Server tools. You can use its management tool, a command-line utility, or extended stored procedures for backup and restoration. SQLsafe’s point-in-time slider can help you restore to a specific point (assuming you have the proper set of backups available) and, like HyperBac for SQL Server and SQL Backup, includes a utility for converting backups you make with SQLsafe to a native SQL Server backup. SQLsafe also has the unique option to restore a single table. Idera offers a free version of SQLsafe called SQLsafe Freeware that has limited functionality and seems to be geared toward small, nonenterprise environments.

Installing Idera’s SQLsafe reminded me a bit of my experience with LiteSpeed for SQL Server—I had to click through a lot of screens to set it up. SQLsafe configures and uses a management database, so you need to have an instance of SQL Server for it to use. Neither HyperBac for SQL Server nor SQL Backup has this requirement. If you use SQLsafe, you’ll need to make sure that the instance powering it is highly available.

One problem with the management database approach—or at least the approach as SQLsafe implements it—is that backups and restores you make outside of SQLsafe don’t seem to be recorded in the management database. The difficulty with this approach is obvious: If a DBA generates any kind of backup outside of SQLsafe, doing so could break the log sequence number (LSN) chain, and SQLsafe will know nothing about it. Idera is making a dangerous assumption that once you’ve installed SQLsafe, no DBA in your environment will ever make a backup outside of SQLsafe. This approach also emphasizes the necessity of making the instance on which SQLsafe runs highly available.

SQLsafe supports different forms of encryption but doesn’t specify whether the level is 128-bit or 256-bit. Idera includes a tool for converting SQLsafe’s backup to a standard SQL Server backup format. SQLsafe also has a command-line utility to perform backups and restores outside of SQL Server and the SQLsafe tool. SQLsafe offers six options for compression: four numbered levels and optimizing Idera’s IntelliCompress technology for either size or speed. Of the three tools I tested, SQLsafe has the greatest number of compression options.

SQLsafe uses a service and doesn’t install extended stored procedures into an instance by default. If you run backup jobs with scripts and don’t want to use the service, the extended stored procedures that would allow you to script SQLsafe backups aren’t installed by default, so they’d need to be installed on each instance where the scripts would run, then you’d need to change your scripts (as you would if you used SQL Backup’s extended stored procedures). One problem is that the only place the different options that the extended stored procedures use are documented is in the sample scripts Idera provides. This isn’t optimal, and it was also a little sloppy—the version of SQLsafe listed in the scripts was the one previous to the version I tested. Even if the scripts are the same in both versions, I would have thought Idera would update the sample scripts to reflect the version of the product that was installed.

I wasn’t impressed by SQLsafe’s management tool. The UI reminds me of Outlook, and I didn’t find its workflow to be as intuitive as the one in SQL Backup. I did like how it showed the progress of an operation as a percentage, which is more useful than SQL Backup’s display of progress in gigabytes.

SQLsafe lets you set a backup policy across multiple servers. I didn’t test this capability across different physical servers, but my test server was running two instances of SQL Server 2005. SQLsafe doesn’t have the ability to customize the backup path for each instance when you set a backup policy, which means all backups will go to the same location. An Idera representative told me that a future version of SQLsafe will rectify this situation and that you can modify the behavior in the current version by using macros. However, this workaround isn’t obvious to anyone who wants to be up and running with SQLsafe quickly.

I have the same gripe with SQLsafe’s management tool that I did with SQL Backup: You can manage your backups but not your restores outside of one-time-only restores. SQLsafe provides utilities for importing maintenance plans into the SQLsafe repository. One curious omission: I saw no provision for importing regular SQL Server Agent jobs. A bigger problem is that as I tried to run the tool for importing a maintenance plan, I got the message “ALLANX64INS2 is SQL Server 2005 or greater which is not supported.” I didn’t see any documentation to tell me that the feature doesn’t work with SQL Server 2005.

The Tool That’s Right for You

When it comes to disaster recovery, refreshing a development server with new data, or any operation that requires a restore, time is money. All three tools both sped up and compressed backups and had faster restore times than native SQL Server operations. Time spent backing up and compressing the backup was similar among all three products. HyperBac for SQL Server and SQL Backup had similar times for a restore operation; although SQLsafe’s restore time was faster than a native SQL Server restore, it was considerably slower than the other two products’ restore times. (To read more about my compression testing, see the sidebar “Compression Testing and Results.”)

HyperBac for SQL Server is available with a per-server license model and also has the lowest list price without reduced functionality. Imagine that you are administering 100 instances. If you spread those instances over 65 physical servers, the list-price comparison among the products would be $99,500 for SQLsafe, $51,675 for SQL Backup Pro, and $43,875 for HyperBac for SQL Server. Despite the cost differential, Idera deserves credit for not requiring a license for the SQLsafe agent on instances that are performing only restores, which is perfect for disaster recovery.

Where workflow is concerned, HyperBac for SQL Server is at the top of the list based on the way most DBAs I know like to work. Although it doesn’t ship with a management tool for backups, it works with whatever tool you have. As I’ve noted, a big part of the pain of tool adoption is the cost of implementation. HyperBac wins hands down on this point because it lets you get up and running in short order. But if you’re managing a lot of SQL Server backups and want the full complement of GUI, management tool, and the ability to use the command line or scripts via extended stored procedures, either SQL Backup or SQLsafe might be better bets for you. Both these products have a management tool that is geared toward environments with multiple SQL servers. Neither management tool is perfect, but either might do a better job for you than managing your backups in each instance with SQL Server Management Studio (SSMS).

Both SQLsafe and SQL Backup were more cumbersome to install than HyperBac for SQL Server, involving multiple steps such as pushing components out to SQL servers after the initial install. HyperBac for SQL Server’s install could become part of a standard Windows build process so that it’s already in place by the time a DBA gets a new server.

All three tools offer similar performance in compression, so an ultimate purchasing decision boils down to the other features and functionality a tool brings to the table and how it can benefit your own backup and restore administration. I found SQL Backup and SQLsafe both slightly disappointing; neither features the ability to set restore policies or lets you manage regularly scheduled restores. In enterprise-class tools, these are glaring omissions. Using the cost example of 100 instances, if you compare according to price and feature set, is SQLsafe approximately two times better than either SQL Backup or HyperBac for SQL Server? In my opinion, the answer is no. Both HyperBac for SQL Server and SQL Backup Pro represent good value, and each takes a different approach. If I were in the market for a SQL Server backup compression tool, I’d choose HyperBac for SQL Server, and that’s why I’ve designated it my Editor’s Choice. But I recommend that you audition the tools that appeal to you and make your own informed choice.