Compaq ProLiant 1000 Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 67

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 67 Like the database itself, the transaction log can be backed up with the database still online using SQL Server dump functionality. A transaction log can be backed up as part of the database, or by itself if it resides on a separate database device. The section in the first chapter entitled Transaction Log vs. Database Archiving discusses the uses of transaction log-only backups. There are a number of different ways in which a database's transaction log can be dumped, and the performance can differ depending upon which of these methods is used. The commands that can be issued to SQL Server to dump a transaction log are listed below. For more details concerning these commands or when to use them, consult the SQL Server Administrator's Companion. DUMP DATABASE: The transaction log is dumped as part of the full database dump. After transferring to tape the data pages in all of the other tables, SQL Server will then begin reading pages from the syslogs table and transferring them to tape as part of the dump. When done in this manner, the transaction log is read sequentially using large size (up to 16KB) asynchronous requests just like the rest of the database, and is not truncated. DUMP TRANSACTION: The transaction log is dumped by itself. As the log table is dumped, it is simultaneously purged of committed transactions, or truncated. The truncation process forces the need to do additional, smaller size I/O (including 2KB writes) to the log volume. Although the SQL Server Read Ahead Manager85 is invoked to assist with the truncate, the additional I/O required limits the overall performance of the dump transaction operation. DUMP TRANSACTION WITH NO_TRUNCATE: The transaction log is dumped by itself, but is not purged of committed transactions. This command eliminates the need to perform additional I/O required by a truncate. However, all log pages are read using single page (2KB size) synchronous requests, therefore limiting the performance. DUMP TRANSACTION WITH TRUNCATE_ONLY or DUMP TRANSACTION WITH NO_LOG: Both of these commands will purge the log of its committed transactions without creating a backup image. Since no data is actually sent to a dump device, the performance of this operation can be faster than for other dump transaction operations, but is still limited by the need to do some smaller size I/O. These characteristics can apply when using third party online backup tools as well, such as Cheyenne's ARCserve for Windows NT and its Database Backup Agent, which initiate a DUMP command within SQL Server86. To determine the maximum throughput possible for each of the above operations, we issued the commands using a NULL device instead of a tape or disk drive. When dumping the transaction log as part of the database, it is possible to see throughput as high as that of the database dump itself, as it is essentially part of the same process. However, as the transaction log usually is stored on a separate, often smaller disk volume, the rate at which it can be read may differ from the rate at which the rest of the database is read. For high-speed backups of the transaction log, we recommend a log volume consisting of at least 4 Fast-Wide SCSI-2 drives in a RAID-1 configuration, or 3 such drives in a RAID-5 configuration. The below table shows some 85 The Read Ahead Manager is a mechanism which can issue separate thread(s) to pre-fetch data during an operation involving sequential reads. 86 The ARCserve database agent performs SQL Server transaction log dumps similar to the way in which it does database dumps. See section entitled Online Backup Considerations with ARCserve for details. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797