Pre Existing Sync - Now want to setup another sync with another database RRS feed

  • Question

  • right iv'e got a sync working perfectly using the Sync framework , local db and azure.


    Now i'm looking to add another database which will act as the master for the local (so new DB > local) and currently we have (local > azure)


    I just want to make sure I understand this - please let me know if i'm incorrect in any of this.



    There are already triggers which have been created by by the framework in the local db


    I will need to provision my new master db (as the server  for the local db)


    as stored procedures already exist due to the original sync 


    If i provision again will I need to setup a new schema for the additional scopes


    Now the new scopes will be using some of the tables which are already part of scopes from the local db > azure sync routine.

    If my new master to local sync requires the syncing of more columns Am i right in thinking that I will need to deprovision my existing sync routine and recreate it with the same column structure as my yet to be created Master to Local?

    If i understand that correctly if i setup the sync for this new master to local, the sync framework still uses the same stored procedures as the existing sync so the column definitions need too match?


    and finally - can sync framework handle composite primary keys?

    Wednesday, September 14, 2011 12:10 AM

All replies

  • when you provision an additional scope on the same schema for tables that are already part of an existing scope, it will reuse the triggers, UDT and stored procedures of the existing scope and will simply add one additional selectchanges stored procedure for the new scope.

    so if the structure of the new scope is different from the existing scope, you will get an error when the new scope tries to re-use these objects.

    you can deprovision/reprovision but you will lose the sync knowledge between local->azure and they will behave as if they were never synched before.

    creating a new scope under a different schema will force creation of new sync objects however, this will mean having multiple triggers fire for the same operation since there will be a trigger for the first scope and another one for the second scope. also, you will have duplicate tracking tables as well.

    Wednesday, September 14, 2011 1:19 AM
  • Ok thanks for that June



    In regard to adding additional scopes.


    You mention that if i set up my provisions in diff schemas (i.e mastersync, and cloudsync)


    It would actually create multiple triggers for especially the same task on the same tables covered by the sync.

    so separating on sync may not be a good idea.


    but what if i wnted to deprovision a sync (i.e if i only wanted to remove the cloudsync objects going forward I take it I wouldnt have a way to just remove those objects if they are all in the same schema ? or would I be able to deprovision based on scopes ? i..e deprovision('cloudscope') as an example.

    If i can deprovision based on scope then having them in the same schema may be okay.


    In regard to losing sync knowledge this will not be too much of an issue at the moment as the azure db is not actually live just yet.



    So let me make sure I understand the steps 



    1. Deprovision my current sync (cloud and local)

    2. Create new Master Sync Provision (Master to Local) - on a diff schema for cleanness (sync schema)

    3. Run Sync

    4. Create Local to Azure Sync Provision (Local to Cloud) - using (sync schema) - using alot of the same tables as the above sync therefore keeping the table structure.

    5. Run Sync


    Does this sound about right?


    also does sync framework handle Composite primary keys okay?


    Wednesday, September 14, 2011 8:04 AM
  • if the local=>master and local=>azure will have the same set of tables and columns, you can all provision them to use the same scope. the provider you're using works on a peer to peer scenario so you can actually do local<=>master, master<=>azure, local<=>azure

    speaking of the triggers, i stand corrected.

    not sure if its a bug, but provisioning using a new schema creates new SPs, UDT, tracking table and all othe scope_xxx tables, but it doesnt add new triggers. however, if you look at the new scope_config entry for the new scope under the new schema, its actually has entries for the trigger names using the new schema.

    yes, the provisioning would pick up composite PKs.

    Wednesday, September 14, 2011 8:36 AM
  • Thanks June


    Provision to use the same scope


    so have the one scope connecting 3 databases?

    sorry could you explain that in a bit more details June.


    In theory a few of the scopes will be the same across the 3 databases

    whilst azure and local will have a few unique scopes not required on master.



    Wednesday, September 14, 2011 9:20 AM
  • yes, you can have all 3 databases synching using one scope.

    if you have tables that you want to sync across all 3, create a scope for it.

    then create another scope for tables that's just between local and azure.

    Wednesday, September 14, 2011 9:59 AM
  • Thanks June


    any examples showing how to do this with the scopes?


    I would like to give htis a good with some testing dbs first



    Wednesday, September 14, 2011 3:39 PM
  • the documentation that gets installed with the framework has this. just lookup : Tutorial: Synchronizing SQL Server and SQL Server Compact. just replace the provider with the one you're working on and the process should be the same. nothing special, you just provision to 3 different databases using the same scope name and scope definition.

    Wednesday, September 14, 2011 11:55 PM