locked
Using Merge Replication Without Precomputed Partitions RRS feed

  • Question

  • I am using merge replication with SQL 2012.

    We have a performance issue with precomputed partitions. We tried turning it off, but the second sync took about 5 minutes even though there were just a couple of changes.

    We then tried to turn on the 'Optimize Synchronization' option, which makes the second sync just a few seconds.

    But the problem was when we tried to delete a record at the publisher. The delete didn't propogate to the subscriber. Is this expected, and is there a workaround?

    Wednesday, November 28, 2012 3:09 AM

All replies

  • Precomputed partitions basically builds a table of what has changed in a partition so the merge agent can consult this table at sync time instead of having to figure out what belongs to a partition and what does not.

    If you are not using precomputed partitions you have to crack open the views and ensure that there are correct covering indexes on all the join columns.

    If you do a delete which is not part of the filter or partition do not expect this row to move. So it might or might not be expected to move. A good check is to see if it is in tombstone. If you want it to move again use sp_mergedummyupdate.


    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

    • Proposed as answer by Shulei Chen Thursday, November 29, 2012 2:51 AM
    Wednesday, November 28, 2012 3:53 AM
    Answerer
  • But if you find a record at the publisher and verify that the record is at the subscriber, then delete it from the publisher, that delete should propagate correct?

    From our testing this didn't happen. This suggests that having precomputed partitions off and having the 'Optimize Synchronisation' option on doesn't seem to work properly.

    I will test it again and look in tombstone.

    Thursday, November 29, 2012 2:35 AM
  • If you delete a row at the publisher it will delete the row on the subscriber IF the delete meets the filtering criteria or is part of the partition. You may find the delete was logged as a conflict.

    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

    Thursday, November 29, 2012 2:54 AM
    Answerer