Introduction to Provisioning
The first step in configuring database synchronization in Sync Framework using the collaboration/peer-to-peer providers (SqlSyncProvider/SqlCeSyncProvider) is to define a scope. A scope is basically a unit or a grouping which defines what is going to be synchronized as a set. This is where you specify/list down which columns/tables to synchronize and what filters to apply when synchronizing. When you synchronize the scope, only the columns/tables included in the scope definition and the corresponding rows satisfying the filter are synchronized.
When creating the scope (referred to as provisioning), Sync Fx creates objects in the database to enable change-tracking (keeping track of the inserts/updates/deletes in the tables) and metadata management. These objects consists of triggers on the tables being synched, stored procedures for updating metadata on what has changed and what has been synched, stored procedures for selecting and applying changes, tables for storing scope information/configuration and a tracking table for each table being synched to keep track of inserts/updates/deletes on that table.
1. Provisioning without a filter
To provision a new scope, we can programmatically retrieve the columns of the tables to be synched, or specify a subset of columns to include from an existing table or build the structure of the tables programmatically.
A. Including all columns from a table
B. Specifying specific columns to include from a table
C. Defining the table structure
After provisioning, you should see additional database objects created by the Sync Fx provisioning API:
- schema_version contains the version information
- scope_info table contains the list of scopes provisioned in the database and the sync knowledge
- scope_config contains the configuration parameters for each scope. There is config_data XML column in the scope_config table that contains the scope configuration including the names of the objects associated for each object created by Sync Fx.
- ???_tracking tables contains the change tracking information for the rows in the tables that’s part of a sync scope
For each table that is contain in a scope, Sync Fx also creates Insert, Update and Delete triggers. This triggers are responsible for keeping the corresponding table’s tracking table information up to date for every insert, update and delete operation performed on the table.
Sync Fx also creates stored procedures to enumerate (selectchanges sp) and apply changes (insert/update/delete sp) to the table.
If database server is SQL 2008 or SQL Azure, Sync Fx creates bulk procedures that accepts a table-valued parameter that contains all changes to be applied to the table. This significantly reduces the round trip required to apply each row to the target table. Otherwise, Sync Fx sends and applies the changes to the table one row at a time. The stored procedures with “metadata” on their name is responsible for updating the synchronization metadata.
Below is a user-defined-data type created by Sync Fx to support the bulk procedures table-valued parameter.
2. Provisioning with Filters
Sync Fx supports the ability to filter data exchange in the synchronization process. There are basically two types of filters in Sync Fx: Static Filters and Parameter-based Filters.
Using Static Filters, the filter value in the filter condition is hardcoded in the stored procedure that enumerates changes (selectchanges sp).
Here’s a quick example of defining a scope with a static filter. Here we specify the name of the column we want to filter on and specify the filter condition which is basically a SQL WHERE clause minus the WHERE keyword. Note the table alias [side]. The table alias [side] refers to the tracking table and [base] is used to refer to the base table.
Note that we have to set SetCreateProceduresForAdditionalScopeDefault to specify that we want Sync Fx to create a new stored procedure for selecting changes.
Once provisioned, you will notice that the tracking table is updated to include specified filter column as well. Every filter columns specified will be added in the tracking table so they can be referred to using the [side] alias.
Since we set SetCreateProceduresForAdditionalScopeDefault to create a new select changes stored procedure, it creates a new one selectchanges sp as shown below. Every additional selectchanges sp created for a scope will have a GUID suffix. (Sync Fx doesn’t allow setting explicitly the names for the objects it creates.)
Sync Fx will also create a new entry in the scope_info and scope_config tables for the new scope. If you looked the config_data column of a filtered scope, you will find the filter column specified is also stored as part of the scope configuration. You will also note that the config_data is configured to use the newly created selectchanges sp with a GUID suffix.
If you open up the new selectchanges sp, you will find that the filter value is hardcoded in the stored procedure.
To create another scope for another filter value, we basically do the same steps over and over again. For each scope that we create, there will be one new entry in the scope_info and scope_config tables and a new selectchanges sp.
Using parameter-based filters is a two step process: Defining the filter/scope template and creating a scope based on a template.
Creating a template is basically similar to creating a scope and the only major difference is that instead of specifying the actual filter value in the filter clause, you specify a filter parameter instead. Likewise, a template cannot be used for synchronization.
Here’s an example of a filter template similar to the static filter we defined earlier. Notice that instead of specifying the actual values we want to filter on, we instead defined a parameter (a SQL parameter object to be exact).
Since we set SetCreateProceduresForAdditionalScopeDefault as well, we get a new selectchanges sp for this template.
When we examine the new selectchanges sp, you will notice that the filter clause is now expecting a parameter instead of a hard coded filter value.
Likewise, Sync Fx creates two additional tables:
- scope_templates which contains a list of templates defined
- scope_parameters which will contain values for the filter parameter for each scope we defined based on the template
If we examine the config_data column for the new template in the scope_config table, you will notice that its flagged as a template (IsTemplate=”true”). You will also find that the filter clause now refers to a filter parameter instead of a static value and that the selectchanges stored procedure is now configured to use the newly created selectchanges sp with a GUID suffix.
Filter templates as it is cannot be used to synchronize. They only serve as base templates from which we can create new scopes that will inherit the configuration of the template and specify a specific filter value we want to pass to the filter parameter in the template.
To create a scope based on a template, we basically just make a call to PopulateFromTemplate specifying the name of the scope we want to create and the name of the template from which we want to inherit the scope configuration. We then specify the filter value that we want to pass for the filter parameter.
In the example below, we want only orders where OriginState=WA, so we pass the value “WA” to the filter parameter named “@originState”.
Once we provision the new scope based on the new template, Sync Fx will create a new entry in the scope_info table for the new scope. However, instead of creating a new entry in the scope_config table, the new scope entry will instead be pointing to the template’s entry in the scope_config table. In effect, the new scope is re-using the scope configuration of the template.
Likewise, Sync Fx creates an entry in the scope_parameters table containing the filter value specified for the scope. Below is the entry for filter value of the newly created scope.
When synchronization is started, Sync Fx pick’s up scope’s configuration from the template’s configuration and applies the corresponding value in the scope_parameters table for the scope and then pass that value to the corresponding selectchanges sp configured in the template.
In effect, you can create multiple scopes for different filter values without creating new selectchanges sp or without repeatedly specifying the same scope configuration over and over again during provisioning.
The problem with versions of Sync Fx prior to v2.1 is that there is nothing in the API that allows you to modify or drop these objects. With v2.1, Sync Fx now allows you to delete/remove these objects from the database (referred to as deprovisioning). (Note: v2.1 still does not support modifying scope definitions, e.g., you created a sync scope for a table with 5 columns and now you want to add or remove two columns to the scope).
Sync Fx v2.1 supports the following deprovisioning scenarios thru the APIs under SqlSyncScopeDeprovisioning and SqlCeSyncScopeDeprovisioning:
1. Deprovision a scope
To remove a scope, you make call to SqlSyncScopeDeprovisioning.DeprovisionScope() or SqlCeSyncScopeDeprovisioning.DeprovisionScope() passing the name of the scope you want to deprovision.
Note that when a Sync Fx metadata object (e.g., tracking table for each table being synched) is shared by more than one scope (you have a table that is included in more than one scope), the object is not deprovisioned while a scope still references it.
For example, you created two scopes named: SalesAsia and SalesEurope, with both scopes referencing the Sales table. Your Sales table will have a corresponding tracking table shared by both scopes. When you deprovision SalesAsia, the shared tracking table will not be removed since its still being used by SalesEurope. The shared tracking table will only be deprovisioned when the last scope referencing it is deprovisioned as well, in this case, when SalesEurope is deprovisioned.
2. Deprovision a filter template
Deprovisioning a filter template removes the template and all associated scopes created using the template.
To remove a filter template including all associated scopes created from the template, you make a call to SqlSyncScopeDeprovisioning.DeprovisionTemplate() or SqlCeSyncScopeDeprovisioning.DeprovisionTemplate() passing the template name .
3. Deprovision store.
Deprovisioning a store removes all Sync Fx created objects in the database
To remove all objects created by Sync Fx provisioning from the database, you simply make a call to SqlSyncScopeDeprovisioning.DeprovisionStore() or SqlCeSyncScopeDeprovisioning.DeprovisionStore() if your using SQL Compact.
That’s all for now. I hope this post helps clarify what goes on during provisioning.
As always, I’d love to hear feedback.