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

Compaq Backup and Recovery for Microsoft SQL Server 6.x, sp_con

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

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 12 Local backup consists of archiving your data to a storage device physically attached to the database server. This device can include a tape drive, a hard disk drive or a rewritable optical disk drive. You can also use a separate disk drive for temporary storage, and move the image to a tape at some later time. Remote backup consists of archiving your data to a storage device located on another server. This device can consist of a hard disk drive or a rewritable optical disk drive. SQL Server does not support direct dumps to a remote tape, even though you can dump your data to a remote server's shared disk drive and then move it to a tape local to that server. The remote server can then act as a database backup server, to which multiple database servers dump their data. With a local backup implementation, each database server requires its own backup storage. This storage is typically a tape drive, even though it may also be a separate disk volume9. There is no additional network overhead generated, but users of this server will experience an increased response time due to the additional workload induced by the backup activity. With a remote backup implementation, the cost of the backup server hardware and software can be shared among many database (and other types of) servers. You may, in some cases, even utilize an existing file server as your database backup server. Additional overhead generated by increased network activity can be alleviated by scheduling backups for after-hours, or by dedicating a separate, high-speed network link between the database server and the backup server. A SQL Server logical dump device can point to either a tape drive (e.g.: \\.\Tape0) or to a file on a disk partition (e.g.: D:\Dump1.DAT). Backups done directly to a tape device are a convenient and relatively inexpensive way of backing up your database. The capacity and the speed are dependent upon the tape hardware. The tape device must be physically attached to the database server to perform SQL Server dumps to tape, unless special database backup agents are used10. File-based tape backup utilities support archiving of remote data, but the backup software must be running on the same machine as the tape drive. Dumps done directly to a disk device, local or remote, are typically faster but more costly as compared to tape backups. You can consider dumping directly to a disk device and then moving the image onto a tape. Frequency of your database and transaction log dumps, scheduling of these dumps and the retention period all depend entirely on your environment. Factors such as an acceptable amount of work that could be lost, if any, acceptable down-time due to a recovery from a failure, the volume of update transactions, etc., will all influence the backup frequency, scheduling, and data retention. SQL Server offers a "media retention" option, configurable through sp_configure, which allows you to prevent overwriting an existing dump too soon. Automatic scheduling is available via the SQL Server Executive service or third party software. Refer to the System Administrator's Guide, documents available from Microsoft, or your third party software documentation for more detailed information. 9 Using the same physical disk volume as your database or transaction log volume is not recommended for two reasons: 1) if your dump destination volume is the same as your database volume, and you lose a disk on this volume, you have no way of recovering your data, and 2) isolating I/O generated by the dump activity from the transaction log and database I/O activity minimizes performance impact. 10 Database Backup Agents are available from Compaq or Cheyenne for use with Cheyenne ARCserve, and are covered later in this paper.

  • 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
12
Local backup consists of archiving your data to a storage device physically attached to the database
server.
This device can include a tape drive, a hard disk drive or a rewritable optical disk drive.
You
can also use a separate disk drive for temporary storage, and move the image to a tape at some later
time.
Remote backup consists of archiving your data to a storage device located on another server.
This
device can consist of a hard disk drive or a rewritable optical disk drive.
SQL Server does not support
direct dumps to a remote tape, even though you can dump your data to a remote server’s shared disk
drive and then move it to a tape local to that server.
The remote server can then act as a database
backup server, to which multiple database servers dump their data.
With a local backup implementation, each database server requires its own backup storage.
This
storage is typically a tape drive, even though it may also be a separate disk volume
9
.
There is no
additional network overhead generated, but users of this server will experience an increased response
time due to the additional workload induced by the backup activity.
With a remote backup implementation, the cost of the backup server hardware and software can be
shared among many database (and other types of) servers.
You may, in some cases, even utilize an
existing file server as your database backup server.
Additional overhead generated by increased
network activity can be alleviated by scheduling backups for after-hours, or by dedicating a separate,
high-speed network link between the database server and the backup server.
A SQL Server logical dump device can point to either a tape drive (e.g.:
\\.\Tape0
) or to a file on a disk
partition (e.g.:
D:\Dump1.DAT
).
Backups done directly to a tape device are a convenient and relatively inexpensive way of backing up
your database.
The capacity and the speed are dependent upon the tape hardware.
The tape device
must be physically attached to the database server to perform SQL Server dumps to tape, unless special
database backup agents are used
10
.
File-based tape backup utilities support archiving of remote data,
but the backup software must be running on the same machine as the tape drive.
Dumps done directly to a disk device, local or remote, are typically faster but more costly as compared
to tape backups.
You can consider dumping directly to a disk device and then moving the image onto a
tape.
Frequency of your database and transaction log dumps, scheduling of these dumps and the retention
period all depend entirely on your environment.
Factors such as an acceptable amount of work that
could be lost, if any, acceptable down-time due to a recovery from a failure, the volume of update
transactions, etc., will all influence the backup frequency, scheduling, and data retention.
SQL Server
offers a “media retention” option, configurable through
sp_configure
, which allows you to prevent
overwriting an existing dump too soon.
Automatic scheduling is available via the SQL Server
Executive service or third party software.
Refer to the System Administrator’s Guide, documents
available from Microsoft, or your third party software documentation for more detailed information.
9
Using the same physical disk volume as your database or transaction log volume is not recommended for two
reasons: 1) if your dump destination volume is the same as your database volume, and you lose a disk on this
volume, you have no way of recovering your data, and 2) isolating I/O generated by the dump activity from the
transaction log and database I/O activity minimizes performance impact.
10
Database Backup Agents are available from Compaq or Cheyenne for use with Cheyenne ARCserve, and are
covered later in this paper.