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

Microsoft SQL Server

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

Page 26 highlights

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 (or Windows NT Application event log) as well. For dumps to tape, SQL Server will report the time that the tape is actually mounted for the dump operation in hh:mm:ss format. The tape mount time is logged after any initial period that may be required to rewind or search the tape. The time that the dump operation completes will also be reported in hh:mm:ss. The dump completion time is logged before any final period that may be needed to unload the tape. The tape mount time is not logged for disk devices however, but since dumps to disk are almost instantaneous the starting time can be gotten by running the getdate function in batch preceding the dump statement. Dividing the dump image size by the time required for the dump operation will yield the throughput in MB/sec. This number can be multiplied by 3.6 to see the result in GB/hr. Remember that a full database dump includes a dump of the transaction log as well. For the database dump performance testing done in the following sections of this paper, we first completely truncated the transaction log so that only actual data throughput would be reported. The throughput for the transaction log part of the dump may differ from the data part depending on where the log resides and how compressable it is. Transaction log dump performance is tested in a separate section of this paper. There is much that can be said on the subject of data layout as it pertains to database performance. Here we will consider only how the performance of a dump operation under SQL Server can be affected by the placement of your database across the disk drives. For a more comprehensive discussion of managing your database drive subsystem, please see the Microsoft SQL Server Administrator's Companion. The most important thing to remember about the dump process is that it is completely sequential in nature: the process will begin reading data pages near the start of the database and continue in order until the end. Unlike with random I/O, if different sections of the database are located on separate disk volumes, this sequential process will be reading data from only one volume at any given time; and the overall backup performance may become limited by the read rate. The only way to guarantee that the reads will be issued to multiple drives simultaneously is for the database to be placed on a single logical volume consisting of an array of drives34. The best way to implement such an array is through use of a hardware RAID controller such as the Compaq Smart-2 SCSI Array controller. For the purposes of optimizing backup throughput, when using the Smart-2 array controller it is best to create a single large array spanning the two SCSI channels, rather than to create multiple arrays and extend the database across the logical volumes on the separate arrays. A notable exception to this rule is when you have multiple large databases that you want to back up concurrently, in which case each database is best kept on its own array. The performance of data reads from various Smart-2 arrays as well as concurrent backup performance is covered in the subsequent chapter on Backup and Recovery Performance. While a single Smart-2 controller will often provide enough capacity and throughput for a single database, some large databases may need to be built across multiple controllers. In this case, a number of methods are available to the database administrator for spanning two or more controllers: The database can be created on the first controller then extended onto the second controller, by using an ALTER DATABASE statement. 33 The size (and time) of the dump upon completion may not be logged if more than 7 dump devices are used. 34 Multiple logical volumes on the same array will achieve this result also.

  • 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
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
(or Windows NT Application event log) as well.
For dumps to tape, SQL Server will report the time
that the tape is actually mounted for the dump operation in hh:mm:ss format.
The tape mount time is
logged after any initial period that may be required to rewind or search the tape.
The time that the
dump operation completes will also be reported in hh:mm:ss.
The dump completion time is logged
before any final period that may be needed to unload the tape.
The tape mount time is not logged for
disk devices however, but since dumps to disk are almost instantaneous the starting time can be gotten
by running the
getdate
function in batch preceding the
dump
statement.
Dividing the dump image size by the time required for the dump operation will yield the throughput in
MB/sec.
This number can be multiplied by 3.6 to see the result in GB/hr.
Remember that a full database dump includes a dump of the transaction log as well.
For the database
dump performance testing done in the following sections of this paper, we first completely truncated
the transaction log so that only actual data throughput would be reported.
The throughput for the
transaction log part of the dump may differ from the data part depending on where the log resides and
how compressable it is.
Transaction log dump performance is tested in a separate section of this paper.
There is much that can be said on the subject of data layout as it pertains to database performance.
Here we will consider only how the performance of a dump operation under SQL Server can be
affected by the placement of your database across the disk drives.
For a more comprehensive
discussion of managing your database drive subsystem, please see the
Microsoft SQL Server
Administrator’s Companion
.
The most important thing to remember about the dump process is that it is completely sequential in
nature: the process will begin reading data pages near the start of the database and continue in order
until the end.
Unlike with random I/O, if different sections of the database are located on separate disk
volumes, this sequential process will be reading data from only one volume at any given time; and the
overall backup performance may become limited by the read rate.
The only way to guarantee that the
reads will be issued to multiple drives simultaneously is for the database to be placed on a
single
logical volume consisting of an array of drives
34
.
The best way to implement such an array is through
use of a hardware RAID controller such as the Compaq Smart-2 SCSI Array controller.
For the purposes of optimizing backup throughput, when using the Smart-2 array controller it is best to
create a single large array spanning the two SCSI channels, rather than to create multiple arrays and
extend the database across the logical volumes on the separate arrays.
A notable exception to this rule
is when you have multiple large databases that you want to back up concurrently, in which case each
database is best kept on its own array.
The performance of data reads from various Smart-2 arrays as
well as concurrent backup performance is covered in the subsequent chapter on
Backup and Recovery
Performance
.
While a single Smart-2 controller will often provide enough capacity and throughput for a single
database, some large databases may need to be built across multiple controllers.
In this case, a number
of methods are available to the database administrator for spanning two or more controllers:
The database can be created on the first controller then extended onto the second controller, by
using an ALTER DATABASE statement.
33
The size (and time) of the dump upon completion may not be logged if more than 7 dump devices are used.
34
Multiple logical volumes on the same array will achieve this result also.