Compaq 219700-001 - ProLiant - 1500 Quick reference guide

¤ 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797
&RPSDT%DFNXSDQG5HFRYHU\IRU0LFURVRIW64/6HUYHU
;
:KLWH3DSHU
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
3UHSDUHG%\
'DWDEDVH(QJLQHHULQJ
&RPSDT&RPSXWHU&RUSRUDWLRQ
-XO\

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 2
NOTICE
The information in this publication is subject to change without notice.
COMPAQ COMPUTER CORPORATION SHALL NOT BE LIABLE FOR TECHNICAL OR
EDITORIAL ERRORS OR OMISSIONS CONTAINED HEREIN, NOR FOR INCIDENTAL OR
CONSEQUENTIAL DAMAGES RESULTING FROM THE FURNISHING, PERFORMANCE, OR
USE OF THIS MATERIAL.
This publication contains information protected by copyright. Except for internal use distribution, no
part of this publication may be photocopied or reproduced in any form without prior written consent
from Compaq Computer Corporation.
This publication does not constitute an endorsement of the product or products that were tested. The
configuration or configurations tested or described may or may not be the only available solution. This
test is not a determination of product quality or correctness, nor does it ensure compliance with any
federal, state or local requirements. Compaq does not warrant products other than its own strictly as
stated in Compaq product warranties.
Product names mentioned herein may be trademarks and/or registered trademarks of their respective
companies.
© 1997 Compaq Computer Corporation.
All rights reserved. Printed in the U.S.A.
Compaq, Systempro, Compaq Insight Manager, Fastart
Registered U.S. Patent and Trademark Office.
ProSignia, ProLiant and QVision are trademarks of
the Compaq Computer Corporation.
Microsoft, Windows, Windows NT, Windows NT Advanced Server, SQL Server for Windows NT are
trademarks and/or registered trademarks of Microsoft Corporation.
DLT is a trademark applied for by Quantum Corporation.
Compaq Backup and Recovery for Microsoft SQL Server 6.x
Second Edition (July 1997)
Document Number 444A/0797
Compaq Computer Corporation

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 3
¤ 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797
7DEOHRI&RQWHQWV
,1752'8&7,21  
&+$1*(6)52035(9,2869(56,21 
'$7$3527(&7,21&21&(376 
,
167$1&( 5 (&29(5<  
)
$8/7 7 2/(5$1&(  
'
$7$%$6( % $&.83$1' 5 (&29(5<  
%DFNXSDQG5HFRYHU\6WUDWHJLHV 
7UDQVDFWLRQ/RJYV'DWDEDVH$UFKLYLQJ 
6WRUDJH'HVWLQDWLRQV 
%DFNXS)UHTXHQF\6FKHGXOLQJDQG5HWHQWLRQ  
5HFRYHU\&RQVLGHUDWLRQV  
2WKHU%DFNXSDQG5HFRYHU\7LGELWV 
%$&.83$1'5(&29(5<&216,'(5$7,216  
7
$3( 3 52'8&76  
'/77HFKQRORJ\)RUPDWVDQG&DUWULGJHV 
,QWHUIDFH 
'/77DSH/LEUDU\ 
'HYLFH'ULYHUV 
'
$7$ & 2035(66,21  
(VWLPDWLQJ+DUGZDUH&RPSUHVVLRQ5DWLRV 
%
/2&. 6 ,=(  
2
1 / ,1( % $&.83 & 216,'(5$7,216:,7+ 64/6 (59(5 ' 803  
(QKDQFHPHQWVLQ64/6HUYHU[  
%DFNXS&KDUDFWHULVWLFVRI64/6HUYHU 
0RQLWRULQJ7UDQVDFWLRQ/RJ8VDJH  
(VWLPDWLQJ7UDQVDFWLRQ/RJ'XPS6L]H 
(VWLPDWLQJ'DWDEDVH'XPS6L]H  
&DOFXODWLQJ'DWDEDVH'XPS7KURXJKSXW 
'DWDEDVH/D\RXW&RQVLGHUDWLRQV  
2
1/,1( % $&.83 & 216,'(5$7,216:,7+ $5& 6(59()25 : ,1'2:6 17 
)HDWXUHVRI$5&VHUYHDQG2SWLRQV  
%DFNXS&KDUDFWHULVWLFVRI$5&VHUYH  
7DSH&DSDFLW\3ODQQLQJ  
%$&.83$1'5(&29(5<3(5)250$1&( 
3
(5)250$1&(2) ' $7$%$6( % $&.8321 / 2&$/ 6 (59(5  
6\VWHP&RQILJXUDWLRQ 
3RWHQWLDO%RWWOHQHFNVWR7KURXJKSXW 
$:RUGRQ6HTXHQWLDO,2 
6LQJOH7DSH'HYLFH'XPS 
(VWDEOLVKLQJ0D[LPXP7KURXJKSXWDWWKH6HUYHU1XOO'HYLFH'XPSV  
6WULSHG'HYLFH'XPSV6PDUW'LVN$UUD\V 
6WULSHG'HYLFH'XPSV'/77DSH$UUD\V 
6WULSHG'HYLFH'XPSV'/77DSH$UUD\V 
&RQFXUUHQW'DWDEDVH'XPSV 
'DWDEDVH%DFNXSWR*%'/77DSH5$,'$5&VHUYHIRU:LQGRZV17 
'DWDEDVH%DFNXSWR*%'/77DSH5$,'$5&VHUYHIRU:LQGRZV17 
3
(5)250$1&(2) ' $7$%$6( % $&.8372 5 (027( 6 (59(5  
6\VWHPDQG1HWZRUN&RQILJXUDWLRQ 

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 4
6LQJOH6HUYHU%DFNXSV%DVH71HWZRUNV  
6LQJOH6HUYHU%DFNXSV%DVH7;1HWZRUNV  
0XOWLSOH6HUYHU%DFNXSV%DVH71HWZRUNV 
0XOWLSOH6HUYHU%DFNXSV%DVH7;1HWZRUNV 
&
203$5,6212) 0 (7+2'6)25 2 1/,1( % $&.83  
64/6
(59(5 ' $7$%$6( % $&.83:,7+ 8 6(5 $ &7,9,7<  
'\QDPLFVRI64/6HUYHU%DFNXSXQGHU8VHU$FWLYLW\  
0HDVXULQJ3HUIRUPDQFHRI64/6HUYHU%DFNXSXQGHU8VHU$FWLYLW\ 
2QOLQH%DFNXSRI$FWLYH'DWDEDVH 
2QOLQH%DFNXSRI,QDFWLYH'DWDEDVH 
2
1/,1( 7 5$16$&7,21 / 2* % $&.83 3 (5)250$1&(  
&KDUDFWHULVWLFVRI7UDQVDFWLRQ/RJ'XPSV  
/RJ'XPS7KURXJKSXW 
(VWLPDWLQJ7RWDO%DFNXS7LPH 
'
$7$%$6( 5 (&29(5<  
2Q/LQH5HFRYHU\&RQVLGHUDWLRQV 
'DWDEDVH/RDG3HUIRUPDQFH 
(VWLPDWLQJ'DWDEDVH/RDG7LPH  
7UDQVDFWLRQ/RJ/RDG3HUIRUPDQFH 
2
7+(5 3 (5)250$1&( & 216,'(5$7,216  
&21&/86,21 
5()(5(1&(0$7(5,$/ 

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 5
¤ 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797
/LVWRI&KDUWV
Chart 1 - Data Compression Ratios and Performance........................................................................................ 18
Chart 2 - Throughput Variance with Block Size & Data Patterns...................................................................... 21
Chart 3 - Dump to Single Tape Device............................................................................................................. 34
Chart 4 - Null Device Dumps : Reading Data from Disk Arrays....................................................................... 36
Chart 5 - Striped Dumps to Disk Arrays ........................................................................................................... 38
Chart 6 - Striped Dumps to 15/30 DLT Tape Drives......................................................................................... 40
Chart 7 - Striped Dumps to 35/70 DLT Tape Drives......................................................................................... 41
Chart 8 - Concurrent Dumps of Multiple Databases to 35/70 DLT Tape Drives................................................ 43
Chart 9 - Database Backup to 15/30 DLT Tape RAID Using ARCserve........................................................... 45
Chart 10 - Database Backup to 35/70 DLT Tape RAID Using ARCserve ......................................................... 46
Chart 11 - Backup of One Server Across Single 10BaseT Segment .................................................................. 50
Chart 12 - Backup of One Server Across Single 100BaseTX Segment.............................................................. 51
Chart 13 - Concurrent Backup of Multiple Servers Across Multiple 10BaseT Segments................................... 54
Chart 14 - Concurrent Backup of Multiple Servers Across Single 100BaseTX Segment................................... 56
Chart 15 - Load Database: Data Restore Performance based on Disk Array Type............................................. 72
Chart 16 - Load Database: Page Reinitialization Performance based on Disk Array Type................................. 73
/LVWRI7DEOHV
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 5 - Online Dump of Inactive Database with SQL Server......................................................................... 64
Table 6 - Online Dump of Inactive Database with ARCserve ........................................................................... 65
Table 7 - Max Dump Throughput for Small Arrays .......................................................................................... 68
Table 8 - Max Transaction Log Dump Throughput with Various Commands ................................................... 68
/LVWRI)LJXUHV
Figure 1 – Network Layout: Backup of 4 Servers Across 4 Dedicated 10Base-T Segments .............................. 53
Figure 2 – Network Layout: Backup of 4 Servers Across 1 Shared 100Base-T Segment................................... 55
Figure 3 - SQL Server Backup with Database Activity..................................................................................... 62

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 6
,QWURGXFWLRQ
The purpose of 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 manner and
in a highly usable format. Any comments, ideas and suggestions are always encouraged and appreciated.
Data backup and recovery is one of the most important aspects of a database server, for it is the data that is the
most valuable asset of your company. This document will present and analyze various backup and recovery
solutions available for your Windows NT and SQL Server platform, from both the hardware and software
perspective, and provide you with choices based on performance, cost, capacity and functionality. Based on the
information presented, you will be able to make a more informed decision about the protection of your data.
When planning or implementing a comprehensive data protection system, fault tolerance often comes to mind.
Fault tolerance, whether hardware-based or software-based, complements, not replaces, backup and recovery.
While the primary purpose of backup procedures is to store data on an auxiliary storage medium for long term
retention and catastrophic failure recovery1, fault tolerance protects against loss of data due to a hardware
failure. In fact, for any business critical systems, where data availability is crucial and any downtime due to a
hardware failure is very costly, fault tolerance is a necessity. A detailed discussion of fault tolerance and
related topics is, however, beyond the scope of this document.
Replication, on the other hand, is a form of fault tolerance at the server level. It involves two or more servers,
which all contain a replica of the primary database, synchronized either real-time or near real-time2. A fail-over
server solution can also be set up where in the event of the primary server failure, a designated secondary server
mounts the storage and database of the primary server, and all user requests are then rerouted to this secondary
server. A detailed discussion on replication or fail-over servers is however, beyond the scope of this document.3
The database administrator seeking a sound backup and recovery strategy today has more options available than
ever before. Increased attention in the industry to the area of data archival has led to the emergence of powerful
and rather versatile new backup products. We have attempted to make this document as comprehensive as
possible to meet the needs of all Compaq customers who manage SQL Server environments on our hardware
products. The customer who is beginning to plan such an environment may benefit from a comprehensive
reading of the document in order to develop a greater understanding of his or her backup / recovery options.
Those who are seeking to improve upon an existing backup / recovery strategy by leveraging new technology,
may better be served by browsing the first two chapters (which stress concepts and considerations), then
referencing those sections of the third chapter (which covers methodology and performance) which are of
relevance or interest.
1Other uses of backup include protection against accidental or malicious data deletions or modifications, theft,
viruses, etc.
2It should be noted that the native replication capability of SQL Server 6.x is not specifically designed for use
as a ‘hot backup’ system.
3For more information on fail-over server solutions from Compaq, refer to the document Configuring Compaq
Recovery Server with Microsoft SQL Server 6.5.

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 7
¤ 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797
&KDQJHVIURP3UHYLRXV9HUVLRQ
The information contained in this documents updates and adds to the information contained in the paper
Compaq Backup and Recovery with Microsoft SQL Server, which was released in Oct. 1994. The additions in
this paper serve mainly to inform the reader on new backup related software and hardware technologies that
have appeared on the market since then. These technologies include:
Software:
  SQL Server 6.0 and 6.5 from Microsoft.
  ARCserve 6.0 for Windows NT from Cheyenne.
  ARCserve Backup Agent (DBagent) for SQL Server from Cheyenne.
  ARCserve RAID Option (Tape Array Software) for Windows NT from Cheyenne.
Hardware:
  10/20-GB, 15/30-GB, and 35/70-GB DLT Tape Drives from Compaq
  DLT Tape Arrays from Compaq
  Fast-Wide SCSI-2 Technology from Compaq
  SMART-2 Drive Array Controller from Compaq
  100-Mbit networking products from Compaq
Details on each of the above technologies will be provided later in this paper. Of these technologies, the most
significant for the purposes of this paper (backup and restore) have been the introduction of Microsoft SQL
Server 6.x and the Compaq DLT tape drives. The previous paper, Compaq Backup and Recovery with
Microsoft SQL Server, provided information pertaining to results 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 been retained in this document, including the entire first chapter
entitled Data Protection Concepts.4
4Modifications have been made as needed.

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 8
'DWD3URWHFWLRQ&RQFHSWV
In the Data Protection Concepts section of this document, we’ll characterize various data protection and
recovery methods that are available to you in this environment. We’ll touch on topics such as instance
recovery and fault tolerance, but our main goal is to outline your options in the area of database backup and
recovery.
,QVWDQFH5HFRYHU\
Automatic data protection and instance recovery consist of the system’s ability to preserve the effects
of all committed transactions and insure the database is in a consistent state after recovery from any
single point of failure. This single point of failure includes a hardware component failure, software
component failure, power loss, etc.
SQL Server provides this automatic data protection and instance recovery through its write-ahead log,
called the transaction log, where SQL Server records changes before it modifies the actual data pages.
In the case of a server failure and recovery, SQL Server, upon startup, uses the transaction log to roll
back uncommitted transactions, and roll forward transactions that were committed but not applied to
the data files.
All data definition changes (such as creating or dropping tables, views, stored procedures, rules, etc.)
and all data manipulation changes (inserts, deletes and updates, but not selects) are logged in the
transaction log. Certain other events such as checkpoints and dumps are logged as well. Naturally, any
data definition changes, checkpoints, dumps, etc., will not be rolled back or forward.
This data protection scheme cannot be turned off, and guarantees that data remains consistent at any
point in time. The automatic data protection and instance recovery, in terms of the write-ahead
transaction log, is inherent to SQL Server. More detailed information about transaction logging can be
found in the Microsoft SQL Server manuals and other publications.
)DXOW7ROHUDQFH
Fault tolerance and data archival should coexist on the same system. Fault tolerance, both hardware
and software based, provides you with immediate protection against a hardware or a software
component failure. In conjunction with hot-pluggable drive systems, hardware-based fault tolerance
allows for continuous data availability, maximum performance5and automatic data recovery. Database
archival allows for a long term data retention, and recovery from operational errors and catastrophic
failures.
The drive subsystem has a substantially higher chance of failing than any other component in the
system. Here, various levels of fault tolerance are available, ranging from no fault tolerance to fault
tolerance for the entire drive subsystem. Naturally, higher levels of fault tolerance carry higher
implementation costs. Many times, a high level of fault tolerance can seem to be a rather expensive
solution. However, you need to weigh the cost of implementing a good, fault tolerant system against
the cost associated with down-time. Sites requiring access to data with minimal down time must
implement hardware fault tolerance for the entire drive subsystem.
With fault tolerance implemented on the entire drive subsystem, a drive failure will not interrupt any
operations and will not cause any data loss. With the Compaq SMART and SMART-2 Array
controllers and the hot-pluggable drive subsystem, a failed drive can be replaced and fault tolerance
restored without taking the system off-line.
5For performance characteristics of various RAID levels of the Compaq SMART Array Controller, and
configuration considerations, refer to the document Configuring Compaq RAID Technology for Database
Servers.

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 9
¤ 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797
With disk fault tolerance installed on the transaction log volume only, and with systematic database
and transaction log dumps, your data is also safe. If you have a full database dump and subsequent
transaction log dumps, you can easily restore your data in case of a failed data drive. This recovery
involving the data drive will, however, require the system to be brought off-line. If you lose a
transaction log drive, this volume is protected by fault tolerance, and recovery will consist of only
replacing the failed drive and letting the controller rebuild the new drive to full fault tolerance.
With no fault tolerance installed, you run into a risk of losing transactions even in the case of frequent
backups. Any changes to your database that occurred after your transaction log dump will be lost if
your transaction log drive fails. In addition to a high possibility of data loss, recovery will require the
system to be taken off-line.
More detailed information on the backup and recovery procedures in various failure scenarios, refer to
the SQL Server Administrator’s Guide.
'DWDEDVH%DFNXSDQG5HFRYHU\
Your ability to recover your data depends entirely on your data protection scheme. This protection
scheme can consist of various methods, such as implementing database and transaction log backups
alone, combining these backups with fault tolerance, or even taking advantage of replication. In this
section, we’ll provide you with some more insight into backup and recovery options available with
Microsoft SQL Server for Windows NT, and refer you, whenever possible, to a more detailed source of
information.
%DFNXSDQG5HFRYHU\6WUDWHJLHV
Database backup and recovery consists of the database administrator archiving data onto a secondary
storage on a scheduled interval, and in the case of a failure, restoring the data from this secondary
storage. Secondary storage can be a remote backup server, a local hard disk device, a local tape device
or a local optical disk device.
Database archiving can be accomplished in two ways. The database administrator can shut down SQL
Server, thus closing all database and transaction log files, and then perform a file-based backup of all
SQL Server devices. This type of backup is called off-line backup. It is necessary to stop SQL Server
to gain access to the files, since SQL Server keeps all database and transaction log files open with
exclusive access as long as the database engine is running. Therefore, any attempt to backup these files
while SQL Server is running will fail.
Or, the database administrator can use SQL Server’s backup facility to dump the contents of the
transaction log, the database, or both, while SQL Server remains running. This type of backup is called
dynamic backup or on-line backup. SQL Server backup operation is referred to as a dump, and the
restore operation is referred to as a load.
Choosing the correct backup and recovery strategy involves a careful analysis of your environment.
Some business critical environments can accept only one strategy: on-line backup and recovery.
Others can employ either one. Below we present some of the major characteristics of each strategy,
and in the rest of the document we provide more detailed information, especially in the area of
performance.6
2II/LQH%DFNXS&KDUDFWHULVWLFV
Shutting SQL Server down for archiving purposes has the following characteristics:
  Any and all user databases remain inaccessible for the duration of the backup operation. SQL
Server opens all databases at startup and keeps them open whether they are being accessed or not.
Therefore, you cannot use typical file backup utilities while SQL Server is running.
6This document will provide a performance analysis of online backups only.

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 10
  Any file backup utility will backup the files in their entirety. To illustrate our point, let’s use an
example where we allocate a 100MB database device, create a 100MB database on this device,
and store 50MB of data in this database. From the operating system’s perspective, we have
100MB worth of data, which we need to back up. However, we end up backing up about 50MB of
data and 50MB of empty database structures, increasing both the duration of the operation and
amount of the backup storage used or needed.
  You have to back up all database and transaction log files in order to recover. SQL Server keeps
track of all user databases in the master database, and time stamps all files. If you restore only
selected files, SQL Server will detect inconsistency between the files and the information in the
master database. Likewise, in order to restore a database, you have to restore all database and
transaction log files to keep them all consistent.
  Incremental backups (backups of changes since the last full backup) or partial backups (backups of
selected subsets of data, such as a particular database) are not possible.
  Disk I/O associated with file-based backup utilities typically occurs in 64KB blocks, thus
improving performance.
  Some file backup utilities support software data compression. Software data compression can
yield the fastest backup throughputs, but is demanding of the system CPU.
  Off-line backups can take advantage of verifying the data (backup with verify) on the tape after
backup. This guarantees data integrity on the tape.
  With Cheyenne ARCserve for Windows NT and Compaq TurboDAT AutoLoader or Compaq DLT
Library, you can group tapes together to increase the capacity. Groups of tapes appear to the host
as one continuous tape with an increased capacity, and the next tape in the group is automatically
and transparently loaded once one tape is filled up.
  File-based backup utilities cannot be used to archive a database that has been created on raw
devices. Creation of database 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. The only
way to archive such a database is by using online backup.
2Q/LQH%DFNXS&KDUDFWHULVWLFV
SQL Server-based database and transaction log backups have the following characteristics:
  SQL Server will dump a database or a transaction log while permitting access to any and all user
databases. All processing in databases not being dumped continues normally. Any changes7to
databases not being dumped remain unaffected. In the database being dumped, any change to a
data page already dumped occurs immediately. This change, however, will not be included in the
backup image. If a transaction intends to update a data page or an index page not yet backed up,
SQL Server places this transaction on an internal queue, dumps that page before allowing any
change to this page, and then proceeds with the transaction. In other words, the database image is
the data captured at the instant the DUMP command is issued. We will discuss performance
implications of this type of backup later in the document.
  SQL Server will backup only pages containing data - both data and index pages. As a result, no
unnecessary data will be backed up. However, during the load operation, SQL Server will
initialize all unused pages in addition to reloading all used data and index pages, thus consuming
more time as compared to the dump operation.
7We are only concerned with insert, delete or update requests. Look-up (read-only) queries don’t concern us
here since they have no affect on the user data.