Using change tracking to implement synchronization RRS feed

  • Question

  • I need to implement a specific synchronization application function between multiple disconnected clients and a central server.

    I've examined the Change Tracking provided and it seems that that may be all I need.    What I'm thinking is that I can provide a stored proc on the server that returns the (updates and inserts for the client and pass this infomation in a formated "packet" for the clients who can update/insert records as appropriate.   The same would be done with deletes as well.  Clients can similarly send packets to the server that would update any data that needs to be passed to the server.

    I would need to account for the situation where the Change Tracking date expires and in those cases send all the data from the source to the destination.

    It I handle the tables in the appropriate sequence, then my relationships would work correctly as well.

    I see that there is a lot more to the synchronization libraries than I would be using if took this approach so I am wondering if I'm way off track by pursing this option.

    Please comment.   I'm using SQL Server at the server and SQL Express at the client.


    • Edited by GPeele Wednesday, February 27, 2013 8:32 PM
    Wednesday, February 27, 2013 8:30 PM

All replies

  • SQL Change Tracking in Sync Fx is only supported if your using the Local Database Cache project item or the older offline provider combos of SyncAgent/SqlCeClientSyncProvider/DBServerSyncProvider.

    that combo doesnt support SQL Express as the client.

    why dont you just use the newer SqlSyncProvider/SyncOrchestrator combo? the documentation contains a sample of synchronizing SQL Express and SQL Server. Likewise, you can also do metadata clean up via the API to remove metadata based on a retention criteria.

    Thursday, February 28, 2013 11:16 AM
  • I thought that Change Tracking (at least the way I'm using it) was a function of turning on (Change Tracking) at the table level at the database/table level within SQL Server.     This works as far as I can tell in SQL Express.

    Using this approach, there is a provision of how long to keep the change tracking in that context as well and it removes change tracking info after a specified period.    I'll look into the SqlSyncProvide/SyncOrchestrator combo and I really want to understand the value provided over and above the approach I'm taking now.  

    Thursday, February 28, 2013 1:39 PM
  • yes, change tracking is feature of SQL Server. but the providers that works with the SQL Change Tracking is the older sync provider whose only supported client counterpart is SQL Ce. there is no client sync provider for SQL Express that can sync using SQL Change Tracking.
    Thursday, February 28, 2013 1:53 PM
  • Hi I am having the same issue as GPeele.

    So let me see if I understand:

    • I can't synchronise SQL Server + SQL Server Express using their Change Tracking feature because there's no Provider for Express, even though the documentation actually suggests using Change Tracking (http://msdn.microsoft.com/en-us/sync/bb887608.aspx);
    • If I use the SqlSyncProvider/SyncOrchestrator combo, I need to pollute my database with extra tracking tables and slow it down with triggers;

    From what I can see my only alternative to use SQL Server 2008 Change Tracking and avoid the extra tables in my schema is to write SyncProviders by hand which in turn means manually writing a lot of SQL by hand?

    Friday, March 8, 2013 4:40 AM
  • yes - OOTB, you can't. you have to build your own provider for any database other than SQL CE. There used to be a sample SQLExpressClientSyncProvider from MS but they pulled it out from the download site. Try searching for SQLExpressClientSyncProvider  and you should find something from another developer's blog post.

    SQL Change Tracking has system tables to track changes as well and while its not using triggers, it also has to keep track of the changes whenever you make one.

    one more thing you need to take into  consideration is that if you use the older DBSyncServerProvider, you cannot re-point your clients to sync with another central database without reinitializing them.

    the older provider doesnt allow you to do batching either nor take advantage of bulk updates if you're using SQL 2008 and above.

    Friday, March 8, 2013 6:39 AM
  • Thanks for that, appreciated.

    I have to say I can't help but feel a bit disappointed with the Sync Framework. This particular feature was promised in 2008 and never saw the light of day. This type of limitation almost pushes you on the path of developing something yourself and not using Sync at all. The framework solves complex issues such as batching and conflict resolution but at the same time makes you write code for most of it by hand.

    The feeling I get is that Microsoft wrote this library to solve 1 particular problem scenario, released it and never bothered to finish it up.

    Saturday, March 9, 2013 10:42 PM
  • The newer SQLSyncProvider supports SQL 2005 and above, SQL Express, LocalDB and Azure SQL Database. Sync Fx is an SDK, what you can't find OOTB, it provides you extension points to write it yourself. Never meant to be a "configure" only tool. 
    Sunday, March 10, 2013 1:19 AM