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.
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…
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
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:
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.