none
How to use SQL Server 2008 Change Tracking and Sync Framework 2.0 CTP2 to sync two SQL Server Databases RRS feed

  • Question

  • Hello!

    I'm delving into the great sync framework to develop a synchronization system for a project. My scenario is as follows:

    - Server Database (SQL Server 2008 - maybe an Express Edition) where all data operations are conducted;
    - Client Database (SQL Server 2008 - maybe an Express Edition) where the data is only (by now) queried;

    This is basically an offline scenario, where sync direction will be Server -> Client.

    All the samples I've seen show scenarios with SQL Server and SQL Server CE. I managed to understand the examples and samples, but when I got and try to make something to use SQL Server in both ends, I get a bit stuck on which client provider to use, how to implement it and all.

    Is there anyone who can help me on this with "better" samples or something like that?

    Many thanks!
    Thursday, June 18, 2009 12:10 PM

Answers

All replies

  • Hi -

    You just need to use the same provider on the client side as well - you just need to instantiate the same provider on the client side.

    Thanks
    Deepa
    Deepa ( Microsoft Sync Framework)
    Thursday, June 18, 2009 4:52 PM
    Answerer
  • Really? I feel a bit stupid now... Didn't even try that one :S

    I'll give it a shot. Thanks!
    Thursday, June 18, 2009 5:36 PM
  • Well, not sure if I'm understanding this right. I'm thinking that I'm confused about something. So, bare with the newbie :D

    So i'm supposed to use, for instance, DbServerSyncProvider both to connect to the server and the client? I'm struggling to grasp this one.

    I'm trying to develop an application that should let me maintain synchronized a local (main server - referred now on as DB1) SQL Server 2008 database, which has the data from the ERP applications, with a SQL Server 2008 database that is in a remote datacenter (client - referred now on as DB2) that supports a web portal.

    My application would, on demand or via a schedule, send the changes from the local databse (DB1) to the remote database (DB2). This is always the direction of synchronization, at least in this stage. Later there will be 2 way. The cahnge tracking has to be SQL Server 2008 change tracking, not custom.

    I've tried to accomplish this modifiying the documentation examples, but to no avail. I'm feeling officaly lost here. Do I have to implement providers? Can I do this with DbServerSyncProvider ? If I do this, I get a cast error on the SyncAgent.LocalProvider...

    Any help would be greatly apprieciated.
    Friday, June 19, 2009 2:21 PM
  • Hi,

    To sync two SQL Server 2008 database, please consider using the new collaboration providers (SqlSyncProvider, SqlCeSyncProvider, and DbSyncProvider).  With the latest MSFv2 CTP2, new APIs are available for you to easily provision your database and create new sync provider instance. For more information, please look at: http://msdn.microsoft.com/en-us/library/bb902829(SQL.105).aspx.

    Thanks,
    Dong
    This posting is provided AS IS with no warranties, and confers no rights.
    • Marked as answer by stargazernc Wednesday, July 1, 2009 8:57 AM
    Friday, June 19, 2009 7:47 PM
    Moderator
  • First, thanks for the reply!

    I've already gone through that documentation. There is nothing over there that allow me to use SQL Server 2008 Change Tracking with those providers. In fact, I've read somewhere in the current release, they do not support just that...

    As such, I'll have to implement custom change tracking, with tombstone tables, triggers and alike, which was exactly the kind of thing I was trying to avoid. :)
    Monday, June 22, 2009 10:52 AM
  • Unfortunately, SQL Server 2008 change tracking is not supported by the current collaboration sync providers. Custom change tracking is the only option for now. You can find more explanations in this forum post: 

    http://social.msdn.microsoft.com/Forums/en-US/uklaunch2007ado.net/thread/0b3e7150-fa76-4c41-97b7-e12d6f3f6713

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    • Marked as answer by stargazernc Wednesday, July 1, 2009 8:56 AM
    Friday, June 26, 2009 8:29 PM
    Moderator
  • Hello Dong!

    Ok, I understood everything post before. Now it boils down to my main doubt. All the examples I found are to sync SQL CE 3.5 with SQL Server 2008. Is there any "special" reason for that? I mean, why there isn't samples or examples to explain how to sync two SQL Server 2008 databases?

    Thanks for the help! :)
    Wednesday, July 1, 2009 8:56 AM
  • Dong, do you have any samples you could point me to that use these new providers in an n-tier solution?
    Saturday, July 11, 2009 3:35 AM
  • Deepa or anyone from Microsoft Sync team,

    Can you explain in details your response:
    "you just need to instantiate the same provider on the client side"

    Can anyone from Microsoft Sync team be clear about whether two way n-tier synchronization between 2 instances of SQL Server 2008 is/will be possible with new version 2.0 ?
    If possible, where we can find the most complete example and guidelines in documentation?

    Thank  you,
    Rad

    Tuesday, July 14, 2009 6:25 AM
  • Is there an answer to Radiv076 question, now that v2.0 has been released.

    I also have a scenario where I need to sync 2 instances of SQL Server, and I would like to use the built-in SQL Server Change Tracking.

    Thanks in advance.

    Garry
    Tuesday, October 27, 2009 10:02 AM
  • Yes, I am interested in the same issue.

    In a nutshell, I want to sync between two SQL Express 2008 databases, using the built-in SQL 2008 Server Change Tracking.  Is this possible with v2.0?  If so, is there sample code?
    Tuesday, October 27, 2009 7:54 PM
  • This has been asked hundred times, but I wonder why it has been left without a clear solution. Since version 1.0 people has been asking for samples on how to synchronize between SQL express and SQL Server and then all we had was a sql express custom provider. Now there is an official Sqlprovider and still there are lots of confusion over sync between two SQL servers. As others has expressed I too express my interest in seeing a demo where Sync framework uses SQL Server 2008 and its built in change tracking for Sync.
    ASP.NET 3.5 | jQuery | SQL Server 2005 | LINQ | C# 3.0 | Crystal Reports
    Thursday, October 29, 2009 4:54 PM
  • Well, we finnally managed to pull this one out, using DbServerSyncProvider and the SQLExpress provider than can be found on codeplex. At the moment, it does it's work, but we're still testing.

    As soon as I have a definite answer, I'll post some code.

    But, we simply did some tweaks to one of the examples on SQL Change Tracking between SQL Server and SQL Server CE, changed the client provider and it worked out ok...

    I'll try to test and validate this ASAP, so I can post the code here.
    • Edited by stargazernc Wednesday, November 4, 2009 3:30 PM completing the text
    Wednesday, November 4, 2009 3:24 PM
  • I look forward to seeing the code.
    Friday, November 13, 2009 6:59 AM
  • Hopefully, it will be available next week. I've been on another assignment. :)
    Friday, November 13, 2009 2:59 PM
  • The sample code for the SQL Express SyncProvider is on MSDN Code Gallery at http://social.microsoft.com/Forums/en-US/synctechnicaldiscussion/thread/a3875382-a768-4125-ac59-b58a0a0e2c41?prof=required&wa=wsignin1.0, not Codeplex :)
    Friday, January 8, 2010 8:24 AM
  • Well, we finnally managed to pull this one out, using DbServerSyncProvider and the SQLExpress provider than can be found on codeplex. At the moment, it does it's work, but we're still testing.

    As soon as I have a definite answer, I'll post some code.

    But, we simply did some tweaks to one of the examples on SQL Change Tracking between SQL Server and SQL Server CE, changed the client provider and it worked out ok...

    I'll try to test and validate this ASAP, so I can post the code here.

    Any luck with this? Can you post the sample?

    Thanks
    * Note: I have no formal CS education, so take all advice with caution :)
    Wednesday, March 10, 2010 5:10 PM
  • Still looking for an example of SQL to SQL sync using the built in SQL 2008 change tracking.  DOes anyone have an example?
    Thursday, February 3, 2011 5:49 AM
  • Hi Dwight,

    Did you look at WebSharingAppDemo-SqlProviderEndToEnd demo ? :

    http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=3762

    I use it, works great. Just a little question about conflict detection...

    Regards,

    David

    Monday, February 7, 2011 2:29 PM