Smooth and swift SQL Server backups

Imceda's LiteSpeed 2005 makes light work of heavyweight database backups

This article is a revised version of the original published January 3, 2005. See Editor’s Note for details.

Two major factors drive database backups these days: compliance and disk space. Some compliance controls now ask for backup encryption to protect against off-site tape theft, yet the size of current databases makes it more difficult than ever for DBAs to follow disaster recovery best practices.

To manage both issues, DBAs turn to products that crunch down the size of databases for speedier backup and protect data with strong encryption. For backups of Microsoft SQL Server databases, DBAs would be well served to turn to Imceda Software's LiteSpeed 2005. Its flexibility and ease-of-use are excellent, and it's got speed to burn, handling backups of large databases far more swiftly than rival Idera SQLsafe 1.2 in my lab tests.

I installed LiteSpeed on 10 machines and put it through a real-world data-backup gauntlet to gauge performance, compression rate, manageability, and scalability. LiteSpeed’s setup is flawless, and it doesn’t require touching each box by hand. After you choose the servers for installation and define parameters for each one, the LiteSpeed console pushes not an agent but the entire application to each server, installing it just as if you were sitting at the remote machine.

The LiteSpeed console gives you a view into your server by color-coding the databases so that you can tell at a glance which ones have and have not been backed up. It also shows you the SQL jobs on the server and which one fired a particular backup.

Two console features that I thought were insignificant proved very handy in my testing: LiteSpeed shows you the command that was executed as well as the amount of time the backup took. It’s not much, but when you need to know the duration of a backup, it’s nice to have it pointed out for you.

Perhaps the most significant features in LiteSpeed are the new NCS (Native Command Substitution) and the addition of object-level recovery to the GUI. Object-level recovery is a great way to restore an individual table that was accidentally deleted or corrupted. NCS allows you to take backups with LiteSpeed using native SQL Server syntax, so you don’t have to alter your current routines. I can’t tell you how much this sped up my implementation. For shops with customized backup routines, being able to plug LiteSpeed into the equation will ease the implementation headache -- no need to spend time testing changes to scripts.

LiteSpeed offers 10 compression levels, giving you a great deal of control over the size of your backups. LiteSpeed doesn’t offer an “at all cost” compression option, such as that found in Idera SQLsafe. However, LiteSpeed’s most effective compression scheme is awfully close to optimal, without the serious degradation of backup time that comes with SQLsafe’s all-out effort.

LiteSpeed also provides fine control over runtime parameters, allowing you to adjust CPU and memory utilization, as well as the number of worker threads and other factors, to optimize backup performance for your specific environment. By tweaking a number of these settings, I was able to squeeze quite a bit of extra performance out of LiteSpeed. (SQLsafe automatically configures these settings, removing any potential for misconfiguration but also the opportunity to optimize for speed or other considerations.)

Establishing the relative performance of LiteSpeed and SQLsafe took some trial and error, and the size of the databases being backed up makes a huge difference in the results. My tests showed LiteSpeed to be at least 30 percent faster, and as much as 49 percent faster, when performing unencrypted backups of a 133GB dataset at low, medium, and high compression levels (see table below). In subsequent tests performed at the InfoWorld Test Center lab in San Francisco, SQLsafe and LiteSpeed were evenly matched when encryption was not used, regardless of compression level during backups of databases in the 100GB to 200GB range. When encrypting backups at the optimal maximum of compression, SQLsafe fell hours behind LiteSpeed with depressing regularity.

LiteSpeed excels at compressing and encrypting SQL Server backups, and its console allows you to centralize Click for larger view. the management of all your backups and easily see which backups have failed. It offers 3DES and RC2 encryption out of the box, and if you have a reasonable encryption method you'd like to use, Imceda will build it into the product for you. And because LiteSpeed gives you full control over server resources, you can back up databases under heavy user load with very little impact on the server. Small and midsize shops will find plenty to love about LiteSpeed; large enterprises will benefit from the 64-bit version for SQL Server and forthcoming cross-platform versions for IBM DB2 and Oracle.

01TCsql_ch.gif
LiteSpeed excels at compressing and encrypting SQL Server backups, and its console allows you to centralize Click for larger view. the management of all your backups and easily see which backups have failed. It offers 3DES and RC2 encryption out of the box, and if you have a reasonable encryption method you'd like to use, Imceda will build it into the product for you. And because LiteSpeed gives you full control over server resources, you can back up databases under heavy user load with very little impact on the server. Small and midsize shops will find plenty to love about LiteSpeed; large enterprises will benefit from the 64-bit version for SQL Server and forthcoming cross-platform versions for IBM DB2 and Oracle.

-- Additional reporting for this article was contributed by P.J. Connolly

InfoWorld Scorecard
Reliability (15.0%)
Setup (10.0%)
Performance (20.0%)
Value (10.0%)
Manageability (25.0%)
Ease of use (20.0%)
Overall Score (100%)
Imceda LiteSpeed 2005 9.0 8.0 9.0 9.0 9.0 9.0 8.9
Recommended
Join the discussion
Be the first to comment on this article. Our Commenting Policies