none
Can Sync Framework be used on a publisher or subscriber

    Question

  • We use merge replication for scale out and are considering using Sync Framework to sync to another system (possibly not a database or possibly another SQL Server database with its own replication topology. Is this supported or are there any conflicts to note?

    Thanks,

    Bruce

    Monday, July 22, 2013 3:08 PM

Answers

  • The Sync Framework needs to use some form of change tracking. You can write your own or hook into change tracking or CDC.

    Change tracking and transcriptional replication do have "dependencies" but not merge replication.

    I have worked on some large merge replication with thousands of subscribers and we did a test as a POC to see how the sync framework performed and quickly realized that it is tuned for low numbers of subscribers or relatively small amounts of changes. 

    The main reason that merge replication outperforms the sync framework is the merge replication collects changes to a table and will batch them to a subscriber in one data operation if possible.

    For most larger workloads this results in better performance. For small workloads with small numbers of subscribes you may find the sync framework to be a best fit.


    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 Bruce-K Monday, July 22, 2013 8:44 PM
    Monday, July 22, 2013 5:07 PM
    Moderator

All replies

  • Hello Bruce,

    There is no relation between SQL Server Replication and the Microsoft Sync Framework, they both act independent of each other, so there can't be a technical conflict between them (only data conflicts, if both changes data at the same time).


    Olaf Helper

    Blog Xing

    Monday, July 22, 2013 3:15 PM
  • As Olaf points out this can be done, but I think you will find that the Sync Framework is not as scalable as merge replication so you will find that it will not perform as well as merge replication end to end.

    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

    Monday, July 22, 2013 4:24 PM
    Moderator
  • Thanks for the responses and I'd like to confirm two details before I mark this as answered.

    First, there is an entry in the documentation that suggests replication is not completely independent (see bb933994 http://msdn.microsoft.com/en-us/library/bb933994.aspx). It specifically mentions interactions between Change Data Capture and transaction replication. I realize my concern is about Change Tracking (used by the Sync Framework ADO.NET provider) and merge replication so I'm hoping for confirmation that the absence of being mentioned means these two technologies are indeed independent as apposed to not allowed.

    Second, I was hoping you could add some detail about your "not as scalable" comment. Our system transactional requirements are actually fairly undemanding and the scale-out is to a reporting database to isolate the OLTP from potential reporting demands. The growth over time is predictable and within the capabalities of the system. The Sync Framework would be to another comparable system (throughput and power,...) so I don't think we need scalability in the sense of addressing increased demands over time or to sync with additional systems. Is that how you meant it?

    Again thanks,

    Bruce

    Monday, July 22, 2013 4:57 PM
  • The Sync Framework needs to use some form of change tracking. You can write your own or hook into change tracking or CDC.

    Change tracking and transcriptional replication do have "dependencies" but not merge replication.

    I have worked on some large merge replication with thousands of subscribers and we did a test as a POC to see how the sync framework performed and quickly realized that it is tuned for low numbers of subscribers or relatively small amounts of changes. 

    The main reason that merge replication outperforms the sync framework is the merge replication collects changes to a table and will batch them to a subscriber in one data operation if possible.

    For most larger workloads this results in better performance. For small workloads with small numbers of subscribes you may find the sync framework to be a best fit.


    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 Bruce-K Monday, July 22, 2013 8:44 PM
    Monday, July 22, 2013 5:07 PM
    Moderator