locked
Slow Merge Replication RRS feed

  • Question

  • Hello,

    We are running merge replication on SQL Server 2008 R2 with web synchronization (all subscribers are 2008 R2). The publication uses dynamic filters (with join filters) based on HOST_NAME().

    Over time the synchronisations have been taking longer and longer.  We initially had a very long retention period which we are slowly reducing and we regularly rebuild the merge table's indexes. However we are now in a position where the synchronisations are taking so long they are overlapping.  We can't really increase the sync period as the data is needed at the publisher.

    We have run the profiler on the publisher and have noticed a large number of calls to sp_MSsetgentozero during the sync.  Is this normal?

    Thanks in advance.

    Tim

    Tuesday, August 21, 2012 11:28 AM

Answers

  • Yes, these are likely primary key conflicts which will not resolve on their own and will slow the duration of synchronization down.

    You'll have to resolve these manually by identifying the conflicting rows and deleting one or the other.

    As Hilary mentioned, its a good idea to reindex the merge tables and update statistics as well.


    Brandon Williams (blog | linkedin)

    • Marked as answer by Maggie Luo Tuesday, August 28, 2012 6:35 PM
    Wednesday, August 22, 2012 1:55 PM
  • Yes, it is possible to mix web sync and non web sync subscribers on the same publisher and switch web sync subscribers to a direct connection over VPN without a reinitialization.  They can be switched by setting the Subscription property Use Web Synchronization or @use_web_sync to false.

    Brandon Williams (blog | linkedin)

    • Marked as answer by Maggie Luo Tuesday, August 28, 2012 6:35 PM
    Saturday, August 25, 2012 1:55 AM

All replies

  • Hi Tim,

    Are you getting recurring conflicts?  As in conflicts that are occurring on every sync and not getting resolved?


    Brandon Williams (blog | linkedin)

    Tuesday, August 21, 2012 6:20 PM
  • Hi Brandon,

    Yes there are a few, could this cause the slow-down?

    Tim

    Wednesday, August 22, 2012 9:24 AM
  • Tim try to reinidex all of your merge tables and then issue an update statistics sp_msforeachtable 'UPDATE STATISTICS ?'

    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

    Wednesday, August 22, 2012 1:38 PM
    Answerer
  • Yes, these are likely primary key conflicts which will not resolve on their own and will slow the duration of synchronization down.

    You'll have to resolve these manually by identifying the conflicting rows and deleting one or the other.

    As Hilary mentioned, its a good idea to reindex the merge tables and update statistics as well.


    Brandon Williams (blog | linkedin)

    • Marked as answer by Maggie Luo Tuesday, August 28, 2012 6:35 PM
    Wednesday, August 22, 2012 1:55 PM
  • Thank you for the suggestions.  I have carried out both with some success but I'm still getting very high sync times.  There is quite a lot of data being generated across the system so I was wondering if web synchronisation could be the bottle-neck.  To test the theory is it possible to mix web sync and non web sync subscribers on the same publisher and switch some subscribers to a direct connection over VPN without reinitialisation?


    Thursday, August 23, 2012 8:27 AM
  • Yes, it is possible to mix web sync and non web sync subscribers on the same publisher and switch web sync subscribers to a direct connection over VPN without a reinitialization.  They can be switched by setting the Subscription property Use Web Synchronization or @use_web_sync to false.

    Brandon Williams (blog | linkedin)

    • Marked as answer by Maggie Luo Tuesday, August 28, 2012 6:35 PM
    Saturday, August 25, 2012 1:55 AM