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

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 61 This section will address considerations of performing on-line backups of a SQL Server database during user activity. Scenarios involving both the SQL Server native dump as well as the ARCserve assisted (database backup agent and RAID option)81 dump will be presented. When you perform a dump of a database under user activity, several things happen. These are outlined below: 1) SQL Server is executing queries sent by the users at a throughput of x-transactions per second. 2) SQL Server receives a dump database command. 3) SQL Server issues an immediate checkpoint to flush all dirty pages to disk. 4) When the checkpoint finishes, SQL Server starts dumping the contents of the database, to the destination device (in the case of a tape drive, the tape is rewound first). SQL Server continues processing user queries (including updates); however, the throughput is reduced to y-transactions per second at the beginning of the dump. 5) If a user transaction causes an update to a page not yet backed up, SQL Server will back up the old value of this page prior to changing its contents and then will allow the transaction to complete. Any transactions updating a page already backed up will proceed immediately. Thus, SQL Server insures that the backup image is consistent with the state of the database at the time the dump command was issued. 6) When the database dump begins, most of the user transaction update (write) requests will be for data pages that have not yet been backed up. As the dump process progresses sequentially through the database, an increasing number of the write requests begin to fall on pages that are already backed up and can be modified immediately. This trend results in a gradual increase in the user transaction performance as the dump proceeds. 7) When SQL Server finishes dumping the data portion of the database, it begins dumping the transaction log. At this point the user transactions resume processing at the original throughput (xtransactions per second). This rate then continues as the log dump finishes and the dump procedure comes to an end. Figure 3 represents this sequence graphically. Note the "performance curve" representative of the gradual increase in transaction rate as the dump proceeds. The exact form of this 'curve' will depend on the read-write ratio of your user transactions (reads are not affected by the dump). In our case the transaction rate went from about 10% of the original throughput to about 50% of the original throughput; the overall reads-to-writes ratio for our transactions was about 2-to-1. 81 The ARCserve products were discussed earlier in the section: Online Backup Considerations with ARCserve for Windows NT. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797