Back up, restore, recover: 2 tools for your toolkit
For at least 50 years, magnetic tape has been the medium of choice for long-term data storage, and nothing is likely to completely replace tape in the near future. However, increasingly, organizations are backing up databases to a hard disk before copying the file to tape because the cost of large-capacity hard disks is decreasing so rapidly and the speed of restoring a backup from a hard disk is several times faster than restoring from tape. For long-term storage, tape has an advantage over hard disks because it is both physically smaller and cheaper per megabyte, but hard disks are cheap enough now to make them the preferred storage for short-term needs.
This change in the way DBAs back up data has led to a change in the way companies design backup software. For example, UltraBac Software's UltraBac 7.1.4 is similar in functionality to such longtime backup leaders as VERITAS's Backup Exec in that it manages backups from multiple computers on the network either to a local tape drive or to a tape library management server. But UltraBac has some extra features that let you back up to hard disks, FTP servers, and other devices as well as perform complete disk-image backups of both client machines and servers. Imceda Software's (formerly DBAssociates') LiteSpeed for SQL Server started out as a disk-only solution but added tape support in a recent update.
I recently evaluated UltraBac 7.1.4 and LiteSpeed for SQL Server 3.1 for my company, an application service provider for the healthcare industry. We already use Backup Exec and a Dell PowerVault 130T tape library server to back up our SQL Server databases and our file servers to tape nightly. But we wanted to look at alternatives or supplements to Backup Exec because my tests showed that restoring from tape requires well over an hour, and being down for more than an hour waiting for a backup to restore is unacceptable for our environment. Additionally, our main database is growing by about 5GB per month. So maintaining a week's worth of full backups on disk is becoming more expensive, and our total storage needs are projected to grow from approximately 220GB per week now to more than 700GB per week within the next 12 months. My goal was to find a way to decrease our storage requirements and our backup and restore times.
Both UltraBac and LiteSpeed let you back up to a hard disk, which Backup Exec doesn't support, and offer data compression that's significantly better than anything we get through the compression hardware built into PowerVault 130T. Imceda has focused on making LiteSpeed the fastest database-backup software for SQL Server, and this attention to detail shows in my tests. UltraBac's feature set overlaps with Backup Exec's almost completely but offers better compression and encryption of the data.
Directly comparing UltraBac and LiteSpeed is difficult because they don't offer the same feature set. Table 1 shows the features that are probably most important to DBAs; as you can see, the products differ a bit in functions that might be essential to your organization. For example, as a consultant, I've seen environments in which point-in-time recovery and object-level recovery are absolutely necessary for a backup-and-restore solution. LiteSpeed's Professional version provides both features, but its Standard version and UltraBac don't.
Encryption is another feature that can be crucial for businesses. The push to protect patient privacy means that more DBAs for healthcare companies are looking at encryption as a way to protect data if the physical storage leaves the company's control, whether by theft or by accident. (For more information about how DBAs can address patient privacy concerns, see Sean Maloney's July 2004 article, "For Your Eyes Only," InstantDoc ID 42615.) Encryption is one more way to protect data against theft. LiteSpeed has encryption in its more expensive version; UltraBac has it standard.
Perhaps the greatest difference between the two products lies in their options for managing your backups. UltraBac's management console is significantly more polished than LiteSpeed's. LiteSpeed replacesbackup and restore commands with extended stored procedures; UltraBac provides a GUI that coordinates access to all its functions and devices. If you're used to Enterprise Manager, the UltraBac GUI provides the same kind of assistance in managing the backup environment.
UltraBac also supports more types of destination devices and offers easy management of those devices. For example, it has a separate console for managing tape-drive autoloaders. LiteSpeed's tape-drive control is limited to the standard set of commands—erase, skip, format, rewind, unload, and so on. In addition, UltraBac supports storing a backup on an FTP server (a good option for storing backups offsite or on UNIX- or Linux-based file servers) and optical drives (which are a good choice if you're worried about the life span of magnetic tape). Finally, a built-in SMTP client lets UltraBac send status messages without having to install SQLMail. I'm sure I'm not alone in wishing more companies would add an SMTP client so that we could do away with SQLMail altogether.
In terms of management options, LiteSpeed excels with its wizards, called assistants. The assistants walk you through everything from setting up simple database backups to modifying SQL Server maintenance plans to use LiteSpeed's extended stored procedures, which replace the T-SQL backup commands. Each assistant also offers the option of saving the steps it generates as a T-SQL script, so you can use the assistant to build a set of scripts for common management tasks, then modify them for other purposes. Less-experienced DBAs will find that the assistants make properly configuring the system easy, and experienced DBAs will find that these tools speed the process of setting up backups in a multiserver environment.
Another nice feature worth mentioning is LiteSpeed's Extractor tool; it converts Imceda's backup format into Microsoft Tape Format (MTF), which SQL Server can read directly. Even when SQL Server backs up a database to a disk file, it's using MTF as if it were writing to a tape. Being able to convert from Imceda's proprietary format to MTF gives you the option of sharing database backups with any other application that can read MTF data.
The Testing Environment
For this comparison, I used fully licensed, unrestricted copies of the software, not trial versions. Because I was installing the software on spare computers in my production environment, my goal was to see how the products would perform if I bought them. I used a Dell PowerEdge 2650 with two 3.0GHz CPUs and a single 10,000-RPM 146GB SCSI disk connected to the onboard Perc 3/DI hard-disk controller for both the database and the backup files. This configuration isn't ideal for a production database server, but the hardware I planned to use for the tests was delayed. Nevertheless, the results from the tests accurately reflect the relative speeds of the two products.
Note that I couldn't test backups to tape because the only tape drive we have is the one my company uses for backing up our production environment. My company is very risk-averse, so I decided not to introduce any changes to production. Because we hold our backups on disk for a couple of days and use tape only for long-term storage, the process of backing up directly to tape wasn't part of my evaluation. What I considered more important was the size of the backup file, reasoning that a smaller file takes less time both to store and to load. As you'll see, the tests don't support that assumption.
Size Matters for Backups
A big database may seem impressive, but size becomes an impediment when it comes time to restore a database after a system failure. The laws of physics dictate a finite limit to how fast data can be read from a physical medium such as a tape drive or hard disk. And restoring a database incurs a double time penalty because the software must read the data from the backup device and write it to the hard disk. Common sense says that a small database backup file should take less time to restore than a large one.
The size of the backup file affects the cost of disaster recovery as well as the time. Compression built into the tape drive doesn't offer much cost reduction because it often doesn't compress database backups well. Even though the tape drives are supposed to provide 50 percent compression, it often happens that databases only end up compressing to 60 to 75 percent of their original size. You can decrease the costs of backup storage by backing up the database to disk, then zipping the files to compress them. However, you'd need to unzip the backups before restoring them. That extra step usually adds a significant amount of time to recovery, so you'd be sacrificing a speedy recovery just to decrease the cost of storing the database backup on tape.
To achieve smaller backups without sacrificing speed, you need to have the compression built into the backup software. Both LiteSpeed and UltraBac perform some compression, but LiteSpeed had a much better compression rate for my production database. Table 2 compares the backup file sizes for both products—and for SQL Server backup for reference. One result that surprised me is that although UltraBac compressed the database file reasonably well (as Table 2 shows), the backup took about 79 percent of the time a standard SQL Server backup took. On the assumption that writing fewer bytes to disk would result in faster backup times, I expected UltraBac's backup time to be roughly 39 percent of the SQL Server backup time. That assumption didn't prove true for either product. In fact, Table 2 shows that the decrease in the backup time correlated to about twice the percentage of compression. In other words, UltraBac produced a file that was 39 percent of the size of a SQL Server backup in 78 percent of the time it took to do a SQL Server backup. LiteSpeed produced a file that was 28 percent of the size of a SQL Server backup in 51 percent of the time. Possible reasons for the differences in backup times range from the efficiency of the compression algorithms to memory management to hard-disk contention. In any case, LiteSpeed was clearly superior in terms of raw backup speed.
Restore in a Hurry
Because databases are a critical business resource, when they stop working, DBAs feel urgent pressure from the highest levels of management to restore operations immediately. There's nothing like a database server crash to teach top managers at your company your name and phone number. For this reason, the speed with which an application can restore a database backup is probably a more important part of the buying decision than how long the database backup takes.
The database-restore time, though, depends on more than the software you use. While writing this article, I discovered some known problems with how fast certain RAID controllers from Dell and other companies can write to a RAID 5 array. A worst-case example was a former client's 50GB database that took about 30 minutes to back up but required more than 3 hours to restore. I narrowed the problem down to the fact that the database's data files were being written to a RAID 5 array. This example is specific to Dell RAID controllers, but it doesn't seem to occur in all cases. However, it lends support to two truisms about database backups. First, just because a database takes X minutes to back up doesn't mean it will take X minutes to restore. And second, you should always test backups by restoring them.
Looking at Table 3, you can see that LiteSpeed had a distinct advantage in restore times in my tests. UltraBac's restore time was similar to SQL Server's, indicating that UltraBac didn't provide any restore-time enhancements over SQL Server's built-in functionality. However, the location of the backup files is another important factor in determining restore times. I performed all the restores in my tests from the backup files on the local hard disk, but the time required to read LiteSpeed's backup off tape might bring its total restore time closer to the other two products. (I couldn't test this, however.)
You might assume that the difference between LiteSpeed's and UltraBac's restore times resulted from UltraBac's backup file being 50 percent larger than LiteSpeed's. If UltraBac restored the database in 1.5 times the amount of time LiteSpeed took, that argument would seem reasonable. But UltraBac took more than twice the time LiteSpeed did, and it took 4 minutes longer than the standard SQL Server restore, so UltraBac in my tests offered no technological superiority over SQL Server's standard functions. UltraBac's documentation says it uses the same APIs SQL Server uses, so restore times comparable to SQL Server's are to be expected.
If you have a backup solution in place at your company, the first question management will ask when you start evaluating these products is, "Why do you need to spend more money on backup software?" One good reason is that compression decreases the effects of growth in the disk backups. If your database grows too large for existing hard disks, your only two choices are to buy more (or bigger) disks or to compress the database backup to fit on existing disks. Compression is the only way to expand your backup capacity without buying more hardware. For example, a 73GB, 15,000-RPM SCSI disk from Dell costs my company about $800, and a 146GB 10,000-RPM SCSI disk costs more than $1100. You can find cheaper prices, but the cost of adding even one new drive or adding a new hard disk array is still going to be comparable to the cost of either LiteSpeed or UltraBac.
The cost of archiving data to tape is another good argument. The compression both products offer should repay your investment by decreasing the number of tapes you need to buy each year. The 200/400GB tapes for our Dell PowerVault 132T cost approximately $75 each. Our policy is to perform a full backup of the database each night, and because of government regulations, we never rotate our tapes out of long-term storage. Based on a projected growth rate of about 5GB of data per month, we'll need 58.5 tapes (23,400GB total divided by 400GB per tape, which assumes the theoretical best hardware compression rate) just for the database backups. So 58.5 tapes at $75 each is $4387.50 a year. If I assume that LiteSpeed will compress my backups to 28.6 percent of their original size, my company will spend only $2509.65 per year on tapes. That's a savings of $1877.85 a year, not including the savings in storage costs for storing half as many tapes. UltraBac, which creates slightly larger backup files, will offer similar, though smaller, savings. For example, 39.1 percent of 23,400GB is 9149.4GB, which divided by 200MB per tape means we would need 45.75 tapes per year. And 45.75 multiplied by $75 per tape yields a yearly cost of $3431.03 and a yearly savings of $956.48. Those savings make it possible for companies like mine that use a large number of tapes each year to pay for a reasonably priced product like UltraBac in a very short time.
My only real complaint about both products is their limited documentation. LiteSpeed has some excuse for the minimal documentation because it's a straightforward, easy-to-learn replacement for SQL Server's standard backup and restore commands. If you understand how to use the T-SQL statements, you should have little difficulty understanding LiteSpeed.
UltraBac, on the other hand, focuses its documentation more on image backups than on database backups. I often felt like UltraBac added SQL Server backups as an afterthought—for example, only a few pages of text in the Help file discussed how to configure UltraBac's backup agent software for SQL Server. There is almost nothing about troubleshooting problems, improving performance, how the agent works, and so on. Fortunately, the GUI is well enough designed that you can back up databases without needing the documentation. Still, it's a weakness in an overall strong product.
Making a Choice
For my environment, in which having the server down as much as 1 hour is a disaster, LiteSpeed is the obvious choice. LiteSpeed does such a good job of compression that you can afford to keep a week's worth of backups on a file server. Given the decrease in the cost of large-capacity hard disks and the growing acceptance of serial advanced technology attachment (ATA) drives for production storage, backing up databases to hard disks will likely become the standard as the first stage of a disaster-recovery plan.
Most DBAs have more confidence in tape than in hard disks for long-term storage, so tape is still king, and every environment needs some method for managing those tapes. UltraBac is superior to LiteSpeed in the number of devices it can manage and in managing tape libraries. In fact, backup management is where UltraBac excels. LiteSpeed's tape-management options aren't as well-developed or sophisticated as UltraBac's tape-management functionality. If you don't already have software for managing tape drives, I recommend UltraBac. Even if you do, the savings from better data compression may make it worthwhile to replace the software you are currently using.
If you have enough money in your budget, consider buying both products. Use LiteSpeed for fast, compressed, encrypted backups, and use UltraBac to keep track of where everything is stored. Each product has strengths that complement the other's weaknesses; together, they make a very powerful toolkit.