Modifying Sync Framework Scope Definition – Part 1 – Introduction


Introduction

Changing Sync Framework scope definition is probably one of the most frequently asked question in the Sync Framework forums.

Changes in scope definition may be due to schema changes such as addition or removal of columns, changes in filter columns or filter clause, or maybe even addition or removal of tables in the scope.

Unfortunately, modifying scope definition is not supported by Sync Framework out-of-the-box (.(which makes this probably  rank as among the most requested feature).

I don’t think i can cover the workarounds in a single blog post so am splitting this into multiple post instead.

Before we dive into some of the workarounds on how to effect scope changes, let’s review first what gets provisioned when we define a new scope. You might want to check my prior blog post on Sync Framework Provisioning to familiarize yourself with the process and the corresponding objects created by Sync Framework .

So let’s review the objects created by Sync Framework during provisioning.

Sync Framework Objects

1. _tracking table

For every table that’s added as part of a scope, Sync Framework creates a table with a _tracking suffix. The tracking table contains change tracking metadata for inserts/updates/deletes made against the table. There is only one tracking table for every table that is part of a scope. The Primary Keys of the table is included in the tracking table and any other columns that is defined as a Filter column for that table in the scope.

2. Insert/Update/Delete Triggers

To update the tracking table mentioned above, Sync Framework creates Insert, Update and Delete triggers for each table that is part of a scope. This triggers get fired every time an Insert, Update or Delete is made against the table. Please note that  some database operations such as bulk inserts or truncate table don’t fire triggers and as such, will not update the tracking tables. There is only one Sync Fx trigger each for Insert, Update and Delete against the table that is part of a scope.

3. _selectchanges Stored Procedure

Each scope definition has its each own _selectchanges stored procedure which selects the insert/update/delete changes in the table that is part of a scope. Since multiple scopes can be defined against a table, you can have multiple selectchanges stored procedure against the table by setting SetCreateProceduresForAdditionalScopeDefault for the second or subsequent scopes. Subsequent scopes will have GUID suffix to guarantee each selectchanges stored procedure is unique.

Please note that while you can defined multiple scopes against the same table, the columns definition for all scopes must be the same as all scopes will actually share the same Insert/Update/Delete stored procedures as described below.

4.  Insert/Update/Delete Stored procedures

Sync Framework creates stored procedures to apply the changes to a sync partner. Sync Framework can apply the changes one row at a time (if you have 100 inserts, it will fire 100 insert statements) or apply the changes by bulk using Table-Valued-Parameters (TVP). If the version of SQL Server supports bulk updates (2008 or Azure), Sync Framework will by default use bulk change application. This can also be controlled by setting SetUseBulkProceduresDefault. If bulk procedures are created, there will be a total of six stored procedures created. Three stored procedures using TVPs applying the changes in bulk and another three stored procedure applying changes one row at a time.

5. _BulkType

When bulk procedures are created, Sync Framework also creates the corresponding User-Defined-Table type for each table that is part of a scope. There is only one UDT for every table that is part of a scope. This UDT is passed as a parameter to the bulk procedures. This UDT will have the all the columns included as part of the table that is part of a scope plus additional columns for Sync Framework metadata.

6. Metadata Insert/Update/Delete stored procedures

These stored procedures are used to update the _tracking tables when applying changes.

7. _selectrow Stored Procedure

This stored procedure is used to retrieve row information when a conflict occurs. There is only one _selectrow stored procedure for every table that is part of a scope.

8 .schema_info table

Contains Sync Framework version information.

9. scope_info table

Contains all scopes defined in the database, their corresponding sync knowledge and a pointer to the corresponding sync configuration/definition

10. scope_config table

Contains scope definition/configuration for each scope. This table contains an XML column that contains adapter information for each table in the scope such as the name of the stored procedures, triggers, tracking table, columns, etc…

image

11. scope_templates table

Contains scope templates

12. scope_parameters table

Contains values for the filter parameters of each scope  defined based on a scope template

image

Now that we know what objects created by Sync Framework during scope provisioning, let’s talk about what needs to be changed when we want to alter the scope definition and the corresponding complexities around each change:

Scope Modifications

1. Adding a new column to a table in a scope definition

if a column needs to be added, the _selectchanges stored procedure needs to be altered to include the new column. Since the selectchanges has a new column, the Insert/Update stored procedures will now have to be updated as well to insert/update the new column.

Likewise, if the column is added as a FilterColumn, the same column should be added in the tracking table. Since the tracking table has a new column, the Insert/Update triggers will also need to be altered to include the new column. column. If the tracking table schema has been updated, there is now a need as well.

The scope_config XML entry should also be updated to reflect the new column in the Adapter definition

2. Removing a column from a table in a scope definition.

just a reverse of the actions above, removing all references to the column in all objects that it appears.

3. Adding a new table

A whole new set of Sync Framework objects will need to be created such as: tracking table, triggers, bulktype, insert/update/delete stored procedures, insert/update/delete metadata stored procedure, selectrow store procedure and selectchanges stored procedure.

Since the tracking table is new, it now needs to be populated as well with metadata based on existing rows in the newly added table.

Likewise, the scope_config entry will also need to be modified to include adapter information for the newly added table.

4. Removing a table

Just a reverse of the actions above but the added the complexity as well of clearing the sync knowledge stored in the scope_info table. Not an easy thing to do since the sync knowledge for each table is contained in the same column for all other tables in the scope.

5. Changing the filter column or filter clause

Changing a filter column may require a change in the tracking table and the corresponding triggers, selectchanges and scope_config entry.

This is further complicated if a scoped is based on a scope/filter template.

The next post will cover some workarounds to the scope modification limitation of Sync Framework.

Cheers,

JuneT

Advertisements

12 comments

  1. Hi,

    I’m trying to do the example.
    I have added one column, modify the scope_config, modify the insert, update, selectchanges and selectrow, in Remote and Local Database.

    But it only update changes in one direction, from remote to local.

    It is necessary to change also SP as BulkInsert/BulkUpdate in remote ?? and also BulkTypes?

    I’m misssing something.

    Thank you!!

    1. the scope definitions on both side should be the same. if you’re using SQL 2008, it will default to using the bulk procedures, so you will have to update them as well. but if you’re getting updates one direction, the config should be working already. check the ApplyChangesFailed just in case there is an error/conflict occuring.

      1. I have checked ApplyChangeFailed but there are not conflicts.

        For changing the bulk procedures, then, It is necessary tho change the bulk types, but there are not “alter type”, so it is necessary drop and create the type. But if you have objects that depends on BulkType you can’t drop it, so, it is necessary to drop bulk procedures, drop bulk type, create type and create Bulk procedures.

        I think there are many steps with this solution… for doing in all the remote machines.

        Is it really useful to have SetUseBulkProceduresDefault to true? What is the advantage? will it work faster with Bulk?

        Thanks!

      2. the bulk procedures improves performance compared to the individually sending insert/update/delete for each row. assuming you have 50 inserts, using the row by row SPs, Sync Fx will fire 50 insert statements compared to one using bulk procedures.

        yes, that’s the catch, you cant alter a type, so you have to drop it and recreate it. you can easily generate scripts for the bulk procedures and the type, drop the type and modify it, then recreate the procedures.

        if you dont want to use bulk procedures, you can set it during provisioning.

        as for your updates not uploading, can check if either using SQL Profiler or subscribing to the changes selected event if you’re client is actually able to select the changes?

  2. […] Idle Thoughts ← Modifying Sync Framework Scope Definition – Part 1 – Introduction […]

  3. […] Modifying Sync Framework Scope Definition – Part 1 – Introduction […]

  4. Hi JuneT, thanks for a wonderful post on the fundamentals of SYNC framework provisioning. You are right when you say that Modifying sync scope is most wanted feature! It’s definitely asked a lot of time in forums and most times in the format – “why do new tables not get synchronized”. your article throws light on this fundamental issue. Thanks

  5. […] the synchronize can’t seem to detect that change and I received an exception. I found this article, which addresses sync scope schema changes. The article states that this is one of the most […]

  6. HI

    I NEED TO CHANGE A PRIMARY KEY OF A TABLE THAT USES BY SYNC. HOW DO I DO THIS. THE NEW PK IS A ANOTHER COLUMN IN SAME TABLE.
    PLEASE HELP ME.

    1. you’ll be better off deprovisioning and reprovisioning. changing the pk effectively invalidates the existing sync knowledge.
      if the current pk is unique and you’re not removing it, you can continue to use it.

      1. Many thanks for the reply.

        My current pk is still unique. but it is a auto increment one. the sync updates my pass records because the child table is a new one and its pk starting from 1. but my older table has more records and it contains the child table running numbers. when sync happens, it automatically update my base table past record. unfortunately i cant change this base column because I’ve used it in my code behinds. what should i do? my parent table used by a live system and contains thousands of records.

  7. i have really strange problem. i have been trying to synchronize 2 tables but they are localized in same db. it is easy if you have 2 tables and 2 database (ClentDb and ServerDb). i dont have clientDb. but i have 2 tables. if you have 2 tables(they should be compared) in same database,Sync is not working in the same db. how can i solve this problem?

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: