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

Striping Devices, Increased Concurrency, I/O Requests, Parameters, Append Dumps to Disk Files,

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 22 highlights

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 22 This section will discuss the features, functional characteristics, and other considerations involved when using the native data "dump" facility provided with SQL Server. The database backup functionality built-into SQL Server 6.x is quite versatile. We will see that backup jobs can be run in a variety of ways, and that data can be archived to different types of storage. The performance benefits and drawbacks of each of these backup options can then be analyzed in subsequent sections. Microsoft has made significant enhancements to the backup functionality in SQL Server versions 6.0 & 6.5, which can yield better performance and ease of use than previous (4.2x) versions: Striping Devices - SQL Server 6.x now allows databases to be dumped to and loaded from multiple tape and/or disk devices, said to form a 'stripe set'. Unlike dumps or loads with single devices, striping is a multithreaded process and can significantly increase the throughput of the operation. Up to 32 backup devices can be used in a stripe set Increased Concurrency - Previous versions of SQL Server were not designed for more than 3 databases to be dumped simultaneously. With SQL Server 6.x up to 32 different databases can be dumped at the same time, provided that the corresponding number of backup devices are available. I/O Requests - Much of the performance limitations of backup and restore operations in the previous version of SQL Server were due to the sizes of the various I/O requests being done to and from both disk and tape. With SQL Server 6.x, all disk I/O (on the volume housing the database) during dump / load operations is now performed with requests for multiple data pages (up to 16KB per request), instead of just a single page (2KB size) at a time. The size of the data blocks sent to various dump devices has also been enhanced. When dumping to a disk device, large writes of 64KB size are performed, and when loading from a disk device 64KB reads are done. When dumping to or loading from a DLT tape device, 64KB data transfers take place. These larger I/O sizes allow SQL Server 6.x to more efficiently drive powerful hardware storage technologies such as disk arrays, Wide SCSI-2 bus, and Digital Linear tape. Parameters - SQL Server 4.2x used hard-coded values to determine the number of buffers and threads allocated when dumping to multiple devices, such as for parallel dump operations. Now, SQL Server 6.x has two new configurations parameters, backup_buffer_size and backup_threads, used to control the size of the buffers and the number of threads used when doing parallel dumps or dumping to striped disk devices. Append Dumps to Disk Files - SQL Server has always provided the functionality to append new dump images to the end of existing ones on tape devices. With SQL 6.x, this functionality has been extended so that dumps can be appended to disk devices as well. Furthermore, the size of the file on disk will now reflect the size of the actual data image(s), as a dump file containing existing dumps is first truncated if the existing dumps are being overwritten, then extended as subsequent dumps are appended. Single Table Operations - With SQL Server 6.x, a single table within a database can be dumped to or loaded from a dump file. However, any table loaded in such a manner will reflect its state at the time the dump was made, and so may not be logically consistent with the current state of the other objects in the database (referential integrity is not guaranteed for single table loads). Single table operations are intended primarily as a disaster recovery mechanism (e.g: recovering a table that exhibits data corruption), and will not be covered further in this paper. Dump to Named Pipe - SQL Server 6.x now allows a named pipe to be specified as the dump device. 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.

  • 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
22
This section will discuss the features, functional characteristics, and other considerations involved
when using the native data “dump” facility provided with SQL Server.
The database backup
functionality built-into SQL Server 6.x is quite versatile.
We will see that backup jobs can be run in a
variety of ways, and that data can be archived to different types of storage.
The performance benefits
and drawbacks of each of these backup options can then be analyzed in subsequent sections.
Microsoft has made significant enhancements to the backup functionality in SQL Server versions 6.0 &
6.5, which can yield better performance and ease of use than previous (4.2x) versions:
Striping Devices -
SQL Server 6.x now allows databases to be dumped to and loaded from
multiple tape and/or disk devices, said to form a ‘stripe set’.
Unlike dumps or loads with single
devices, striping is a multithreaded process and can significantly increase the throughput of the
operation.
Up to 32 backup devices can be used in a stripe set
Increased Concurrency -
Previous versions of SQL Server were not designed for more than 3
databases to be dumped simultaneously.
With SQL Server 6.x up to 32 different databases can be
dumped at the same time, provided that the corresponding number of backup devices are available.
I/O Requests -
Much of the performance limitations of backup and restore operations in the
previous version of SQL Server were due to the sizes of the various I/O requests being done to and
from both disk and tape.
With SQL Server 6.x, all disk I/O (on the volume housing the database)
during dump / load operations is now performed with requests for multiple data pages (up to 16KB
per request), instead of just a single page (2KB size) at a time.
The size of the data blocks sent to
various dump devices has also been enhanced.
When dumping to a disk device, large writes of
64KB size are performed, and when loading from a disk device 64KB reads are done.
When
dumping to or loading from a DLT tape device, 64KB data transfers take place.
These larger I/O
sizes allow SQL Server 6.x to more efficiently drive powerful hardware storage technologies such
as disk arrays, Wide SCSI-2 bus, and Digital Linear tape.
Parameters -
SQL Server 4.2x used hard-coded values to determine the number of buffers and
threads allocated when dumping to multiple devices, such as for parallel dump operations.
Now,
SQL Server 6.x has two new configurations parameters,
backup_buffer_size
and
backup_threads
,
used to control the size of the buffers and the number of threads used when doing parallel dumps
or dumping to striped disk devices.
Append Dumps to Disk Files -
SQL Server has always provided the functionality to append new
dump images to the end of existing ones on tape devices.
With SQL 6.x, this functionality has
been extended so that dumps can be appended to disk devices as well.
Furthermore, the size of the
file on disk will now reflect the size of the actual data image(s), as a dump file containing existing
dumps is first truncated if the existing dumps are being overwritten, then extended as subsequent
dumps are appended.
Single Table Operations -
With SQL Server 6.x, a single table within a database can be dumped
to or loaded from a dump file.
However, any table loaded in such a manner will reflect its state at
the time the dump was made, and so may not be logically consistent with the current state of the
other objects in the database (referential integrity is not guaranteed for single table loads).
Single
table operations are intended primarily as a disaster recovery mechanism (e.g: recovering a table
that exhibits data corruption), and will not be covered further in this paper.
Dump to Named Pipe
- SQL Server 6.x now allows a named pipe to be specified as the dump
device.
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.