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

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 31 In this section of the document, the performance aspects of various backup and recovery methods are analyzed, and some guidelines provided to help you choose the best method for your environment. We have focused our efforts on the software products discussed earlier: SQL Server 6.x and its native backup capabilities40, and ARCserve 6.0 for Windows NT, which works in conjunction with SQL Server using the Backup Agent component. To represent backup throughput, the GB/hour metric has been chosen, and a MB/sec rate may occasionally be referenced (conversion is GB/hr = MB/sec * 3.6). DISCLAIMER: The performance numbers presented in the following sections are to be used in relative comparisons between different configurations, and they are included to give the reader an idea which method and/or configuration achieves better throughput rates. The throughput rates can vary with the nature of data being backed up and with other variables. This is particularly true when using data compression. The nature of data being backed up determines the effectiveness of data compression algorithms, and in turn will affect the backup throughputs. Finally, these results were achieved using a dedicated SQL Server system experiencing no other application activity, and with no user activity occurring on the database(s) under test, unless otherwise stated41. This section offers a performance analysis of on-line database backups, or dumps, when done to storage devices located on the same (local) server. These types of backups usually result in the best performance, providing archival of data in the least amount of time. We will look at backup to various storage devices including disk arrays, 15/30-GB DLT tape, and 35/70-GB DLT tape. Also, different methods of implementing local database backup will be considered, such as using SQL Server striped dumps, dumping multiple databases concurrently, and using a third party application and backup agent. The hardware we used for the performance testing conducted for this section consisted of the following: Compaq Proliant 5000 Server with 4 P6/166 processor boards. Compaq SMART-2/PCI Drive Array controllers.42 Compaq 32-bit Wide-Ultra SCSI PCI controllers.43 Compaq 15/30-GB and 35/70-GB DLT tape drives. Compaq DLT Tape Array and DLT Tape Array II cabinets.44 40 Results including Microsoft SQL Server 4.21x were covered in the previous paper, Compaq Backup and Recovery with Microsoft SQL Server (4.21x), document #250A/0894. 41 See section entitled SQL Server Database Backup with User Activity. 42 For peak performance in Pentium Pro systems such as the Proliant 5000, it is important to ensure that a WideUltra (SCSI) capable Smart-2/P Array controller is used, as opposed to earlier, Fast-Wide only versions. This is because of differences in the SCSI chipsets (53C875 vs. 53C825) that affect PCI bus utilization and thus data transfer rates. Note however, that we are using external storage cabinets for the disk drives, and so the controller actually operates in Fast-Wide mode as opposed to Ultra mode. 43 For peak performance in Pentium Pro systems such as the Proliant 5000, it is important to ensure that a WideUltra SCSI controller is used, as opposed to earlier, Fast-Wide versions. This pertains to the same issue encountered with the Smart-2/P controllers (see previous footnote), and was explained in the second chapter under the section entitled Interface. 44 DLT Tape Array cabinets have Fast-SCSI-2 interfaces and hold 10/20-GB or 15/30-GB DLT drives. DLT Tape Array II cabinets have Wide-SCSI interfaces and hold 35/70-GB DLT drives. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797