SQL Server 7.0 Backup Roundup

Options for protecting your enterprise data

Without a proper backup, your organization's data is just one administrator error away from disappearing into a dark void. If you're shopping for a backup solution for SQL Server 7.0 data, consider the many factors that affect data backup, such as shrinking backup windows, cost of equipment, and personnel experience in backing up data. You'll notice differences in table backups between SQL Server 6.5 and 7.0. With SQL Server 7.0, you back up only files and filegroups; to back up one table, you need to save that table in a file or filegroup and then back it up. This difference might frustrate systems administrators, but Microsoft's reasons for implementing these changes include system and database stability and consistency.

What should you look for in a tool to assist you in backing up your SQL Server 7.0 databases? Speed and reliability are the two main features, but also look for the ability to restore the database to any hard drive you choose. I evaluated current enterprise-level backup solutions: UltraBac.com's UltraBac 5.02, Computer Associates' ARCserveIT 6.61, Legato's NetWorker 5.5.1, SQL Server's native backup, and VERITAS's NetBackup 3.2.

Test Configuration

To test each product's features and speed, I took a trip to Data General's corporate testing labs in Westboro, Massachusetts. I used a Data General 8700 system with four 500MHz processors and 4GB of RAM (constrained to 3.2GB), connected to a CLARiiON disk array with 60 18GB Seagate Cheetah hard drives. I configured five sets of 10 hard drives, each as RAID 5. Eight additional drives, configured as RAID 5, held the Master and tempdb databases. The other two hard drives mirrored each other and included SQL Server logs. I also set up a second CLARiiON disk array with the same number of drives, each containing formatted 18GB Seagate Cheetah hard drives. I restored each backup product's data set to a new disk system on the second array to simulate an after-disaster full database restore.

Using fibre channel connectivity, I connected an ATL 7100-series tape autoloader to the CLARiiON disk drive. The autoloader included seven DLT drives and a 100-tape storage capacity. I spread the tapes across four SCSI buses on the autoloader to improve response time and reduce network bottlenecks.

To test the backup programs, I set up a 500GB SQL Server 7.0 database called TeraCLIN, which contained all the database tables, on the CLARiiON disk array. Next, I used each product to run a full backup of the database. In some cases, I configured all seven DLT tape drives to write simultaneously using RAID 0; other times, the backup software allowed simultaneous read/write in parallel. Next, I restored the backed-up data to a second disk array and timed the process. I timed each product's backup and restore of the test data separately and then added the times together.

I reviewed additional backup features, such as support for open-file backup (or third-party, open-file backup utilities included with the products), which is important if your databases run 24*7. I evaluated the product's support for parallel data streaming (interleaving) or, lacking that, its support for configuring the drives for simultaneous data streaming with RAID 0. Further, I looked at each product's support for autoloaders, automatic drive configuration, and bar coding of tapes. Finally, I evaluated the documentation and technical support for each product. Table 1, page 48, shows the results.

UltraBac 5.02 Enterprise Edition

UltraBac from UltraBac.com (BEI Corporation) focuses on the Windows NT market, from the small business to the enterprise. UltraBac was the only backup product I tested that came on floppy diskettes—two, to be exact. After starting the setup program, I accepted the default settings and the prompt for the software to create an account with appropriate privileges. Next, I had to set up the optional BEI Medium Changer driver to enable UltraBac to use the ATL autochanger. Then I rebooted my system and opened UltraBac from the Start menu.

Although UltraBac supports autoloaders, it doesn't fully support parallel streaming, bar-code reading and labeling, or other common enterprise-level features. I had to manually load the tapes into each of the seven DLT drives because UltraBac was built to work with individual drives first and autoloaders or jukeboxes second. I set the seven drives as a RAID 0 set to maximize performance.

When UltraBac first ran, it created the BEI Scheduler service and started it automatically. UltraBac documentation states that DLT tape drives lose hardware compression, and recommends that you use the BEI DLTTAPE drive or upgrade the NT 4.0 DLTTAPE.SYS with the corrected version from Service Pack 3 (SP3) or later. To back up my SQL Server 7.0 database, I had to install UltraBac's SQL Server Agent. To use the SQL Server Agent, I had to run the SQL Server 7.0 Client Network utility from the Start menu and enable Named Pipes as the default network library. Next, I checked that SQL Server and Windows NT were selected in the Authentication area of the Security tab in the Properties dialog box.

