Sql 2 Sql using 2008 Change Tracking Demo RRS feed

  • Question

  • I know it' s a long shot but it would be a great help if anyone knew of an Sync for ADO.NET 2.0 example that sync's two Sql 2008 databases via WCF and that uses Sql 2008 change tracking on both. There are so many examples of Sql to CE but that's not what were looking to do.

    * Note: I have no formal CS education, so take all advice with caution :)
    Tuesday, March 9, 2010 9:18 PM


All replies

  • I'm not sure there's one from MS, but i think you can build one using the existing samples. I'm assuming offline scenario since you want to use SQL Change tracking.

    The first item you need to address is the fact that there is no out-of-the-box client sync provider for SQL Server/SQL Express. The closest you can have is a sample client provider.

    So here's what you can do:

    1. Download the SQLExpressClientSync sample client provider and use it as your client sync provider : http://code.msdn.microsoft.com/sync/Release/ProjectReleases.aspx?ReleaseId=1200
    2. Follow the steps here to enable SQL change tracking on both SQL databases: http://msdn.microsoft.com/en-us/library/cc305322(SQL.105).aspx
    3. Follow the steps here to use WCF: http://msdn.microsoft.com/en-us/library/bb902831(SQL.105).aspx

    You can actually use as a guide too the existing SQL to CE samples except that you'll have to replace the CE provider with the sample SQL client provider.


    • Marked as answer by EisenB Wednesday, March 10, 2010 4:26 PM
    Wednesday, March 10, 2010 2:29 AM
  • Thank you so much, I'm going to give it a try. I guess my primary concern was if it can even be done since I havent seen it yet. I've noticed in programming that if you find yourself going down a path that hasn't been traveled before then you are most likely going the wrong way.
    * Note: I have no formal CS education, so take all advice with caution :)
    Wednesday, March 10, 2010 4:29 PM
  • I am working through these steps and so far I see one flaw in my thinking. My hope was that I could use Sql 2008 change tracking on both ends and avoid having to make any changes to the database whatsoever (i.e. tracking tables etc.). Now that I am working thorough filling in the ClientSyncProvider methods I see that the client needs to have some knowledge of when it was last updated. In the case of change tracking it would be the version. In both the CE and Express clients it has this somewhere.

    My initial thinking was that the server and client were peers but it seems more like the server is the boss and it's the client's job to keep track of when it was last sync'd.

    * Note: I have no formal CS education, so take all advice with caution :)
    Wednesday, March 10, 2010 7:01 PM
  • in the offline scenario, it's the client who keeps track of what was sent and what was received. In the collaboration scenario, everyone else keeps track.
    Thursday, March 11, 2010 1:11 AM
  • Yep, I just saw that this morning. I used the visual studio local data cache tool to create a sync between Sql and Ce, and now I am trying to steal the code it generated to make it Sql to Sql. I just realized now I need to change the provider for Sql from DbServerSyncProvider to DbSyncProvider for th ecollaboration scenario. Thats where I am right now. Hoping to get this working today.
    * Note: I have no formal CS education, so take all advice with caution :)
    Thursday, March 11, 2010 3:07 PM
  • take note that provisioning for offline and collaboration scenarios are different. There's not much that you can re-use from the designer generated code. The collaboration providers uses the concept of "scopes" to define what to synchronize and it has a totally different change tracking mechanism than the one's generated by the designer.

    i reckon it will be faster if you just provision the sync using the Sync provisioning API than going thru the designer generated code figuring out how to change it to work with the collaboration providers. see http://msdn.microsoft.com/en-us/library/ee617382(SQL.105).aspx
    Thursday, March 11, 2010 3:18 PM
  • Do you mean skip 2008 change tracking altogether and just use the SqlSyncScopeProvisioning Class to set it up?
    * Note: I have no formal CS education, so take all advice with caution :)
    Thursday, March 11, 2010 5:36 PM
  • you can't use SQL Change tracking with the collaboration providers.
    Thursday, March 11, 2010 5:44 PM
  • Ahh that's too bad. I really dislike cluttering up the db with extra tables and triggers especially for something so generic. So back to where I started, and your original reply stating I need to use the offline scenario which at the time I didn't realize there was a difference.

    Anyway, I am working on the POC for an in house developed point of sale system. Originally we thought it would be neat if each terminal at the store worked independently. I.e. if the stores network went down the terminal remained up. Considering we have 450 stores, and about 3 terminals per store I think adding all those extra tables and triggers are going to cause some maintenance and deployment difficulties, especially since it's a new system and db changes are likely.

    So here is what I am thinking. In the back of each store have a sql express instance and a exe that reaches out periodically to the home office and sync up.

    - Use offline scenario
    - Use Sql Server for home office with 2008 change tracking enabled
    - Use Sql Express for client with 2008 change tracking enabled

    |---------------------------Store--------------------------| <--> |---------------Home Office------------------|
    SqlExpressClientSyncProvider <--> SyncOrchestrator <--> Web Service <--> DbServerSyncProvider

    Now I'm working on how to cobble this together from all the different demos, etc. I can base my POC on the WebSharingAppDemo - End-To-End SharingAppDemo Sample Application. Then I can steal the DbServerSyncProvider and SyncAdapter from the Visual Studio local data cache code behind. This will take care of the home office provider. Then I can customize the SqlExpress provider to utilize sql change tracking and use that for the store side.

    * Note: I have no formal CS education, so take all advice with caution :)
    Thursday, March 11, 2010 6:45 PM
  • you might want to try this out instead: http://msdn.microsoft.com/en-us/library/dd938879.aspx

    just replace SmartDevice Project with a WinForms project, follow the same steps. Then when the designer's done generating the code, substitute the sample SQL Client provider with the CE provider.

    Thursday, March 11, 2010 11:03 PM
  • Thanks. I just about have it working without the WCF piece.
    * Note: I have no formal CS education, so take all advice with caution :)
    Monday, March 15, 2010 3:43 PM
  • I came so close but I think I finally ran into something that doesn't seem to have a decent workaround. It has to do with batching. There is one documented flaw with using 2008 change tracking and sync which is that if you enable change tracking on a database that already has rows in it then the initial version is the same for all those rows. This is a problem because the batching feature for offline/SyncAgent bases its batch on the version. So if there are 50k rows in version 1 then it will try to download all 50k in one batch. There are workarounds for this however there is another problem which they do not mention. If lets say your data center recieves 50k new customers in a datafile and they load it into SQL using an insert statement (i.e. insert into x select x) then SQL change tracking will assign all 50k rows the same version. Again this will trick the SyncAgent into trying to load all records in one batch.

    So basically I was able to get SQL change tracking working on both ends, and put WCF inbetween them but now I am stuck at batching.

    * Note: I have no formal CS education, so take all advice with caution :)
    Wednesday, March 17, 2010 5:07 PM