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

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 75 loaded into an empty database with 50 GB of total data space and 2 GB of total log space. The database server is a Proliant-5000 with a Smart-2/P array controller. The database is constructed across 10 disk drives configured in RAID-5, spanning both SCSIports of the Smart-2/P (5 per port). 2 more drives on the Smart-2/P comprise the volume for the transaction log, configured in RAID-1. The system has available 4 DLT drives which are connected to 2 SCSI-2/P controllers (the same tape configuration was used to back up the system). The amount of time needed to complete this load needs to be estimated. We must compare the maximum throughput of the disk drive volumes to the maximum throughput of the tape array. Chart-15 shows that a 10 drive RAID-5 array can write around 10.6 GB/hr (from multiple restore threads93), so the data volume cannot restore very fast. The log volume is slow also, capable of only about 7.5 GB/hr. Chart-6 shows that it is possible to achieve around 16.5 GB/hr with 4 DLT drives, which exceeds the rate of both data and log volumes. Therefore the load time must be calculated as: [27.3 / 10.6] + [.85 / 7.5] + [(50 - 27.3) / 22.9] + [(2 - .85) / 16.7] = 3.75 hours, or about 3 hours & 45 minutes. The reinitialization rates of 22.9 GB/hr and 16.7 GB/hr for the data and log volumes respectively, were obtained from Chart-16. We can compare this time to the 1.72 hour time that was needed to dump the database on the same system (see example in presented in 'Estimating Total Backup Time'). The database load time is over 2 times greater than the dump time, due to the restore performance of the disk volumes and the need to reinitialize unused pages. Now lets say that the drive volumes for this system were configured as 10 drives in RAID-1 for the data, and 4 drives in RAID-1 for the log, so that the restore throughputs increase to 24.5 and 12.6 GB/hr respectively. Both the database and log restore are now limited by the tape subsystem, so that the time calculation becomes: [(27.3 + .85) / 16.5] + [(50 - 27.3) / 32.7] + [(2 - .85) / 28.5] = 2.44 hours, or about 2 hours & 26 minutes. The reinitialization rates of 32.7 GB/hr and 28.5 GB/hr for the data and log volumes respectively, were again obtained from Chart-16. Keep in mind that all such calculations yield only rough estimates of the required time, and should be used conservatively. Figuring out the total time needed to load a database is a complex procedure, and may be quite specific to individual systems. Customers should perform their own tests to measure actual time, and in a mission critical environment should allow for extra time to incorporate any unforeseen performance degradation. Depending upon the type of backup strategy which you have implemented, recovery of a database may not be complete after restoration of data and log pages from a full database dump. There may be successive transaction log-only dumps which then need to be loaded, in the order that they were performed. For a discussion of backup strategy involving transaction log dumps, as well as details on the actual transaction-logging process, please see the chapter entitled Data Protection Concepts. This section will attempt to give an idea of the amount of time required to load a transaction log dump. In the previous section, the performance of loading a transaction log as part of a full database load was considered. That process consists of simply restoring the dumped log pages from media into the database's syslogs table. Loading a transaction log-only image (i.e: one performed with a DUMP TRANSACTION command) however, consists of restoring the log as well as re-executing all of the data changes recorded in the log - a process known as "recovering the database" from the transaction log. Also called "applying" the transaction log, this process rolls forward all committed transactions 93 This number is a 'worse case' since the chart was based upon 8 restore threads and the example uses only 4. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797