Modifying Sync Framework Scope Definition – Part 2 – Workarounds


In my previous blog Modifying Sync Framework Scope Definition – Part 1 – Introduction, we went thru the database objects that Sync Framework creates when a scope is provisioned. If you haven’t read the post, I suggest you go thru it very quickly including my other post on Sync Framework Provisioning so you have a better understanding of the objects that need to be modified to effect a scope modification.

Moving on in this post, we will try to cover some workarounds/recommendations on how to go about Sync Framework’s lack of functionality around scope modification.

Here are the choices:

1. Deprovision/Re-Provision

You’ve probably heard or read about this approach many times over. In fact this is the easiest way to accommodate a scope modification although it has its own ramifications.

If you are fine losing the sync knowledge, re-initializing the sync community and starting all over again, this one works well. The problem with this approach though is when you have large volumes of data that has been synchronized already.

If you deprovision the scope and retain the existing data on each replica, when you re-provision the scope, each replica will be populating their respective tracking tables. Since each participant in the sync community has most likely have the same data as the others, this will result to conflicts being fired when you try to upload or download data. Not that bad if you have a couple hundred rows only, bad if you have tens or hundreds of thousands of rows.

Now assuming you clean up all the other participating database and have them re-initialized from a single remaining copy (most likely a central server copy), you still incur the penalty of resending the rows all over again. Not too bad for a few hundred rows, expensive if you have tens or hundreds of thousand of rows.

Imagine wiping out your sync community just because you want to add an extra column in the scope.

2. Manually modify the Sync Framework Database Objects

In Part 1 of this post and in the Provisioning blog entry, you’ve seen what database objects Sync Framework creates when provisioning. So it’s easy to go straight to these objects and modify them directly.

This may seem to be an easy task when you only have a handful of scopes and synchronization parties to deal with and when the scope modification is infrequent. However, this becomes a tedious and error-prone task when you have to deal with many scopes, sync partners and when schema changes occur frequently (IMHO, if the schema changes frequently, that’s an even bigger problem  whether your using Sync Framework or not).

On the other hand, you get to retain the existing sync knowledge for the data that has been synchronized already.

Now let’s look at the various scenarios requiring a scope modification and the corresponding objects to modify:

1. Add/Remove column

(Assuming the column is not a PK or a FilterColumn)

  • modify select_changes SP
  • modify select_row SP
  • modify Insert/Update SP
  • drop Bulk Insert/Update /Delete SP (they reference the bulktype and we can’t re-create the bulk type without dropping them)
  • drop and recreate BulkType (there is no ALTER TYPE in SQL Server)
  • recreate Bulk Insert/Update /Delete SP
  • modify config_data column in the scope_config table

2. Add/Change  FilterColumn/FilterClause

