May 23, 2006 at 10:46 am
Manual Snapshot - Transactional Replication - SQL Server 2000 Sp3a
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?
May 24, 2006 at 10:26 am
May 25, 2006 at 8:03 am
FYI - response to this post on google groups from MVP Hilary Cotter
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply