none
Merge replication with rebublisher - retention periods and conflicts

    Question

  • Hi All,

    I have a question regarding publication retention periods when you have a republisher.

    I have a merge replication topology as follows:

    Server1 = publisher (with single database)

    Server2 = republisher (and distributor)

    Server3 = mirror of publisher DB

    Plus 6 remote/mobile devices that subscribe to publications (with static/join filters) on server2.

    There's an MSDN article that talks about "considerations for setting the publication retention period for merge publications" - and it says that "The retention period for any republisher must be set to a value equal to or less than the retention period set at the original Publisher."

    My remote subscribers need a retention period of 5 days because they can't always sync due to poor mobile coverage - and those publications are on the republisher. Does that mean that the retention period for the publication that the republisher subscribes to on the publisher must be 5 days or more?

    I'm just trying to clarify this because at the moment I have a retention period on the publisher publication of 1 day and 5 days for the remote clients and I'm getting lots of "false" conflicts between the publisher and republisher.

    And if I have to increase the retention period on the publication on the publisher do I need to reinitialise all subscriptions in the topology?

    Thanks for your help.

    Monday, July 22, 2013 8:09 AM

Answers

All replies

  • Yes, your upstream publisher should have a higher retention period than your republisher and downstream subscribers. If your republisher retention period if 5 days, your upstream publisher should have a retention period of 5 days or more.

    The false conflicts could be something unrelated to retention periods, it is hard to say from what you provide here.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Marked as answer by DarrrenW Monday, July 22, 2013 10:31 PM
    Monday, July 22, 2013 1:24 PM
    Moderator
  • Thanks Hilary.

    I'll change the publisher retention period and see what happens with the false conflicts.

    Will increasing the publisher retention period mean I have to reinitialise all downstream subscriptions?

    Monday, July 22, 2013 10:33 PM
  • After changing the retention period on the publisher to 10 days and reinitialising everything, I'm getting a lot of false conflicts between the publisher DB and republisher DB, even when the remote subscribers haven't syncronised except for the reinitialisation sync.

    Just to be clear, to make sure I'm not missing something, the republisher DB subscribes to a publication from the publisher DB and that publication has a retention period of 10 days and is not filtered. The remote clients subscribe to publications on the republishing DB and those publications have a retention period of 5 days and they all have static/join filters. The distributor DB is on the republishing server and has the default setup.

    FYI it's all SQL 2008 R2 CU7 running on Windows 2008 R2 and the 3 servers are in a domain.

    As soon as data is changed in the main DB (via a web site, and it's normal that there are lots of updates) we're seeing lots of update conflicts when the publisher DB syncs with the republisher DB. The conflicts are always in the subscriber uploads phase and always saying that the same column (we use column tracking) was updated at both the publisher and subscriber and the publisher wins (because its the publisher and we're using the default conflict resolver.) In this case that outcome is correct but the records are definitely not being changed on the subscriber.

    As a test, I set up another subscriber on one of the servers an identical publication. This subscriber was not a republisher, just a stand-alone subscriber where no data was manually entered or changed. The false conflicts occurred when this DB syncronised also.

    Any help would be greatly appreciated.

    Tuesday, July 23, 2013 11:56 PM
  • Hi Folks,

    Update:

    After following procedures suggested by Hilary in other posts, looking at a conflict and tracing the lineage and using lineage to look at sysmergesubscriptions, I'm seeing 5 entries in sysmergesubscriptions that look almost the same - subscriber_server, db_name and replnickname are all the same.

    That doesn't sound right does it?

    Appreciate your help

    Cheers
    Darren

    Wednesday, July 24, 2013 1:31 AM