Compaq ProLiant 1000 Compaq Backup and Recovery for Microsoft SQL Server 6.X
Compaq ProLiant 1000 Manual
View all Compaq ProLiant 1000 manuals
Add to My Manuals
Save this manual to your list of manuals |
Compaq ProLiant 1000 manual content summary:
- Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 1
1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 2
distribution, no part of this publication may be photocopied or reproduced in any form without prior written consent from Compaq Computer Corporation. , ProLiant and QVision are trademarks of the Compaq Computer Corporation. Microsoft, Windows, Windows NT, Windows NT Advanced Server, SQL Server for - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 3
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 3 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 4
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 4 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 5
Page Reinitialization Performance based on Disk Array Type 73 Table 1 - Tape Cartridge Support Matrix ...15 Table 2 - Number of DLT Drives Needed for Desired Throughput 59 Table 3 - Online Dump of Active Database with SQL Server 63 Table 4 - Online Dump of Active Database with ARCserve 63 Table - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 6
this document is to provide the reader with information on backup and recovery technology available for and specific to the Compaq ProLiant family of servers and Microsoft SQL Server for Windows NT. It is our desire to deliver the best technical information possible on a specific topic in a timely - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 7
achieved with SQL Server version 4.21a and the Compaq 4/16GB TurboDAT (and earlier) tape drives. Readers interested in these earlier technologies should refer to that paper. However, since some of the information from the previous paper continues to apply to the current environment parts of it have - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 8
is inherent to SQL Server. More detailed information about transaction logging can be found in the Microsoft SQL Server manuals and other publications. Fault any operations and will not cause any data loss. With the Compaq SMART and SMART-2 Array controllers and the hot-pluggable drive subsystem, - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 9
procedures in various failure scenarios, refer to the SQL Server Administrator's Guide. Your ability to recover your data depends entirely on utilities while SQL Server is running. 6 This document will provide a performance analysis of online backups only. 1997 Compaq Computer Corporation, All - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 10
the tape. With Cheyenne ARCserve for Windows NT and Compaq TurboDAT AutoLoader or Compaq DLT Library, you can group tapes together to increase the device files on 'raw' (unformatted) partitions is supported by both Windows NT and SQL Server to enhance I/O performance by bypassing the file system. - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 11
Server-based backups in greater detail later in this document. SQL Server currently does not support the autoloading and/or cataloging capabilities of the Compaq of your needs, along with techniques outlined in the System Administrator's Guide, will help you determine a plan that is best for you. - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 12
on another server. This device can consist of a hard disk drive or a rewritable optical disk drive. SQL Server does not support direct Server Executive service or third party software. Refer to the System Administrator's Guide are available from Compaq or Cheyenne for use with Cheyenne ARCserve, and - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 13
to test your backup AND recovery strategy! More details on various SQL Server-based recovery scenarios, load requirements and other considerations can be found in the System Administrator's Guide or the manuals available with your backup software. We will analyze some considerations and performance - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 14
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 14 restore will fail, and you will have to use a raw data import/export tool such as SQL Transfer Manager or BCP.12 12 Even these utilities have certain limitations. See the Administrators Guide for details. - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 15
large, mission critical databases like those found in many SQL Server environments. Table 1 summarizes the tape cartridge support of tape drives evaluated in this document: Table 1 - Tape Cartridge Support Matrix Tape Drive Tape Cartridge(s) Supported Compaq 10/20-GB DLT Drive DLT Tape III (10 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 16
Wide-Ultra SCSI controllers on Pentium-Pro based Proliant systems if peak performance is desired. Multiple drives can exist on the same SCSI bus by Daisy Chaining them with the appropriate cables. Compaq also provides DLT Tape Array cabinets which support up to four internal DLT tape drives. The - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 17
a 32-bit Compaq SCSI-2 controller such as the integrated controllers found on Proliant and ProSignia servers, make sure that the Compaq 32-Bit SCSI-2 use data compression by manually de-selecting the compression option on the drive, or using tape software that supports data compression control. The - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 18
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 18 initialized and never used pages the 20 The case of 'unallocated space' showing increased compression does not apply to a SQL Server based (online) backups. 21 Streaming means that the system can and does deliver enough information - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 19
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 19 tape drive writes out the current line backup method, your backup throughput can be limited by SQL Server and how fast it can perform reads from the disk subsystem. For SQL Server version 6.0 and above however, we will see that in most - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 20
prior to 6.0 would use a hard-coded block size of 512 bytes, which had serious performance repercussions in the case of DLT tape drives. As a result, Compaq recommends that DLT drives not be used with these earlier (i.e: version 4.21x) SQL Servers. 512 byte blocks work fine for DAT and QIC drives. - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 21
of 512 bytes, which had serious performance repercussions in the case of DLT tape drives. As a result, Compaq recommends that DLT drives not be used with these earlier (i.e: version 4.21x) SQL Servers. 512 byte blocks work fine for DAT and QIC drives. 26 Exceptions are when writing headers, trailers - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 22
. This is useful when software vendors or customers want to write their own application to accept data from a SQL Server dump process either locally or across the network. In addition to named pipe dumps, SQL Server 6.x continues to support dumps to network disk devices, as did previous versions. - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 23
database during dumps can also benefit from read-ahead at the hardware level, such as the Compaq SMART-2 Array Controller which pre-fetches data into its on-board cache module. During a dump, SQL Server reads the data pages into in-memory buffers; with one buffer being allocated per backup thread - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 24
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 24 When the transaction log fills up and SQL Server backup. Refer to the System Administrator's Guide for more details and important considerations regarding use Executive The SQL Executive Service in SQL Server 6.x includes several ' - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 25
done which requires a subsequent database dump to preserve the usability of future transaction log dumps. For more information on this subject, refer to the SQL Server Administrator's Companion. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 26
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 26 of pages reported as dumped in the SQL Server error log (or Windows NT Application event log)33, then multiply this number by '2048'. The time required to perform the dump can most accurately be acquired from the SQL Server error log - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 27
job can continue uninterrupted across all the tape drives in that group. ARCserve supports up to 8 tape drives per server. 35 Cheyenne Software, Inc. is now a wholly owned subsidiary of Computer Associates International, Inc. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 28
native SQL Server product. Auto Changer - This option is for use with a tape autoloader or tape library such as the DLT 15Cartridge Library from Compaq. The current SQL Server 6.x Task Manager, part of the SQL Executive service, provides a more limited scheduling scheme for native SQL Server backup - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 29
ARCserve 6.0 or its Option software, please refer to the User Guide for the corresponding product. The ability to stripe data across multiple of SQL Server. 39 The backup_threads parameter under SQL Server must be set to at least '3' when using ARCserve. 1997 Compaq Computer Corporation, - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 30
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 30 GigaBytes of storage in an ARCserve RAID-0 tape array. This applies to SQL Server tape stripe sets as well, which perform data striping similar to ARCserve RAID-0 arrays. ARCserve RAID-5 arrays will provide the capacity of n-1 drives, - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 31
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 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 32
SQL Server 6.x Page 32 Compaq 4.3 GB Fast-Wide SCSI-2 disk drives (7200 RPM). Compaq Note that the transaction log dump was not measured as part of the database dump tests (see section on Calculating insufficient spindles in database volume to service required number of read requests from - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 33
service write requests (1.25/2.50 MB/sec49 for DLT Tape drives). Intermediate (Link): Network - insufficient bandwidth (1.25/12.5 MB/sec50) in the network cable. NIC Cards - limited capability of network controllers (at server 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 34
Compaq Backup and Recovery for Microsoft SQL Server 6.x Chart 3 - Dump to Single Tape Device Page 34 size (please see the earlier section entitled Block Size). The larger read/write blocks specified by SQL Server 6.5 allow the DLT to record the data at a much faster rate - especially in the case - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 35
attempt to determine the maximum read throughput achievable during a database dump on a Proliant 5000 system. In large, this involves increasing the read capacity of the The 'diskdump' device in SQL Server is a 'null' device. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 36
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 36 The tests were done as striped dumps the case in the following sections, where striped dumps are done to various hardware storage devices. SQL Server allows the use of "disk dump devices" for backup purposes, as well as tape. The - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 37
Compaq Backup and Recovery for Microsoft SQL Server devices were placed on the secondary PCI bus of the Proliant 5000, while the controllers holding the database itself were placed be limited by the PCI bus bandwidth. 60 The on-board controller cache was manually configured for 100% write caching (vs - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 38
Compaq Backup and Recovery for Microsoft SQL Server 6.x Chart 5 - Striped Dumps to Disk Arrays Page 38 As expected, performance increases as the drive). For a discussion on the performance implications of RAID technology, see the Configuring Compaq RAID Technology for Database Servers white paper. - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 39
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 39 goes up almost 50% to 47 GB/hr which is close to what it was with RAID-0. The average CPU usage seen during the third set of tests (for both types of arrays) was around 60 tape drives are used as part of a single tape device 'stripe set - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 40
Compaq Backup and Recovery for Microsoft SQL Server 6.x Chart 6 - Striped Dumps to 15/30 DLT Tape a single system for this type of backup activity. Production environments that wish to use the SQL Server dump but that require a backup rate 30GB/hr or greater to meet their time constraints, should - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 41
Compaq Backup and Recovery for Microsoft SQL Server performance numbers seen when dumping to tape drives supporting hardware compression. We have found however, that performance ) on the secondary PCI bus. All tape drives are used as part of a single tape device 'stripe set'. Since up to 4 tape drives - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 42
disk head contention. 66 See earlier section entitled Establishing Maximum Throughput at the Server. 67 When SQL Server 6.5 database dump tests were run on previous generation (EISA-based) Compaq servers (i.e: the Proliant 4500), the maximum throughput achieved was about 25 GB/hr, due mainly to - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 43
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 43 The final point above deserves further discussion. When a single database is being dumped to a single drive the throughput is 18.6 GB/hr, and when dumping two 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 44
system. The ARCserve applications act as a 'go-between' from SQL Server to the tape subsystem. For these types of operations utilizing ARCserve to which resided on the primary PCI bus of the Proliant 5000. All of the DLT drives resided in Compaq DLT Tape Array cabinets (4 drives per cabinet). Each - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 45
the named pipes mechanism allows the ARCserve backup agent to process the incoming data and send it out to tape faster than the native SQL Server process can. However, this mechanism also demands much more CPU power; with 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 46
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 46 eight DLT drives our four Pentium Pro/166 processors were almost saturated (CPU usage split between the SQL Server and ARCserve threads). This high load on the system CPU's could cause performance degradation to other application - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 47
ARCserve products to perform concurrent backups of databases to a remote server, will be shown in the following sections. 69 CPU usage for such a scenario would not be expected to exceed around 50% in a 4P/166 Proliant 5000. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 48
this section consisted of the following: Storage Server: Compaq Proliant 5000 Server with 4 P6/166 processor boards. 4 Compaq 32-bit Fast-Wide SCSI-2 PCI controllers (C875). 1 Compaq SMART-2 Array PCI controller. 8 Compaq 15/30-GB DLT tape drives. 1 Compaq 35/70-GB DLT tape drive. 70 Alternatively - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 49
written out from memory to peripherals on PCI bus 2). Database Server: Compaq Proliant 5000 Server with 4 P6/166 processor boards. 1 Compaq SMART-2 Array PCI controller (C875). 1 Compaq External Storage cabinet with 5 Fast-Wide disk drives. 1 Compaq NetFlex-3 PCI 10/100 TX UTP card. This was - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 50
selected was TCP/IP. Networking hardware included: Compaq NetFlex-3 and Netelligent network interface cards. Compaq Netelligent 10Base-T Repeater. Compaq Netelligent 100Base-TX Repeater. CAT 5 UTP cables (supports both 10 Mbs & 100 Mbs). Both servers were cabled to the 10Base-T repeater for the - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 51
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 51 over 75% average usage with just this oversimplified prediction, as shown by the below chart: Chart 12 - Backup of One Server Across Single 100BaseTX Segment Comparing Chart-12 to Chart-9, the throughput seen while backing up data - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 52
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 52 network utilization, it was never seen to exceed an average of 25%. The CPU usage at both the source and destination servers . Four database servers are placed on the network: two Proliant 5000 systems and two Proliant 2500 systems73. - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 53
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 53 Figure 1 - Network Layout: Backup of 4 Servers Across 4 Dedicated 10Base-T Segments At the storage server, four tape drive "groups" are set-up under ARCserve, one for each server to be backed up. Each drive group contains only a - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 54
ratio between systems to be backed up vs. network cards in the storage server, although all data transfers occur over the same wire. The reason for having more than one NIC on the 74 Our configuration did not include performance testing with the Compaq Netelligent Dual 10/100TX PCI UTP controller. - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 55
More than 2 RAID-5 arrays could not be configured as ARCserve supports only 8 drives and each RAID-5 array requires at least 3 server to receive data from up to 4 other servers. 76 The PCI % usage shown in the chart was taken as an average of the load observed between the 2 peer-buses. 1997 Compaq - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 56
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 56 Chart 14 - Concurrent Backup of Multiple Servers Across Single 100BaseTX Segment The benefits of this concurrent strategy are obvious, as greater throughput is derived from the tape server the server and written out to tape, and 60% - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 57
bridge or routing device is configured in between the source and destination nodes77. 77 Bridges or routers placed in between the tape backup server and the other servers can significantly degrade network backup performance. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 58
Backup and Recovery for Microsoft SQL Server 6.x Page 58 The DLT drives are Compaq's highest performing tape backup solution, and when configured as an array with the use of SQL Server 6.x striping or ARCserve RAID Option this performance is scaled even further. By adding enough of the drives - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 59
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 59 temporary disk storage can be removed by using Cheyenne ARCserve, which can 'pull' data from multiple database servers and sent directly to tape on the Backup server single database on a Proliant 5000 server to multiple locally - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 60
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 60 Note that the MIS manager has decided database backup, then the DLT 15-Cartridge Library along with ARCserve (and its autochanger support) may have provided a better solution, allowing for unattended backups with even more capacity - 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 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 62
Finally, we measured throughput (both user transactions per second throughput and GB/hour backup throughput) during a database dump to two Compaq 15/30GB DLT drives, with the SQL Server native striped dump as well as the ARCserve assisted dump. During each of the phases we also noted changes in the - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 63
was used for this testing was a Proliant 5000 server with two (2) Pentium Pro/166 processors and 1 GB of RAM. The system had enough memory and Rate: Time: Usage: Throughput: No User n/a Transactions ~ 0% No User n/a Transactions ~60% 10.0 GB/hour 33% of Maximum .12 sec ~32% 3% to 12% - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 64
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 64 When dumping an active database with SQL Server, the user OLTP-type environment with numerous read/write transactions. A Decision Support-type environment involving large read-only queries may behave differently since reads are - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 65
: Usage: Throughput: No User n/a Transactions ~ 0% No User n/a Transactions ~ 60% 10.0 GB/hour 25% of Maximum .12 sec ~26% 25% of service all threads and I/O is separated. If CPU usage becomes high however, the backup throughput will suffer (with the SQL Server dump 1997 Compaq - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 66
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 66 process), or the transaction rate will suffer along with a reduction in backup throughput (with the ARCserve backup agent). - 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 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 68
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 68 common transaction log volumes (fault and in fact went down - when a striped dump was done to multiple DLT drives! The SQL Server Dump Transaction process does not seem to be able to keep the tape drives streaming for a striped - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 69
do a full database backup of a 27.3 GB database (used space) with a 850 MB transaction log (used space), using SQL Server dump. 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 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 70
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 70 16.5 GB/hr with 4 DLT drives, which exceeds occurring between this load will be lost. To prevent this situation from happening, start SQL Server in a single user mode. During a restore operation, the damaged database must be dropped, - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 71
Server 6.0), in order to skip the initialization of data pages. Since unused data pages are initialized as part Microsoft SQL Server manuals and the Microsoft SQL Server, Backup DLT tape drives using the native SQL Server load functionality. The system was a Proliant 5000 with a single Smart-2 SCSI - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 72
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 72 These results do not include any activity needed to search, replace or rewind89 the tapes, which would naturally prolong the restore process. Chart - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 73
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 73 Chart 16 - Load Database: Page 8 15/30-GB DLT's (11.5 GB/hr). Interestingly, using the ARCserve Backup Agent for SQL Server with 8 DLT drives yielded significantly better restore performance to the RAID-5 array: 16.6 GB/hr with 14 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 74
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 74 The page reinitialization phase of the log pages. The charts above apply to both data and log restores (meaning log restoration done as part of the LOAD DATABASE command). However, since the array used to house the transaction log may - 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 section, the performance of loading a transaction log as part of a full database load was considered. That - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 76
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 76 that were recorded (but not previously applied), and rolls back any transactions that were uncommitted at the time of the log dump. During the first phase of the transaction log load, SQL Server The system was a Proliant 5000 with 2 P5 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 77
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 77 RAID 5 data array: 1 hour, 32 minutes. This will not necessarily be the case necessary Re-creation of the database devices and the database itself, if necessary. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 78
The intention of this document is to communicate relevant information on backup and recovery technology available with Microsoft SQL Server for Windows NT and Compaq server products. We have presented different methods for database backup, and compared their functionality and performance (see the - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 79
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 79 13. When estimating the amount of time needed to complete a full database revisions of this document and related new information products available to you. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797 - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 80
, published in February 1994 (Document Number 267A/0294) Compaq Backup and Recovery Of Microsoft SQL Server for Windows NT, First Edition, published in October 1994 Configuration and Tuning of Microsoft SQL Server 6.5 for Windows NT on Compaq Servers, Third Edition, published in June 1996 (Document - Compaq ProLiant 1000 | Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 81
Other: RDBMS Microsoft SQL Server Sybase System 10 Oracle7 Other: Processing Type On-Line Transaction Processing Decision Support Batch Processing Other: Please Microsoft SQL Server Sybase System 10 Oracle7 Other: Additional Comments: Return to: Database Engineering Compaq Computer Corporation
1997 Compaq Computer Corporation, All Rights Reserved
Doc No 444A/0797