locked
Database Provisioning for Synchronization RRS feed

  • Question

  • I  am new to ADO.Net synchronization and I am creating my first application with disconnected data - it will synch a local SQL Server 2008 CE database with a remote SQL Server 2008 Express database.  I am using VS 2008 and I created used the Data Source Wizard in VS 2008 to set up the initial configuration including the SQL Server CE database and the various synch agents.  I thought that the wizard in VS 2008 would make whatever changes were needed to the database to support synchronization, but it didn't.

    I think what needs to be done is to create additional columns in each table to support synchronization and to create tombstone tables as described in the following:

    http://www.codeguru.com/csharp/.net/net_asp/miscellaneous/article.php/c14611/

    Is there a wizard that will do this for me or do I need to create all these columns, tables, and triggers manually?

    Here's the code I'm using to do the synchronization:

                // ----Create Synchronization Agent--------------------------------------------------            
                ProcessMapsCacheSyncAgent dbsynchAgent = new ProcessMapsCacheSyncAgent();
    
                // ----Configure the Tables to Synchronize-------------------------------------------            
                dbsynchAgent.Configuration.SyncTables.Clear();
                dbsynchAgent.Configuration.SyncTables.Add("Table1");
                dbsynchAgent.Configuration.SyncTables.Add("Table2");
                dbsynchAgent.Configuration.SyncTables.Add("etc.");
    
                // ----Run the Synchronization------------------------------------------------------            
                SyncStatistics synchStats = dbsynchAgent.Synchronize();
    Am I missing something?  Is there more that is needed other than setting up the databases?  I tried a simple synchronization and it worked, but deletions aren't being propagated - I suspect that is because the data table tombstones haven't been set up.

    Any help would be appreciated...

    Thanks,

    Chuck
    Wednesday, December 16, 2009 10:19 PM

Answers

  • Hi Chuck,

    It is supported, but from what I experience, not out of the box. i.e. you must make some changes to your code and database to get it to work.

    There is this blog post that would indicate it as easy as just checking a box, however, on my machine, with VS 2008 SP1, I do not get the 'Use Sql Server change tracking' checkbox.
    http://blogs.msdn.com/sync/archive/2008/06/25/sql-server-2008-integrated-change-tracking.aspx

    Then there is this page in the MSDN documentation that explains how to set it up manually:
    How to: Use SQL Server Change Tracking

    And maybe best of all is the following CodePlex project:  
    Sync Framework SP and Object Generator for SQL Server 2008 Change Tracking
    This really nice projects has taken the steps from the MSDN documentation and converted it into a tool that just generates all the code for you.

    In the end, it seems like you must do it manually. It is also best to start off with a 'clean' database, without any change tracking enabled, or change tracking triggers/tables of the standard SQL 2005 solution.

    Hth,
    Rudi
    • Marked as answer by Yunwen Bai Saturday, December 26, 2009 7:00 PM
    Friday, December 18, 2009 12:26 PM
  • the site Rudi shared out (http://msdn.microsoft.com/en-us/library/cc305322(SQL.105).aspx ) has pretty much all you need to provision the server db for Sync Service with Sql Change Tracking. the 8 proc-s are for the sync adapter for a table and in this site they are just in Sql query format. regardless SCT is used or not, they will be needed for Sync.

    Hope this clears some confusions.

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by chuckc3 Monday, January 4, 2010 11:56 AM
    Saturday, December 26, 2009 8:33 PM

All replies

  • I have seen conflicting reports on this...can someone please confirm or deny that change tracking is supported for SQL Server 2008 in Sync Services?  What I need to know is whether I need to do any special configuration to the database tables (like adding triggers, timestamps, tombstone tables, etc.) or whether I can just rely on SQL Server 2008 change tracking.

    Thanks,

    Chuck
    Thursday, December 17, 2009 11:05 PM
  • Hi Chuck,

    It is supported, but from what I experience, not out of the box. i.e. you must make some changes to your code and database to get it to work.

    There is this blog post that would indicate it as easy as just checking a box, however, on my machine, with VS 2008 SP1, I do not get the 'Use Sql Server change tracking' checkbox.
    http://blogs.msdn.com/sync/archive/2008/06/25/sql-server-2008-integrated-change-tracking.aspx

    Then there is this page in the MSDN documentation that explains how to set it up manually:
    How to: Use SQL Server Change Tracking

    And maybe best of all is the following CodePlex project:  
    Sync Framework SP and Object Generator for SQL Server 2008 Change Tracking
    This really nice projects has taken the steps from the MSDN documentation and converted it into a tool that just generates all the code for you.

    In the end, it seems like you must do it manually. It is also best to start off with a 'clean' database, without any change tracking enabled, or change tracking triggers/tables of the standard SQL 2005 solution.

    Hth,
    Rudi
    • Marked as answer by Yunwen Bai Saturday, December 26, 2009 7:00 PM
    Friday, December 18, 2009 12:26 PM
  • Thanks, Rudi...that's very helpful...I'll look into that.

    By the way, when I set up a data source in VS2008, it looks like it does it at least partially because it sets up a SQL change to turn on change tracking in the database, but it doesn't look like it completely works (at least not for deletions).

    Which version of Sync Services are you using?


    Chuck
    Friday, December 18, 2009 12:39 PM
  • The reference to the sample Rudi posted is good, but it's just an example...surely there must be a concise statement of what needs to be done to modify a SQL Server 2008 database to support Sync Services.  I've seen a lot of confusing and conflicting information on this...some sources say you need to add tombstone tables and change tracking columns to SQL Server, other sources like the one Rudi referenced seem to indicate that none of that is necssary all you need to do is add (8) stored procedures for each table...maybe that's only if you have SQL Server Change Tracking Turned on...

    When you set up a data source in Visual Studio 2008, it seems like the process is fully automatic - you just check off in the data source configuration wizard that you want to have a local database cache, tell it to use SQL Server Change Tracking, and identify the objects you want to synchronize and it seems to imply that it will set up everything for you (creating the sync agents and making the modifications to the database).  But I don't believe that is the case, I'm sure there are some things that the user must do to fill the gaps of what VS2008 does automatically for you and whatever Sync Services is expecting.

    Surely this must be documented somewhere, but I can't seem to find a concise and reliable source on this anywhere.  Any help would be appreciated.

    Thanks,
    Chuck
    Tuesday, December 22, 2009 6:10 PM
  • the site Rudi shared out (http://msdn.microsoft.com/en-us/library/cc305322(SQL.105).aspx ) has pretty much all you need to provision the server db for Sync Service with Sql Change Tracking. the 8 proc-s are for the sync adapter for a table and in this site they are just in Sql query format. regardless SCT is used or not, they will be needed for Sync.

    Hope this clears some confusions.

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by chuckc3 Monday, January 4, 2010 11:56 AM
    Saturday, December 26, 2009 8:33 PM
  • Thanks
    Chuck
    Monday, January 4, 2010 11:54 AM