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

SQL Server Backup with Database Activity, recovery interval

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

Compaq Backup and Recovery for Microsoft SQL Server 6.x Figure 3 - SQL Server Backup with Database Activity Page 62 Note that if a checkpoint command is issued during the database dump, it may not complete until near the end of the dump when most of the dirty pages that need to be written out have been backed up. Furthermore, write requests from the checkpoint process may delay user transaction writes thereby further affecting transaction performance. Automatic checkpointing can be disabled for the duration of the dump by setting the recovery interval parameter to a very high value, then resetting it immediately after the dump. In order to measure performance of SQL Server dumps under user activity, we engineered our environment in such a way, that we were able to control the level of load at the server via the introduction of a "think time" or a slight delay for our artificial users between submitting transactions. We started by measuring maximum throughput in transactions per second, or 100% load at the server, by allowing users to submit transactions as fast as they could, with no induced delays. Next, we introduced delays to achieve 25%, 33% and 75% of the maximum transactions per second throughput rate (1/4th, 1/3rd, and 3/4th load at the server). Finally, we measured throughput (both user transactions per second throughput and GB/hour backup throughput) during a database dump to two Compaq 15/30GB DLT drives, with the SQL Server native striped dump as well as the ARCserve assisted dump. During each of the phases we also noted changes in the transaction response times as would be experienced by the end-user terminals. We defined two scenarios: 1) dump of an active database, and 2) dump of an inactive database. An active database would be one under a variable user activity, yielding a load at the server (we chose to test the 1/3rd load with this scenario). An inactive database would be one with no user activity, but another database in the system would be active at the same time, again yielding some load at the server (we chose to test the 1/4th and 3/4th loads with this scenario). Note that both of these situations are considered to be "online" backups, since in neither case is the database being backed up made

  • 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
62
Figure 3 - SQL Server Backup with Database Activity
Note that if a checkpoint command is issued during the database dump, it may not complete until near
the end of the dump when most of the dirty pages that need to be written out have been backed up.
Furthermore, write requests from the checkpoint process may delay user transaction writes thereby
further affecting transaction performance.
Automatic checkpointing can be disabled for the duration of
the dump by setting the
recovery interval
parameter to a very high value, then resetting it immediately
after the dump.
In order to measure performance of SQL Server dumps under user activity, we engineered our
environment in such a way, that we were able to control the level of load at the server via the
introduction of a “think time” or a slight delay for our artificial users between submitting transactions.
We started by measuring maximum throughput in transactions per second, or 100% load at the server,
by allowing users to submit transactions as fast as they could, with no induced delays.
Next, we
introduced delays to achieve 25%, 33% and 75% of the maximum transactions per second throughput
rate (1/4
th
, 1/3
rd
, and 3/4
th
load at the server).
Finally, we measured throughput (both user transactions
per second throughput and GB/hour backup throughput) during a database dump to two Compaq 15/30-
GB DLT drives, with the SQL Server native striped dump as well as the ARCserve assisted dump.
During each of the phases we also noted changes in the transaction response times as would be
experienced by the end-user terminals.
We defined two scenarios: 1) dump of an active database, and 2) dump of an inactive database.
An
active database would be one under a variable user activity, yielding a load at the server (we chose to
test the 1/3
rd
load with this scenario).
An inactive database would be one with no user activity, but
another database in the system would be active at the same time, again yielding some load at the server
(we chose to test the 1/4
th
and 3/4
th
loads with this scenario).
Note that both of these situations are
considered to be “online” backups, since in neither case is the database being backed up made