View this month's Buyer's Guide
As recently as just a few years ago, backup software had some significant limitations. But vendors have been listening to customer requests by including innovative features in their newer products. This Buyers' Guide compares 11 products and shows how they support the most requested features for backup applications: disk, tape, or DVD backup options; encryption; compression; and management capabilities. With the possibilities that these features offer, you can simplify and speed up your backups while saving space and effort. Before you make your choice, it's helpful to understand the functions and benefits of these features. Then, use the table on the following pages to start comparing your options.
Tape, Disk, and DVD
Five years ago, DBAs had only one viable choice for backup storage: magnetic tape. With tapes, DBAs spent a great deal of time maintaining the list of files and expiration dates on the tape. Backup software was as much concerned with tape management as it was with backing up data.
In recent years, a drastic drop in prices and a substantial increase in capacity has made hard disks the preferred destination for short-term storage. Although tape is still the predominant medium for long-term storage, hard disks have the advantage of being faster for backing up and restoring data. The primary reason for backups is to be able to recover from a disaster, so the amount of time it takes to restore data is in many cases more important than the time it takes to do the backup. Hard disks perform both tasks faster than tapes, so in newer backup software, backing up to disk is the first stage of the overall backup strategy.
The only situation in which tapes can beat hard-disk performance is in backing up very large (i.e., multi-terabyte) databases. The ability to partition the database backup across multiple tape drives so that you can back up all the partitions in parallel gives tapes an edge over hard disks—if you have multiple tape drives and software that can keep them supplied with data fast enough. Additionally, even though the top capacity for hard disks is in the 300GB to 350GB range, a configuration that can store multiple terabytes of data can be expensive, and hard disks aren't cost effective for long-term storage.
Databases that are less than about 30GB have a new alternative to both tapes and hard disks: writeable DVDs. Currently, DVDs have a capacity of about 4.5GB (the new dual-layer DVDs effectively double this), and they have a street price of about 75 cents each. If a 200GB tape costs $75, DVDs are about 50 percent cheaper per megabyte than magnetic tape. Given that DVD-R drives are an inexpensive upgrade to most servers, DVDs are becoming a viable, cost-effective alternative for backing up smaller databases.
Compression
Hard disks and DVDs became backup options when vendors integrated software-based compression into backup applications. A few years ago, 36GB to 40GB disks were the norm, so only a small to midsized database could fit on one disk. Even though RAID arrays could accommodate larger files, using multiple disks to store database backups wasn't economical. Compression rates of 2-1 or 3-1 mean several backups can now fit in the same space as one uncompressed backup.
Compression also decreases the overall time it takes to backup or restore. Most databases contain "empty" space that doesn't have any data, and a large part of any database is usually composed of character data such as names, addresses, and descriptions. The common lossless compression algorithms easily compress this type of data. Because most of the work in backup is writing the data to a physical device such as a hard disk or tape drive, writing a smaller amount of data improves overall backup time.
For a long time, tape drives have had specialized hardware to compress data as it's written to the tape, but that hardware hasn't kept pace with the performance of the host systems' CPUs and memory. It's logical to expect your multi-CPU production database server to be able to process many times more data than your tape drive because that's what your server is optimized to do. Additionally, the algorithm that the tape drive uses is typically a general-purpose compression algorithm that yields about 50 percent compression for a variety of data types. Algorithms that are optimized for databases can not only produce greater compression but also perform their job in less time.
The effect of storing compressed files on a tape drive that has hardware compression built in is sometimes confusing. Because backup software compresses the database as it writes to the storage device, it's unlikely that the compression hardware on the tape drive will be able to compress the data any further. Thus, you must plan your tape storage based on the actual raw capacity of the tape and not on its compressed capacity. Software compression can actually increase the number of backups you can get on a tape—and therefore save money on tapes—if the software compression is more than what the tape-drive hardware can produce. The best way to determine whether to use software compression in the backup application or hardware compression on the tape drive is to use both methods to back up the same database, then compare the size of each backup file.
If you have databases that contain hundreds or thousands of gigabytes, you must compare striping the database backup across multiple tape drives to writing a software-compressed backup to a single drive. A few of the applications in this survey will compress the data and write it to multiple tape drives, making quick work of backing up even terabytes of data. At the time of writing, Imceda had successfully demonstrated a backup of 2 terabytes in less than one hour. If you have large databases, you can filter your list of candidates by the size of the databases they have demonstrated they can handle and the speed with which they can back them up.
In comparing compression, you must also look at more than the raw metrics. The application architecture will affect the overall performance too. For example, if you're going to write your backups to a remote device, where you perform the compression will affect backup times. Applications that compress the data on the database server before sending it across the network typically take less time than applications that move uncompressed data across the network. For tape drives in particular, the time to move the data offsets the benefits of performing compression on the tape drive's hardware. Several applications in this survey have a client-server architecture; therefore, you should compare them in an environment that closely resembles your production environment.
Encryption
Several recent federal laws make securing backups from unauthorized inspection vital. In the past, the way to protect backups was by physically securing them in a safe, a locked cabinet, or an offsite storage facility. Although you should still lock up your backups, it's also a good idea to secure your backups with encryption.
Encryption's main benefit is that it secures the data even when the backup leaves your control. For example, one of my main worries is that I will be robbed when I am transporting our backups from our hosting facility to our offices. With encrypted backups, I know the backup will be useless to anyone who doesn't have the password. Even though I know it's unlikely that our backups will fall into the wrong hands, encrypting them nearly eliminates the possibility that someone could use our data illegally.
You need to consider three points when comparing applications' encryption support. First, see whether encryption is a standard feature or an add-on that has an extra cost. Supporting encryption is becoming the standard, but some applications in this survey either don't support it or charge extra for it.
Second, check the list of encryption algorithms that the application uses. Each algorithm has its strengths and weaknesses, and the size of the key the algorithm uses greatly affects its overall security. To secure important data, look for 512-bit keys and passwords of at least 10 characters; more bits and characters are always better. To avoid potential flaws in the algorithm's implementation, check whether the application uses an open-source implementation, one from a third party, or its own code.
Finally, where you perform the encryption can be important. If you're moving the database backup to a remote device across a network, you need to know whether the data is secure in transit. Applications that encrypt the data before sending it to the storage device are preferable, but you must keep in mind that encryption is a CPU-intensive operation. You should balance the need for security on the network with the demands the encryption process will place on your database server.
Management
The last major point you need to consider in evaluating backup applications is the management of not only the backups but the backup process. Even for the small, three-server, five-database environment I currently manage, setting up and maintaining daily full backups and every-15-minute transaction log backups takes a couple of hours for each new database. Checking on the status of the backups takes another 15-20 minutes a day. Over time, as the number of servers and databases increases, the complexity of managing all the backup jobs and storage devices also increases.
Some applications retrofit their commands into standard SQL Server maintenance plans, whereas others integrate the backup jobs directly into their management interfaces. Products that vendors such as Ultrabac and VERITAS offer have well-designed management tools that simplify managing multiple servers from the DBA's workstation. Other products have fairly simple, wizard-like interfaces to help you build the T-SQL command scripts to perform backups. The sophistication of interfaces varies greatly, so you have to evaluate how well an interface fits with your existing management tools.
So far, we don't have one perfect application that backs up databases to all available devices, quickly compresses the data to infinitesimal size, encrypts with military-grade algorithms, and provides a management interface that makes 1000 servers as easy to manage as one. All of the applications in this survey have areas in which they excel. Choosing the one that fits your requirements for a price within your budget is up to you.
