standby server

Strategies for Backing Up and Restoring Transactional Replication
Microsoft® SQL ServerT 2000 allows you to restore transactional replication
databases without reinitializing subscriptions or disabling and
reconfiguring publishing and distribution. You can set up replication to
work with log shipping, enabling you to use a warm standby server without
reconfiguring replication.

Recovering transactional replication from a loss of the publication database
or distribution database, without having to reinitialize subscriptions or
reconfigure replication, requires the publication database and the
distribution database be restored to a consistent point in time. In SQL
Server version 7.0 and earlier, this had to be ensured manually by backing
up the publication database and distribution database simultaneously, and at
the same time ensuring no changes were being made to the databases while the
backup was in progress. SQL Server 2000 automatically handles the
coordination of the backups of the two databases.

To ensure that you can restore the Publisher or Distributor at any time, SQL
Server 2000 requires the replication database option sync with backup be set
to true on the publication database and on the distribution database. If you
use this option, you will need to back up the publication database and
distribution databases (usually you would back up the transaction log or
make differential backups) frequently because the frequency of backups
determines the latency with which replication delivers changes to
Subscribers.



Important Only SQL Server 2000 Publishers support this option. If the
distribution database is set to sync with backup, Publishers running SQL
Server 7.0 and earlier and using that distribution database will be treated
as if the option is not set.


Backing Up and Restoring the Publication Database
Usually the Log Reader Agent runs in continuous mode, monitoring the log for
data changes, which it immediately propagates to the distribution database
(typically within a few seconds). In addition, because backups of the
publication database usually occur on a scheduled basis, the Log Reader
Agent may be transferring transactions faster than they are being backed up.
If the Publisher fails and is restored, the distribution database may
already have transactions that will not exist in the restored publication
database because those transactions were not backed up.

Setting the sync with backup option on the publication database ensures that
the Log Reader Agent will not propagate any transactions to the distribution
database that have not been backed up at the Publisher. This ensures that
the last backup can be restored without any possibility of the distribution
database having transactions that the restored publication database does not
have.

Synchronizing the Log Reader Agent with backing up the publication database
means that replication latency (the time it takes for changes at the
Publisher to be delivered to the Subscriber), which can often be as low as a
few seconds, is now constrained to be equal to the frequency of backups at
the Publisher. For example, if you are backing up the transaction log of the
publication database every five minutes, replication latency could be as
much as five minutes plus the time it takes to complete the backup. On the
average, it will be less than five minutes, but more than typical
transactional replication latency, which can be tens of seconds. If you
synchronize the Log Reader Agent with the backup, it is recommended that you
back up the publication database (database backup followed by log and/or
differential database backups) as frequently as possible to reduce the time
it takes for changes to appear at Subscribers.

To synchronize the publication database to a backup

a.. Execute sp_replicationdboption '<publicationdatabasename>', 'sync with
backup', 'true'.


Note If you change the sync with backup option to false, the truncation
point of the publication database will be updated after the Log Reader Agent
runs, or after an interval if the Log Reader Agent is running continuously.
The maximum interval is controlled by the -MessageInterval agent parameter
with a default of 30 seconds.


To determine if the sync with backup option has been set on a publication
database, use the IsSyncWithBackup property of the DatabasePropertyex()
intrinsic function. You can also run the system stored procedure sp_helpdb
to check if this option has been set.

If the increase in replication latency is not acceptable, do not to set the
sync with backup option on the publication database. If the publication
database fails, it will be possible for the distribution database to have
transactions that the restored publication database does not have, and it is
not guaranteed that the Subscriber will be in synchronization with the
Publisher.

Restoring the Publication Database When the sync with backup Option is False
If you do not set the sync with backup option and allow the distribution
database to have transactions that the restored publication database does
not have, it is possible to restore a publication database from backup and
for replication to continue, but the Subscriber and Publisher may no longer
be in synchronization. To accomplish this:

1.. Restore the publication database. At this point, you will get an error
from the Log Reader Agent because it will detect that the Distributor is
ahead of the Publisher.


