Manual Synchronize and Maintain Consistency.
ch...@gratisinternet.com
What is the best way to handle the snapshot process of transactional
replication under the following conditions?
1) Manual synchronization (semi-large database - 200GB)
2) Without locking publisher tables during snapshot (semi-active
database - 500 transactions per second)
3) While maintaining data consistency, so that when the distribution
agent is run;
a) All transactions are sent in proper order to subscriber
b) Only transactions that exist in publisher are applied to subscriber
4) Able to manually synchronize the subscriber with necessary
transactions if need be
I have read over the following articles / posts several times:
http://www.sswug.org/archives/read.asp?mid=42237
http://support.microsoft.com/default.aspx?scid=kb;en-us;320499
http://www.sql-server-performance.com/snapshot_replication_tuning.asp
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part8/c2961.mspx?mfr=true
Is there a better way / recommended method that I'm missing here?
>From these posts, I'm gathering the only real way to manually
synchronize a publisher and subscriber with transactional replication
is to:
1) Setup the publication with "distributor has schema" option -
disable all replication agents. At this point, transactions are stored
in the publishers transaction log until they are have been marked as
replicated.
2) Full backup the publisher database.
3) Restore it to the subscriber. During the restore, the publisher is
actively performing transactions, which again, are stored in the
transaction log until marked as replicated.
4) Manually synchronize the subscriber with the publisher data by using
a data comparison tool by PK values (by whatever means, be it a tool or
log backup / restore).
5) Mark all publisher transactions as replicated manually.
6) Kick off the snapshot and log reader agents, then distribution agent
to start "synchronizing", and hope that the transactions sent are
modifying data that have already been sent.
My main concern here is the time it takes to perform actions between #4
and #5, as transactions will most definitely have occurred on the
publisher in the time it takes to sync the data.
Before I start getting into testing this method and trying to plan this
out to work in an active environment, I thought I would check to see if
I'm on the right track here. There must be a better way?
Hilary Cotter
large snapshots.
With luck what you can do is, kick everyone off the publisher, back it up,
restore it on the subscriber, convert all constraints, triggers, and
identity columns to not for replication.
If you can't do this, what you do is do a backup, the moment it is complete,
do a no-sync subscription, but don't run the distribution agent. Copy the
backup to the subscriber, and make the changes above.
Then start up the distribution agent, and use the continue on data
consistency errors profile. After some time transactions and commands will
not longer be skipped. Now start to run validations to determine how out of
sync you are, and which tables are problematic. Then manually run data
compares to ensure they are consistent.
You can also do a no sync to a dummy db, the instant you start the backup,
and then stop the distribution database. You will find the commands pooling
in the distribution database and replay them on the real subscriber after
you have applied the backup.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<ch...@gratisinternet.com> wrote in message
news:1148417212.3...@i39g2000cwa.googlegroups.com...
ch...@gratisinternet.com
I think that I've got everything going now after using the profile to
skip consistency errors for the distribution agent you mentioned above.
I assume that in skipping the consistency errors, eventually the data
will become consistent since the full backup of the publisher was taken
after the subscription was created.
Transactions began to queue up in the publisher log as being marked for
replication when the full backup was taken. By the time the full backup
was recovered to the subscriber, it was about 10 hours behind the
publisher. However all 10 hours of transactions would still be "queued"
on the publisher until the log & distribution agent sends them.
If it took 2 hours to backup the publisher database, then we only have
to worry about those 2 hours worth of transactions being duplicated on
the subscriber when the replication agents finally run.
Thus in taking the full backup after the publication / subscription is
created, and enabling the distributor profile to skip consistency
errors, no "manual" sync should be required.
Am I thinking right here?