UltraBac's uncluttered interface was easy to navigate. To back up the test database, I chose a new online SQL backup, selected the TeraCLIN database, gave the set a name, then started the backup. The backup ran without any problems, finishing in 3 hours 15 minutes. This time was the slowest of all the products, and 9 minutes longer than SQL Server's native backup.

Next, I prepared to restore the database. I selected the TeraCLIN database, and the restore started without a hitch. The restore took 7 hours 15 minutes, 20 minutes more than the leader in this review. UltraBac's total backup and restore time was 10 hours 30 minutes, which gave it third place. I was disappointed that if I wanted to do a test restore of the database, I couldn't specify specific drive locations, although I could specify a different system to restore to.

Scheduling backups is easy with UltraBac's intuitive scheduler GUI, which you see in Screen 1; I didn't use the Help file or documentation. UltraBac includes UltraCopy, a media duplication utility, and UltraVue, a utility to view backup data and logs. You can change user preferences and tailor them to your work habits or to the tasks you're trying to complete.

UltraBac performed admirably in backing up my enterprise database; UltraBac also offers Windows NT file and Microsoft Exchange backup in the same program. Although UltraBac finished in the middle of the pack in total backup and restore time, its installation and configuration was among the easiest of the five products. However, I was disappointed that I couldn't use the ATL autoloader's full capabilities.

ARCserveIT 6.61 Enterprise Edition

ARCserveIT 6.61 Enterprise Edition is part of Computer Associates' Management Solutions series. ARCserveIT assists overburdened systems administrators by installing and configuring small-, medium-, and large-scale backups quickly and efficiently.

ARCserveIT comes on a single CD-ROM, which starts automatically and brings up a product explorer. After reading the printed manual, I determined that I needed to install the Tape Library Option, the Backup Agent for SQL Server Database, and the ARCserveIT standard backup utility. The installation ran through a detailed wizard that included locations for files and the default database for storing information about its operations. I chose to install the default ARCserveIT database and its support files. Having to install Computer Associates' Unicenter TNG Framework whether I wanted it or not frustrated me.

Next, I installed ARCserveIT's Tape Library Option, which let me optimize the ATL autoloader's capabilities. The wizard-based installation was simple and included a configuration button that automatically set up the seven DLT devices in the correct order. I enabled Library Quick Initialization, which meant that ARCserveIT wouldn't read media during the library's initialization, thereby saving time. Finally, I installed the Backup Agent for SQL Server Database, which prompted me for the SQL Server login name and password, then completed its setup. Within 10 minutes, I finished installing all three components without any errors, and I started ARCserveIT from the Start menu.

To stream data to all the DLT tape drives simultaneously and reduce the time for backup and restore, I configured the tape drives as RAID 0 through the ARCserveIT for Windows NT program item. I assigned all seven DLT drives in the autoloader to the newly created RAID, then restarted the tape engine to save the configuration changes.

With the configuration complete, I opened the ARC-serveIT Manager from the Start menu. The screen showed two access option tabs. Wizard Quick Access included wizards for backup, restore, and device operations, and a boot-disk wizard for disaster recovery. Classic Quick Access lets you select specific items and set them up manually. I chose the Classic tab because I couldn't see my SQL Server 7.0 database while I used the Backup Wizard. I started the backup and viewed its status through the Job Queue tab, as Screen 2 shows. Installing and configuring all the test equipment to back up the enterprise SQL Server database was easy. The wizards were simple to follow, and detailed information was just a click away in the online Help file. The backup completed without error in 2 hours 41 minutes, the third-fastest time.

Restoring the database was easy to set up, too. I started the restore, returning periodically to monitor its status. I was stunned when it completed 29 hours later! I rechecked all the settings, verified that they were correct, and started the backup restore again to test for a fluke. But the second time was within 1 minute of the original. Numerous calls to Computer Associates' technical support line to identify whatever problem was slowing the performance proved useless. This disappointing restore time gave ARCserveIT a total backup and restore time of 31 hours 41 minutes—almost three times as long as the longest-running of the other products.

The interface for the installation and configuration process for ARCserveIT was user-friendly. I had the backup running within 15 minutes after I inserted the CD-ROM into the server. I liked having the ability to toggle between wizard-based tasks, and the classic interface that lets you dig deeper or configure your hardware and your backup more finely. But Computer Associates needs to find the problem that's slowing ARCserveIT's restore process, especially for enterprise environments, where time is money and a full day's downtime is unacceptable.

NetWorker 5.5.1

