|
[SQL Server Magazine Review]
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
·
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
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
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.
SQLsafe
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 “ALLANX64\INS2 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
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.
|