HP ProLiant 1500 Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 58

Compaq Backup and Recovery for Microsoft SQL Server 6.x, SQL Server Administrator's, Companion

Get HP ProLiant 1500 PDF manuals and user guides View all HP ProLiant 1500 manuals
Add to My Manuals
Save this manual to your list of manuals

Page 58 highlights

Compaq 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, we have shown that we can attain backup throughput that is limited only by the ability of the disk subsystem to deliver the data. Of course disk subsystem data-transfer rates will continue to increase as more advanced SCSI and Fiber Channel technologies emerge, and these combined with high-end DLT drives such as the 35/70-GB DLT, will allow us to attain unprecedented backup throughput even beyond what is demonstrated in this paper. The DLT drives also provide us with the type of capacity sufficient for even the largest SQL Server databases: two DLT Tape Arrays (eight 15/30-GB DLT drives) can be combined into a single array that provides at least 120GB of storage space (no compression) on a single set of tapes, and with just one DLT Tape Array II (four 35/70-GB DLT drives) at least 140GB of space (no compression) is available. With the DLT 15-Cartridge Library storage capacity is extended to at least 225GB (these numbers are increased if compression is enabled). The DLT hardware and media are rated for superior reliability, even under continuous operation. It is important to consider however, that as multiple tape drives are combined into a stripe set the Mean Time Between Failure (MTBF) of the tape array as a whole is compounded due to dependancy upon a greater number of devices (e.g: an array of 4 tape drives is 4 times more likely to fail than a single tape drive). Thus, fault tolerance for the tape drives or tape sets may become a consideration. Software fault tolerance is not however, available with SQL Server 6.x striped dumps. The Data Warehousing or IT department seeking a fault tolerant backup solution should certainly look at the ARCserve options, which are available directly from Compaq. Using disk drives for local dump operations instead of tape drives may offer certain benefits in performance that can be useful for those who have a limited time interval in which to complete the backup of a large database (although one can match the performance of disk array dumps with a sufficient number of striped 35/70 DLT drives). However, dumps to disks are generally not practical in and of themselves, due to capacity limitations and because disks do not offer readily removable media that can be inexpensively archived. Rather, they should be used in conjunction with a tape solution, where the database can be dumped rapidly to disk and then the dump device files backed up to tape at leisure using a file-based backup utility. The advantage here is that interruptions to user transactions on the database caused by a rapid online dump would be relatively brief. And unlike a file-based backup of the database files themselves which would involve shutting down SQL Server, doing an offline backup of the dump files requires no shutdown. Such an off-line backup should not cause significant performance degradation to normal system activity, as it is not a processor-intensive operation (provided software compression is not used), and I/O interference with normal database transactions during the backup can be minimized by providing a dedicated disk volume for the dump files78. If the user does find it practical to perform dumps to disk, a fault-tolerant capable RAID controller should be a consideration, keeping in mind the performance aspects of different RAID-level arrays. Other SQL Server backup strategies involving temporary storage to disk include dumping to remote servers across the network. Multiple database servers could send their data to a single Backup server, from where the dump images could be transferred to tape (the only tape drives needed would exist on the backup server). Also, a Standby server can be set up which receives dump images (data and transaction log) to its disks from a Primary SQL Server, then immediately loads these images in order to maintain a "warm" copy of the production data in case the Primary server should fail.79 The need for 78 A dedicated volume for dump files (or even a dedicated controller) is recommended not only for performance reasons, but also for data isolation in case of hardware failure. 79 Details on implementing a "warm" standby server are discussed in the SQL Server Administrator's Companion, Appendix E.

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
Compaq 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, we have shown that we can attain backup throughput
that is limited only by the ability of the disk subsystem to deliver the data.
Of course disk subsystem
data-transfer rates will continue to increase as more advanced SCSI and Fiber Channel technologies
emerge, and these combined with high-end DLT drives such as the 35/70-GB DLT, will allow us to
attain unprecedented backup throughput even beyond what is demonstrated in this paper. The DLT
drives also provide us with the type of capacity sufficient for even the largest SQL Server databases:
two DLT Tape Arrays (eight 15/30-GB DLT drives) can be combined into a single array that provides
at least 120GB of storage space (no compression) on a single set of tapes, and with just one DLT Tape
Array II (four 35/70-GB DLT drives) at least 140GB of space (no compression) is available.
With the
DLT 15-Cartridge Library storage capacity is extended to at least 225GB (these numbers are increased
if compression is enabled).
The DLT hardware and media are rated for superior reliability, even under continuous operation.
It is
important to consider however, that as multiple tape drives are combined into a stripe set the Mean
Time Between Failure (MTBF) of the tape array as a whole is compounded due to dependancy upon a
greater number of devices (e.g: an array of 4 tape drives is 4 times more likely to fail than a single tape
drive).
Thus, fault tolerance for the tape drives or tape sets may become a consideration.
Software
fault tolerance is not however, available with SQL Server 6.x striped dumps.
The Data Warehousing or
IT department seeking a fault tolerant backup solution should certainly look at the ARCserve options,
which are available directly from Compaq.
Using disk drives for local dump operations instead of tape drives may offer certain benefits in
performance that can be useful for those who have a limited time interval in which to complete the
backup of a large database (although one can match the performance of disk array dumps with a
sufficient number of striped 35/70 DLT drives).
However, dumps to disks are generally not practical in
and of themselves, due to capacity limitations and because disks do not offer readily removable media
that can be inexpensively archived.
Rather, they should be used in conjunction with a tape solution,
where the database can be dumped rapidly to disk and then the dump device files backed up to tape at
leisure using a file-based backup utility.
The advantage here is that interruptions to user transactions
on the database caused by a rapid online dump would be relatively brief.
And unlike a file-based
backup of the database files themselves which would involve shutting down SQL Server, doing an off-
line backup of the dump files requires no shutdown.
Such an off-line backup should not cause
significant performance degradation to normal system activity, as it is not a processor-intensive
operation (provided software compression is not used), and I/O interference with normal database
transactions during the backup can be minimized by providing a dedicated disk volume for the dump
files
78
.
If the user does find it practical to perform dumps to disk, a fault-tolerant capable RAID
controller should be a consideration, keeping in mind the performance aspects of different RAID-level
arrays.
Other SQL Server backup strategies involving temporary storage to disk include dumping to remote
servers across the network.
Multiple database servers could send their data to a single Backup server,
from where the dump images could be transferred to tape (the only tape drives needed would exist on
the backup server).
Also, a Standby server can be set up which receives dump images (data and
transaction log) to its disks from a Primary SQL Server, then immediately loads these images in order
to maintain a “warm” copy of the production data in case the Primary server should fail.
79
The need for
78
A dedicated volume for dump files (or even a dedicated controller) is recommended not only for performance
reasons, but also for data isolation in case of hardware failure.
79
Details on implementing a “warm” standby server are discussed in the
SQL Server Administrator’s
Companion
, Appendix E.