RE: Data loss when using merge replication

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Thomas,

the retention period of subscribers (and history retention period) will
relate to how long a subscriber can go without syncing before it is deemed
expired, in which case it'll have to reinitialize. It sounds like you have a
problem because the default behaviour is that publisher wins conflicts with
subscribers and are asking if the situation is affected by a shorter or
longer retention period but this is mostly a separate issue. There is some
overlap because if the retention period was decreased, the problem might be
avoided because the subscriber would have to reinitialize and (optionally)
lose his changes altogether.

Again assuming that you have conflicts, apart from changing the retention
period you might want to consider changing the conflict resolver used and
there is even the option to have the inverse of the default behaviour and
have the subscriber win in a conflict with the publisher. However generally
I'd say that there is a business problem if you have conflicts and have
subscribers offline for a while.

You also mention that the updates are lost sometimes and not at others. Can
you confirm if there are conflicts in the conflict viewer. They may possibly
be the result of compensating changes rather than normal conflicts (see
http://support.microsoft.com/default.aspx/kb/828637/ for more info).

Your setup is fine for your requirements, and the most obvious alternative
would be transactional with queued updating subscribers which also has
conflict resolution but a smaller set of alternative resolvers. Peer-to-peer
could be used if the data is partitioned, but I gather that this is not the
case so as you mention, there would be an issue with conflicts.

HTH,

Paul Ibison
.

Relevant Pages

  • Re: "publisher always wins conflict" resolver? (for merge replication under SQL 2000)
    ... there are basically 3 types of conflicts -pk violations, update conficts - same row updated onboth sides or same column updated on both sides when you are using column level tracking, update/delete conflicts - row is updated on one side, deleted on the other. ... This is the default for the client subscription type - the first to the publisher will win against any other conflicts generated by other subscribers. ... - Microsoft SQL Server Subscriber Always Wins Conflict Resolver ...
    (microsoft.public.sqlserver.replication)
  • Re: "publisher always wins conflict" resolver? (for merge replication under SQL 2000)
    ... if i understand your example correctly, you do updates on two subscribers ... that lead to conflicts. ... should happen only on the publisher. ...
    (microsoft.public.sqlserver.replication)
  • Re: When Publisher is out of order ...
    ... I think what you are talking about is the retention period. ... After you have created your publication if you right click on it and select ... You can set how long you want your subscribers to remain off line ... Looking for a book on SQL Server replication? ...
    (microsoft.public.sqlserver.replication)
  • Re: Subscription marked inactive
    ... > 2) go to tools, replication, configure publisher, subscribers, and ... and then click the distributor tab. ... > retention period is. ... > Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Is Ok to clean msmerg_genhistory at publisher and not subscriber?
    ... By default these tables are cleaned up on each run on the subscribers, ... all named subscribers have checked in or the retention period has expired ... the merge replication process will not know which rows have changed ... genhistory tables every day instead of the 14 day at the PUBLISHER. ...
    (microsoft.public.sqlserver.replication)