Replicated Database Not In Sync - How to ensure whole database is in sync

Replicated Database Not In Sync - How to ensure whole database is in sync

Post by paul » Wed, 04 Aug 2004 23:38:04


I have a database that is merge replicated across 5 sites
Some how the database isnt in sync, although testing the syncs works
fine

I assume when I modify a record in DB A that it issues an insert or
update when the merge rep runs depednant on whether the records with
the same GUID exists

Therefore considering I have records in DBA that dont exist on DB B
and vice versa is there a trick to modify every record so that a
massive merge takes place (Or a better way I am not aware of)

I cannot sync, and resnapshot and push out as data would be lost

Please advise

Regards Paul
 
 
 

Replicated Database Not In Sync - How to ensure whole database is in sync

Post by Paul Ibiso » Thu, 05 Aug 2004 00:15:12

Paul,

there are a few reasons I have come across for non-convergence:
(1) Firstly when the filter was set to 1=2 and inserts were made while the
merge agent was running

(2) If you bulk insert the rows and choose the defaults, then FIRE_TRIGGERS
is false and consequently the rows are not added to MSmerge_contents.

(3) compensate for errors problem (see
http://www.yqcomputer.com/ ;en-us;828637&Product=sql2k).
If a change from publisher fails to get applied at the subscriber (for some
reason, PK,FK,CHECK,etc constraints) it undoes the change at the publisher.
So a insert from publisher when fails at the subscriber gets deleted at the
publisher too. Similary a delete from publisher which fails at the
subscriber, it gets re-inserted at the publisher.

In any of these cases, you need to run sp_addtabletocontents to include the
rows then resynchronise. Alternatively you can use sp_mergedummyupdate for a
single row.

HTH,

Paul Ibison

 
 
 

Replicated Database Not In Sync - How to ensure whole database is in sync

Post by paul goldn » Thu, 05 Aug 2004 00:42:40

Paul

Thanks for the advice, I have been reading up on sp_addtabletocontents

It says this must be run at the Publisher

I have data on both sides not sent ?

Also I have over 100+ tables, many of which are affected

I am no SQL GURU - Any chance of a piece of code to extrapolate the
table names so I can then write a mass of sp_addtabletocontents ?

Any advice would be appreciated

Regards Paul


*** Sent via Developersdex http://www.yqcomputer.com/ ***
Don't just participate in USENET...get rewarded for it!
 
 
 

Replicated Database Not In Sync - How to ensure whole database is in sync

Post by Paul Ibiso » Thu, 05 Aug 2004 01:25:29

Paul,

something like this should do it for you:

SELECT 'exec sp_addtabletocontents ' + sysmergearticles.name
FROM sysmergearticles INNER JOIN
sysobjects ON sysmergearticles.objid = sysobjects.id
WHERE (sysobjects.xtype = 'U')

Run this on the publisher and the resulting output should be run on the
publisher and subscribers.

HTH,

Paul Ibison