2.. Run sp_replrestart in the publication database with no parameters.
This forces replication to continue even if the Distributor and some
Subscribers may now have data that the Publisher no longer has.


3.. Ensure that that the Distribution Agents, which could now deliver
duplicate rows to Subscribers, can continue despite these failures. Choose
the -SkipError Distribution Agent profile, or you can manually add
the -SkipError parameter to the runtime parameters of the Distribution
Agents and supply the errors you want the Distribution Agents to ignore. For
more information, see Distribution Agent Profile.


Caution This method can lead to inconsistencies between data at the
Publisher and data at the Subscribers.


Backing Up and Restoring the Distribution Database
The distribution database can be restored to the last backup without
reconfiguring replication or reinitializing subscriptions. Usually, the Log
Reader Agent connects to the publication database, scans the log, retrieves
the next set of N transactions that need to be replicated, propagates them
to the distribution database, and then indicates to the publication database
that the transactions have been successfully committed at the distribution
database.

At this point, the publication database can truncate the part of the log
that contains these transactions (provided they have been backed up). If the
distribution database fails at this point and is restored to a previous
backup, it will not be possible for the Log Reader Agent to deliver the
missing transactions because the part of the log containing them may have
been truncated.

Setting the sync with backup option on the distribution database ensures
that the log of the publication database will not be truncated beyond the
point up to which all transactions have been propagated to the distribution
database. It also ensures that the distribution database with the new
transactions has been backed up. The distribution database can be restored
to the last backup and the Log Reader Agent will be able to deliver
transactions that the restored distribution database is now missing.
Replication will continue unaffected.



Important To backup the distribution database more frequently by backing up
the transaction logs and setting the sync with backup option, you must set
the trunc. log on chkpt. option of sp_dboption to false on the distribution
database.


Unlike the publication database, setting the sync with backup option on the
distribution database has no effect on replication latency, but it will
delay the truncation of the log on the publication database until the
corresponding transactions in the distribution database have been backed
up.The sync with backup option is available only if the Publisher and
Distributor are running SQL Server 2000.

To synchronize the distribution database to a backup

a.. Execute sp_replicationdboption '<distributiondatabasename>', 'sync
with backup', 'true'
To determine if the sync with backup option has been set on a distribution
database, use the IsSyncWithBackup property of the databaseproperty()
intrinsic function. You can also run the system stored procedure sp_helpdb
to check if this option has been set.

Backing Up and Restoring a Subscription Database
To restore the Subscriber to the last backup without any need to
reinitialize the subscriptions, ensure that the minimum transaction
retention period at the Distributor is greater than the frequency of the
backup interval at the Subscriber. This guarantees that when you restore a
Subscriber, all the transactions necessary for the Subscriber to catch up
will still be available in the distribution database. When you restore a
Subscriber, the Distribution Agent delivers any transactions the Subscriber
is missing. By default, the minimum transaction retention period is set to
0, and under most circumstances a transaction that has been delivered to all
Subscribers will be deleted.

To set the minimum transaction retention period of the Distributor

1.. In SQL Server Enterprise Manager, expand a server group, expand the
Distributor, right-click the Replication folder, and then click Configure
Publishing, Subscribers, and Distribution.


2.. To modify the distribution database, click the Properties button for
the distribution database to change the transaction retention period.


Note It is not necessary to back up the Subscribers to restore
transactional replication; however, it is essential that you back up the
Publisher and Distributor. If the cost of generating a snapshot and
reinitializing the subscription is less than the time it would take to
restore the subscription database from a backup, there is no need to back up
the subscription database.


Backing Up and Restoring the msdb Database
The msdb database contains the job definitions for replication agents that
are run under the control of SQL Server Agent. To provide additional
security against a system failure, the msdb database on the Distributor and
Subscribers that use pull subscriptions must be backed up periodically,
whenever a subscription is dropped or a new one added, or whenever a change
is made to a replication agent.

The msdb database may also contain Data Transformation Services (DTS)
package definitions used in replication if transformable subscriptions are
used with any transactional or snapshot publications. To provide assurance
against system failure, the msdb database on the Distributor and on the
Subscribers that use transformable subscriptions must be backed up
periodically as well. These operations should be performed any time the DTS
package associated with a publication is modified or each time a
subscription is dropped or a new one is added. This ensures that the most
up-to-date definitions can be recovered.Backing up and restoring the msdb
database allows you to restore replication after a complete loss of the
Distributor or Subscriber.

Backing Up and Restoring the master Database
When a new Subscriber is added, an entry for the Subscriber is added to the
sysservers table in the master database on the Publisher. Back up the master
database at the Publisher and after a Subscriber is added or after an entry
for the Publisher is added to the sysservers table in the master database on
the Distributor.

To restore replication after loss of the Publisher or Distributor, back up
the master database on the Publisher and Distributor each time a new
Subscriber or Publisher is added (respectively).


begin 666 important.gif
M1TE&.#EA# `+`(#_`,P``,# P"'Y! $```$`+ `````,``L`0 (7C(\9`&H,
3X8F43>E>2]CRZFF1$V9E4 ``.P``
`
end

begin 666 note.gif
M1TE&.#EA# `+`(#_`(2&`,# P"'Y! $```$`+ `````,``L`0 (:C(\(H'S[
68%R0&ED;M7,'[%S2YW#1)VJ;4P``.P``
`
end

begin 666 caution.gif
M1TE&.#EA# `+`/<``````#,``&8``)D``,P``/\````S`#,S`&8S`)DS`,PS
M`/\S``!F`#-F`&9F`)EF`,QF`/]F``"9`#.9`&:9`)F9`,R9`/^9``#,`#/,
M`&;,`)G,`,S,`/_,``#_`#/_`&;_`)G_`,S_`/__````,S,`,V8`,YD`,\P`
M,_\`,P`S,S,S,V8S,YDS,\PS,_\S,P!F,S-F,V9F,YEF,\QF,_]F,P"9,S.9
M,V:9,YF9,\R9,_^9,P#,,S/,,V;,,YG,,\S,,__,,P#_,S/_,V;_,YG_,\S_
M,___,P``9C,`9F8`9ID`9LP`9O\`9@`S9C,S9F8S9IDS9LPS9O\S9@!F9C-F
M9F9F9IEF9LQF9O]F9@"99C.99F:99IF99LR99O^99@#,9C/,9F;,9IG,9LS,
M9O_,9@#_9C/_9F;_9IG_9LS_9O__9@``F3,`F68`F9D`F<P`F?\`F0`SF3,S
MF68SF9DSF<PSF?\SF0!FF3-FF69FF9EFF<QFF?]FF0"9F3.9F6:9F9F9F<R9
MF?^9F0#,F3/,F6;,F9G,F<S,F?_,F0#_F3/_F6;_F9G_F<S_F?__F0``S#,`
MS&8`S)D`S,P`S/\`S `SS#,SS&8SS)DSS,PSS/\SS !FS#-FS&9FS)EFS,QF
MS/]FS "9S#.9S&:9S)F9S,R9S/^9S #,S#/,S&;,S)G,S,S,S/_,S #_S#/_
MS&;_S)G_S,S_S/__S ``_S,`_V8`_YD`_\P`__\`_P`S_S,S_V8S_YDS_\PS
M__\S_P!F_S-F_V9F_YEF_\QF__]F_P"9_S.9_V:9_YF9_\R9__^9_P#,_S/,
M_V;,_YG,_\S,___,_P#__S/__V;__YG__\S______P``````````````````
M````````````````````````````````````````````````````````````
M````````````````````````````````````````````````````````````
M`````````````````````"'Y! $``*P`+ `````,``L`0 @B`%D)'$BPH,&#
;K HH1)AP(4*%!1A"9$A1X,2'#BMBC#@P( `[
`
end