sql server replication: I have set up Mege replication where a publishing subscriber
subscribes to a central publisher. The publishing subscriber publishes
to other regional subscribers. All Subscriptions are set up as pull
subscriptions using dynamic snapshots. The problem however is that
when the associated application is used and merge replication running
across all machines at 1 minute intervals occurrs randomly some rows
within some of the articles are not recieved at the leaf node
subscribers (No errors are received or conflicts generated) . The
changes always make it out to the publishing subscriber. This
inconsistant state can be corrected by inititaing a reinitialisation of
the subscription for all subscrptions relating to the publishing
subscriber. When this occurrs the original shapshot is reapplied and
all the changes since the last snap shot are applied. The result is
all the records are now transferred. In addition if i use associated
web application to create records within the database and then manually
run each merge agent the data is successfully transferred.

I have tried a lot of differnet things to correct this problem.
(Monitored locks / deadlocks etc). Has anyone else experienced this
sort of behavior?

Look to see if these missing rows show up in the conflict viewer. If so you
may be able to roll these deletes back.

Are you using join filters? This could be a factor. If a parent row falls
out of the filter on the subscriber, it will take all rows in the child
tables out of the subscriber if keep_partition_changes is set to false.

--
Hilary Cotter
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

[quoted text, click to view]

Thanks for the reply Hilary I really appreciate your help,

I am using Join filters with @keep_partition_changes = N'true'.
There are no conflict tables

The problem seems to be that when synchronization bewteen my lowest
level leafnode subsciber and regional publisher occurr
the merge agent when enumerating pending changes does not pick up the
full set of data that should be delivered to the subscriber based on
the join filter.



I have managed to narrow down the sequence of events that cause the
problem i am experiencing

This is the Replcation topology i have used

CentralPublication ->Regional Publisher 1 (Global Subscription) ->
Subscriber 1 (Local Subscription)


In Step 1 My web application runs against the Central Publisher
database effectively creating rows of data in a number of tables (all
articles of the publication)
Step 2 - Replication takes place between central publisher and the
regional publisher via a global pull subscription. Rows are filtered
via Join filter to the regional Publisher. This seems to be operating
as i would expect
Step 3 - Replication then takes place between the regional Publisher
and the Regional Subscriber. (The regional Publisher republishes a
filtered set to the regional subscriber) Investigation of the
replicated data at the Central publisher is also correct at this stage
Step 4 - Perform mode transactions via web application such that when
synchronisation between central publisher and regional publisher take
place the join filter now includes some of the rows created in step 1
but not part of the filtred data in step 2
Step 5 - Merge Agent executes synchronizing between central publisher
and Regional Publisher. Inspection of the regional Publisher database
also is shown to be correct
Step 6 - Merge Agent executes on regional Subscriber to sync Regional
subscriber with Regional Publisher. And not all rows are transferred

Interestingly the missing rows seem to be the ones that were created in
step 1 and only satisfy the join filters at step 4 and 5.

Actually this problem i have just noticed seems very similar to one you
have been discussing on Developers Dex
http://www.developersdex.com/sql/message.asp?p=582&r=4712070&Page=1
with regards to Join Filters and GenerationIDs.

As part of my ivestigation i have noticed that the msMerge_GenHistory
table at the leafnode subscriber is populated with entries that have
not been necessaily delivered to the the subscriber. Does this sound
like expected operation. Could this account for the missing records in
that the merge process believes they have already been delivered.