Legato's NetWorker, for enterprise backup and recovery, is the company's flagship product. I received NetWorker 5.5.1 in a thin package with four CD-ROMs that included all the NetWorker software with client agents and extensive online documentation. First, I inserted the documentation CD-ROM into my test server. The online installation guide led me through the steps for setting up NetWorker. Installing the product was easy, and after a system reboot, I opened the NetWorker Administrator from the Start menu. I chose the NetWorker wizard, which led me through configuring my backup server, setting up initial backup clients, and configuring the autoloader.

Configuring the autoloader was a little tedious, despite the detailed instructions in the Help file. NetWorker recognized the ATL autoloader. However, to configure it, I had to enter jbconfig at the command prompt, rather than use the program's GUI. An installation script appeared and prompted me for information, such as the type of autochanger to install, the name to assign the autoloader, the pathname to the autoloader's control port, and the paths to each tape drive. Another option was to enable bar-code reader support and to have the tape's volume labels match the bar-code labels. Although the installation wasn't difficult, Legato could better serve users by leveraging the GUI for setup and configuration, rather than forcing them to use the command prompt.

Next, I used the autoloader configuration window to enter devices. I had to enter each of the seven tape drives individually and list their pathnames in the same order as their physical locations in the auto-changer. It took extra time to figure out what NetWorker was looking for and to ensure that the entries were configured correctly. A Legato tech support person told me that after I got this configuration running, I wouldn't have to touch it. Unfortunately, configuring the autoloader was so cumbersome that I wondered whether I'd want to touch it in the future, even if I needed to.

NetWorker lets you simultaneously send multiple tape streams to multiple tape drives in a process called parallelism. Parallelism is beneficial if you have multiple backup jobs being sent from one or multiple clients. Also, NetWorker includes target sessions, which let you set the number of backups a device accepts. For SQL Server 7.0 backups, you need to set this number to one. With these two options, you can multiplex the data, sending data streams to each of the drives and ensuring that data is always sent to the most underused drive first.

Next, I followed a three-step wizard to install the NetWorker BusinessSuite SQL Server Agent 2.0 Module. The agent can reside on the same system as the NetWorker server software or on a separate system. The BusinessSuite Module passes the data from SQL Server to NetWorker, and NetWorker takes care of the scheduling and storage-management tasks.

After the installation was complete, I opened the SQL Server agent from the Start menu, as in Screen 3, and chose a seven-stripe configuration for backup. Using striping, NetWorker sends multiple streams to the tape devices in parallel, which makes backup and restore faster.

After I set the system and backup parameters, I started the Performance Monitor counters running on a separate computer and started the backup timer. After about a minute, all seven drives started flashing, indicating that the backup was running. The backup completed in 2 hours 12 minutes, the fastest of the products I tested.

Restoring the data was easy, too. The restore took 7 hours 18 minutes, in the middle of the pack of products. I was disappointed to find that if I wanted to do a test restore of the database, I couldn't specify specific drive locations, but had to restore to a different system.

You can schedule multiple backups with NetWorker, which is a particularly useful feature when you need to back up multiple databases and you want to minimize backup time. You can create backup groups to assign one or more SQL Server hosts to, enabling you to distribute backups and alleviate network traffic.

NetWorker includes numerous menus and options, which are useful but difficult to work with at first. Setting up this product was easier than previous versions I've tested, but having to use the command line for configuration tasks was time-consuming, and accessing multiple screens to accomplish backup tasks was confusing. However, NetWorker's total backup and restore time, 9 hours 30 minutes, put it in nearly a tie with the fastest of the products. You can use NetWorker to back up not only SQL Server 7.0, but virtually every operating system and major application (Microsoft Exchange, Informix, Oracle, etc.), which makes it a good choice for enterprisewide backup.

SQL Server 7.0 Native Backup

The advantages of SQL Server 7.0's native backup utility include avoiding the extra cost for a third-party backup product and the ability to back up to any local or network drive. If you've ever used SQL Server 6.5's backup utility, you might be inclined to write off SQL Server's backup as just another hodgepodge of frustration. But Microsoft has improved on the functionality and ease of use of SQL Server 7.0's backup. Backup is no longer painful. Now you can quickly move through the well-constructed wizard to back up as little or as much data as you want to. Of particular interest is SQL Server 7.0's option to rename files before restoring them. This option lets you test your backup on the system running your SQL Server database without destroying critical data. This capability lets you save money on equipment costs and gives you peace of mind that your backup is reliable. Screen 4 shows the restore database options.

However, if you're wondering why you'd need any other backup product, look at the limitations of SQL Server's native backup. For example, you can back up only SQL Server data and not data from any other product or operating system, which means you need to schedule and run at least two backups. Also, the SQL Server backup utility's interface is different from that of every backup vendor's product, so you need to learn two programs. Finally, SQL Server native backup doesn't support autoloaders, which means you have to manually load the tapes and the program doesn't prompt you to insert another tape when one fills up. Although SQL Server 7.0's backup is faster and more user-friendly than that of SQL Server 6.5, unless you have little SQL and user data to back up, you'll want to look beyond the integrated backup utility.

To test SQL Server 7.0's native backup, I expanded the SQL Server Group and the system name. I expanded the Databases folder in Enterprise Manager and selected the TeraCLIN database. I started the Performance Monitor counters running on a separate computer. In the right pane of my test server, I clicked the Backup database link, accepted the defaults in the SQL Server Backup dialog box, and clicked OK. The backup completed in 3 hours 6 minutes. Restoring the data was easy, too. The restore took 7 hours 27 minutes, in the middle of the pack, giving SQL Server's native backup a total time of 10 hours 33 minutes.

NetBackup 3.2

VERITAS strengthened its backup family by acquiring Seagate's Backup Exec in mid-1999. With Backup Exec in the fold, VERITAS began focusing NetBackup, its enterprise backup solution, on the unique problems of large organizations, such as small backup windows, multiple autoloaders and jukeboxes, and large, untrained staffs. Reviews of previous versions of NetBackup pronounced it unwieldy, but VERITAS promised me that this latest version would be easier to configure and use.

I received NetBackup's main program on one CD-ROM and the SQL Server agent on another. I was pleasantly surprised at this release's improved wizard-based installation. Each screen explained the task that would result from changing settings in that screen. Within 5 minutes, I configured the ATL autoloader, set up a group to include all the autoloader's drives, and accepted the default of a full backup. NetBackup includes the option to install Open Transaction Manager, which is enabled by default. Open Transaction Manager lets you back up any file, even if it's open and in use. I was impressed that NetBackup includes an option to test the software by backing up the installation files during installation. None of the other products included this feature, which helps ensure that you have a good connection and backup before opening the program.

Next, I inserted the NetBackup for SQL Server agent CD-ROM into my test system and followed the four-step wizard, which integrated the agent into the main NetBackup administration tool. I rebooted the test server to reinitialize all the services and program files. When I opened the NetBackup administration tool, it presented a splash screen menu, which Screen 5 shows, with various choices for media management, activity monitoring, report management, etc. (A splash screen menu is a graphic with hyperlinks that lets you choose various menu items as you move your mouse over them.) Unfortunately, VERITAS expects you to follow a specific order in setting up your media, policies, and monitoring of backup jobs, but nothing in the documentation or on the menu specifies that order. I spent about 45 minutes working through the various options before calling VERITAS's technical support. The technician was cordial and explained that I needed to go through the menu items in a clockwise spiral, starting with Media and Device Management. When I followed this procedure, everything configured correctly and worked fine. VERITAS could save users a lot of headaches if it changed the splash screen to clearly label the order of steps in the process.

After configuring the backup, I used the NetBackup for SQL Server agent to begin backing up my database. Backing up the test database was simple; after only a few clicks, the backup started without any problems. It finished in 2 hours 22 minutes, second to NetWorker by 10 minutes. The restore also ran without any problems and finished in 6 hours 55 minutes, the fastest of the group. NetBackup's total backup and restore time was 9 hours 7 minutes, which was fastest overall by 23 minutes. One complaint I had with NetBackup was that I couldn't specify specific drive locations to do a test restore to; I had to restore to an entirely different system. Otherwise, NetBackup 3.2 performed admirably.

Dumping the Verdict

Each product I reviewed shines in some areas, but needs work in others. Unfortunately, no one product fulfilled my needs in all areas. But of the five products, VERITAS's NetBackup 3.2 provided a reasonable installation and configuration experience, support for autoloaders and their options, and the fastest combined backup and restore speed.

Many companies offer an enterprise backup solution that includes support for NT, UNIX and Novell NetWare files, Exchange, SQL Server 6.5 and 7.0, and many other operating systems and applications. Unfortunately, many of these companies loosely define the term enterprise. If you're supporting an enterprise, you'll most likely use an autoloader or jukebox. You'll want bar-coding and automatic-sorting functionality built into the backup product. You'll also want the program to be easy to install, configure, and maintain. You'll want a product that works quickly, and one that offers multiple media options and a first-rate experience with the company's technical support organization. Finally, you'll want the flexibility of expanding into other equipment or management products in the future.