(assuming the FilterColumn is not a PK and was added using #1 above or is a column that is already part of the scope)

  • modify tracking_table (filter columns are added in the tracking table)
  • modify insert/update triggers
  • modify select_changes SP
  • modify config_data column in the scope_config table
  • if the scope is based on a filter template, modify the parameter_data column of the scope_parameters table

3. Add Table

  • add tracking table
  • populate tracking table
  • add insert/update/delete SP
  • add bulktype
  • add bulk insert/update/delete SP
  • add select_changes SP
  • add insert/update/delete metadata SP
  • add select_row SP
  • modify the config_data column in the scope_config table

4. Remove Table

I’ll cover this in a separate post as it requires tinkering with the sync knowledge to remove the knowledge around the table that’s removed.

3. Create DDL Triggers on the tables included in a sync scope

This one entails creating  DDL triggers on the tables that are part of a scope and automatically modifying the Sync Framework database objects when the table schema is altered.

Have a look at this thread by David Chanussot on the Sync Framework forums and see how it’s done : Easy solution to adapt scopes to schema changes… You’ll find the contributor’s email in the thread itself if you want a copy of the scripts.

That’s it for now and I’ll reserve the rest of the workarounds in another blog post.

Cheers,

JuneT.

Advertisements

20 comments

  1. […] Modifying Sync Framework Scope Definition – Part 2 – Workarounds […]

  2. Hello , I am trying to Change FilterColumn/FilterClause but my FilterColumn is PrimaryKey is there any solution for this ?

    Thanks

    1. can you be more specific with your question? you want to change from a PK filter column to a non PK one? or vice versa? are you using scope templates?

  3. Sabi Reuven · · Reply

    As I understand if I am using templates ,I will have to do the following:

    serverProv.PopulateFromTemplate(scopeName, scopeTemplate); // Create Scope from template
    serverProv.Tables[“SomTable”].FilterParameters[“@filter”].Value = “Value”; // Set Filter Value
    serverProv.Apply(); // Scope Creation on the database

    Afterwards in order to change the filter i can change the XML stored in the database containing data filter with new filter and the data will be sync based on new filter.

    The Question is what if my FilterColumn is Primary key in this table will it be a problem.

    P.S I have to say your website is better by far then msdn documentation on sync framework.
    Thumbs up Great work

    Thanks

    1. i dont see a problem if the new filter column is based on the PK since the PK is already in the tracking table itself regardless of whether you have a filter or not.

      please note though that Sync framework doesnt support “partition realignment” or rows going “in-scope” or “out-of-scope”.

      for example, assuming filterclause is State=”WA”, when your client syncs, they get rows with State=”WA”. now if you changed the rows on the server so that their State=”NY”, the clients who has downloaded the rows when State=”WA” will never get updated to reflect the new state.

      1. What is the solution for your example ?
        I am trying to achieve exactly the same scenario.
        My FilterClause = string.Format(“[side].[Id] IN (@filter)”
        In the beginning @filter = “WA” When Client synced
        Now @filter = “WA,NY” When Client is syncing i don’t receive any data.

        Is there any solution for that or i have to make scope for each State “WA” and “NY”

        Thanks in advance

      2. creating separate scopes is one of the simplier workarounds.

  4. This is an excellent resource for this topic. I have been going the de-provision/re-provision route … but I think I will take a stab at manually modifying the objects on my next upgrade.

    I think I understand what is required to update the provisioning on the SQL server … assuming I correctly make changes to the SQL server … BUT how do I propagate those changes to my SqlCe remote clients? How/when do the data and tracking tables get created in the SDF?

    1. Sql Ce gets provisioned via the provisioning API as well. unfortunately, the Sql Ce provisioning api doesnt have the same Scripting as the Sql provisioning API. also, there are differences in the objects created for Sql Ce provisioning.

  5. Hi June,

    Thank you very much for providing such a nice tutorial.

    I request you to understand the problem we are facing with Sync for which I need your help and guide.

    What we have:
    1) 50 tablesto be sync-ed, divided into 6 common scopes and 8 template based scopes(parameterized based on CLIENTID column of CLIENT table).
    2) Sync is to be done from different PCs to SQL Azure
    3) From C# WPF App, we select a client(CLIENTID), run sync set-up(provisioning) and then start sync process using method.

    Issue we face very often:
    1) Few clients are already sync-ed, on a n-th client, due to network connectivity issue / timeout issue, app needs to be closed. When we run provision on that particular client again, sometime we get exception that Selectchanges or bulkInsert is missing.

    2) If the above happens, we destoreProvision, then re-provision. That leaves us with client and server having the same set of data. If we proceed with sync then, it takes 7 hours to complete erroring out with lots of INSERT CONFLICTS.

    So, when in the mid of syncing, network connectivity fails, COMMIT happens with scopes that ran successfully? Or, few scopes that ran well leave us with partial syncing?
    Is there any way of getting rid of numbers of Insert onflicts except de-provision and re-provision?

    If you can help us with this on any terms, we would remain grateful to you. Please email if you can.

    Thanks
    Raman Basu

    1. Raman,

      i replied to your email separately.

      cheers,

      junet

  6. Hi Junet,

    at point 1, you make the following restriction “(Assuming the column is not a PK or a FilterColumn)”. What if I am trying to add a primary key column? Is there a workaround for that?

    1. Not sure what you mean you want to add a PK. Provisioning requires a PK. If you meant changing the PK or adding a column to the existing PK (compound key), you can still modify the scope config, but you have to alter the tracking table to add the new column and populate it as well. PK columns are always part of the tracking table. If you’re replacing the PK, you might just consider reprovisioning.

  7. Liew Wai Foong · · Reply

    Hi JuneT,

    Thanks for your great and useful article.

    Need opinion from you, if we able to make change to server side DB (hub) following the section “Manually modify the Sync Framework Database Objects”, how should we propagate the same changes to clients (member)? We may have few hundred clients.

    Thanks in advance.

    Wai Foong

    1. the scope changes will need to be propagated manually. there are workarounds to versioning scopes, but if you can deprovision/reprovision, just go that route. it becomes tricky for example if the newly added column does not allow nulls and you have older scopes that doesnt have that column. even older scopes will fail because of the null constraints.

  8. In a client server setting with the Dynamic Schema, If I make the manual changes to the server database schema/sync scope how does the client figure out to modify itself? Or does the same Sql Scripts need to be run on the client to for example add a column and change all the Sync objects and scope description? Prior to sync?

  9. Hi JuneT,

    Thanks for such great articles on sync services.

    I m writing to you to ask a question on the problem i am facing. It is regarding removing a table from the existing scope and re-provisioning. In the present article you say you will be writing a separate post for it, have you written it already? If so please give the link to it since my sync is failing after i try to remove a table from the scope and trying to make it work.

    Thanks,
    Megha

  10. Sreekumar · · Reply

    Is it possible to provide filters for multiple tables? Because in my sync scenario, my main dB consists of a main Company and the remote db is it branches.Employee details for all branches are consolidated in the main company. Tables are COMPANYBRANCH,EMPLOYEE,ATTENDANCE. Companybranch $ attendance are relational table(PK-FK) and Employee is a master table and it is related to attendance(PK-FK).I have remote databases for singapore & dubai branches.Both remote Dbs are keeping the same structure olike in Main database.
    kindly provide some ideas to achieve the sync data in above scenario.
    Thanks & regards,
    Sreekumar

  11. Jagadish · · Reply

    I JuneT, Great work man well I’ve same ? like (Raman Basu · June 27, 2012 – 2:42 am) asked you on the same page. Another ? is, can we handle audit history like INSERT/ UPDATE/ DELETE. changes against table by user.

    Please help me… Thanks,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: