none
Looking for SqlServer to SqlServer Sync Provider Example RRS feed

  • Question

  • I'm trying to find a sample application that shows how to synchronize just certain tables and/or partial tables from one sqlserver to another sqlserver.  Can someone point me at an example of doing this?
    Peter Kellner http://peterkellner.net Microsoft MVP • ASPInsider
    Wednesday, June 2, 2010 8:35 PM

Answers

  • For (1):

    • You do have to worry about order when you're syncing multiple tables with PK/FK relationships.
    • The order is controlled by the order in which you add table descriptions to the scope description.  i.e. the order of calls to DbSyncScopeDescription.Tables.Add() calls.
    • The tables with the PK's need to be added before the tables with the FK's.  Basically the same order you would need to create the tables in a T-SQL script.
    • This topic is not currently covered in the documentation and we will fix this in the future.

    For (2):

    • It can.  As you state, you can use the SetCreateTableDefault() method on the SqlSyncScopeProvisioning class.  Pass in the DbSyncCreationOption.Create value.
    • This method should only be used for simple cases as it does not copy over and create all attributes for a table.  E.g. you would have to manually re-create the indexes.
    • Provisioning describes the steps required to get a database in a state so that it can be synced.  There steps include creating the scope meta-data tables, creating the side-tables for change tracking, creating the triggers for change tracking and creating the stored procedures using to select, insert, update and delete data during a sync.  Provisioning can also include creating the actual tables in the other database, if they have not been created out-of-band.

    Regards, Mark

     

    Tuesday, June 8, 2010 4:32 PM
    Moderator

All replies

  • Hey,

    Please take a look of SyncFX Database Synchronization Provider Samples @ http://code.msdn.microsoft.com/sync.  

    Thanks,


    Ann Tang
    Wednesday, June 2, 2010 9:23 PM
  • I'm really looking for a sample that simply replicates between two sqlserver's with no IIS involved.  that is, not having to add any special columns to tables, just give it two connection strings, a list of tables (with constraints if possible) and invoke a sync.  That is, very simple, just like the file sync provider example.  I don't want WCF in IIS, I just want an example of a simple console program that sync's two sqlserver databases (not sqlexpress, though maybe that is the same)

    If you could point me at one solution, not a list, that would be helpful

    Thanks


    Peter Kellner http://peterkellner.net Microsoft MVP • ASPInsider
    Thursday, June 3, 2010 5:16 PM
  • Here is one 2Tier sample of SQL server to SQL CE sync. You could replace the SQL CE setup with SQL server  and this would give you some idea.

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

     

    Thank,


    Ann Tang
    Thursday, June 3, 2010 5:57 PM
  • I did create a sample that works now syncing two sqlserver2008 databases similar to the orders,orders_details example you mentioned. I now have two more questions regarding that.

    1. If I have a lot of tables I want to synchronize and they have lots of foreign keys, how to I make sure they sync happens in the correct order? (or do I have to worry)

    2. Will the sync framework create tables automatically if they don't exist in the destination database? I'd like it to.

    I think the line: //indicate that the base table already exists and does not need to be created serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip); has something to do with creating new tables, but the doc is pretty skinny on this. can you explain what it means along with a short explantion of what is meant by provisioning. Thanks,


    Peter Kellner http://peterkellner.net Microsoft MVP • ASPInsider

    • Edited by Peter Kellner Monday, June 7, 2010 12:56 AM formatting
    Monday, June 7, 2010 12:55 AM
  • For (1):

    • You do have to worry about order when you're syncing multiple tables with PK/FK relationships.
    • The order is controlled by the order in which you add table descriptions to the scope description.  i.e. the order of calls to DbSyncScopeDescription.Tables.Add() calls.
    • The tables with the PK's need to be added before the tables with the FK's.  Basically the same order you would need to create the tables in a T-SQL script.
    • This topic is not currently covered in the documentation and we will fix this in the future.

    For (2):

    • It can.  As you state, you can use the SetCreateTableDefault() method on the SqlSyncScopeProvisioning class.  Pass in the DbSyncCreationOption.Create value.
    • This method should only be used for simple cases as it does not copy over and create all attributes for a table.  E.g. you would have to manually re-create the indexes.
    • Provisioning describes the steps required to get a database in a state so that it can be synced.  There steps include creating the scope meta-data tables, creating the side-tables for change tracking, creating the triggers for change tracking and creating the stored procedures using to select, insert, update and delete data during a sync.  Provisioning can also include creating the actual tables in the other database, if they have not been created out-of-band.

    Regards, Mark

     

    Tuesday, June 8, 2010 4:32 PM
    Moderator