Sync Framework Provisioning

September 2, 2010 · by JuneT · in Sync Framework · 114 Comments

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

Code Snippet

// define a new scope named OrdersScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("OrdersScope");
 
// get the description of the Orders table 
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", serverConn);
 
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
 
// create a server scope provisioning object based on the OrdersScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
 
// start the provisioning process
serverProvision.Apply();

B. Specifying specific columns to include from a table

Code Snippet

// define a new scope named OrdersScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("OrdersScope");
 
// Specify the list of columns to include from the Orders Table
Collection<string> columnsToInclude = new Collection<string>();
columnsToInclude.Add("OrderId");
columnsToInclude.Add("ProductId");
columnsToInclude.Add("Quantity");
columnsToInclude.Add("OriginState");
 
// get the description of the Orders table with the specified columns
DbSyncTableDescription tableDesc =
    SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", columnsToInclude, serverConn);
 
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
 
// create a server scope provisioning object based on the OrdersScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
 
// start the provisioning process
serverProvision.Apply();

C. Defining the table structure

Code Snippet

// define a new scope named OrdersScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("OrdersScope");
 
// create a new table description
DbSyncTableDescription newTableDescription = new DbSyncTableDescription("Orders");
 
// define the columns
DbSyncColumnDescription newTableOrderIdCol = new DbSyncColumnDescription();
DbSyncColumnDescription newTableProductIdCol = new DbSyncColumnDescription();
DbSyncColumnDescription newTableQuantityCol = new DbSyncColumnDescription();
DbSyncColumnDescription newTableOriginStateCol = new DbSyncColumnDescription();
 
// specify column properties
newTableOrderIdCol.UnquotedName = "OrderId";
newTableOrderIdCol.Type = "int";
newTableOrderIdCol.IsPrimaryKey = true;
 
newTableProductIdCol.UnquotedName = "ProductId";
newTableProductIdCol.Type = "int";
newTableProductIdCol.IsPrimaryKey = false;
 
newTableQuantityCol.UnquotedName = "Quantity";
newTableQuantityCol.Type = "int";
newTableQuantityCol.IsPrimaryKey = false;
 
newTableOriginStateCol.UnquotedName = "OriginState";
newTableOriginStateCol.Type = "nvarchar";
newTableOriginStateCol.Size = "2";
newTableOriginStateCol.IsPrimaryKey = false;
 
//add the columns to the table description
newTableDescription.Columns.Add(newTableOrderIdCol);
newTableDescription.Columns.Add(newTableProductIdCol);
newTableDescription.Columns.Add(newTableQuantityCol);
newTableDescription.Columns.Add(newTableOriginStateCol);
 
// add the table description to the sync scope definition
scopeDesc.Tables.Add(newTableDescription);
 
// create a server scope provisioning object based on the OrdersScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
 
// start the provisioning process
serverProvision.Apply();

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.

Code Snippet

<SqlSyncProviderScopeConfiguration 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot; 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema&quot; 
  IsTemplate="false">
  <Adapter Name="[Orders]" GlobalName="[Orders]" 
           TrackingTable="[Orders_tracking]" 
           SelChngProc="[Orders_selectchanges]" 
           SelRowProc="[Orders_selectrow]" 
           InsProc="[Orders_insert]" 
           UpdProc="[Orders_update]" 
           DelProc="[Orders_delete]" 
           InsMetaProc="[Orders_insertmetadata]" 
           UpdMetaProc="[Orders_updatemetadata]" 
           DelMetaProc="[Orders_deletemetadata]" 
           BulkTableType="[Orders_BulkType]" 
           BulkInsProc="[Orders_bulkinsert]" 
           BulkUpdProc="[Orders_bulkupdate]" 
           BulkDelProc="[Orders_bulkdelete]" 
           InsTrig="[Orders_insert_trigger]" 
           UpdTrig="[Orders_update_trigger]" 
           DelTrig="[Orders_delete_trigger]">
    <Col name="OrderId" type="int" param="@P_1" pk="true" />
    <Col name="ProductId" type="int" param="@P_2" />
    <Col name="Quantity" type="int" param="@P_3" />
    <Col name="OriginState" type="nvarchar" size="2" param="@P_4" />
  </Adapter>
</SqlSyncProviderScopeConfiguration>

???_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.

Static 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.

Code Snippet

// define the OrdersScope-Filter-State-to-NC parameterized scope 
// this scope filers records in the Orders table with OriginState set to NC
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("OrdersScope-Filter-State-to-NC");
 
// gets the description of the Orders table and add it to the scope
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", serverConn);
scopeDesc.Tables.Add(tableDesc);
 
// create server provisioning object
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
 
// set the filter column on the Orders table to OriginState
serverProvision.Tables["Orders"].AddFilterColumn("OriginState");
 
// set the filter value to NC
serverProvision.Tables["Orders"].FilterClause = "[side].[OriginState] = ‘NC’";
 
// create a new select changes stored proc for this scope
serverProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
 
// start the provisioning process
serverProvision.Apply();

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.

Code Snippet

<SqlSyncProviderScopeConfiguration 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot; 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema&quot; 
  IsTemplate="false">
  <Adapter Name="[Orders]" 
           GlobalName="[Orders]" 
           TrackingTable="[Orders_tracking]" 
           SelChngProc="[Orders_selectchanges_6660a2d5-88f7-4eeb-a05b-50539fddc1ac]" 
           SelRowProc="[Orders_selectrow]" 
           InsProc="[Orders_insert]" 
           UpdProc="[Orders_update]" 
           DelProc="[Orders_delete]" 
           InsMetaProc="[Orders_insertmetadata]" 
           UpdMetaProc="[Orders_updatemetadata]" 
           DelMetaProc="[Orders_deletemetadata]" 
           BulkTableType="[Orders_BulkType]" 
           BulkInsProc="[Orders_bulkinsert]" 
           BulkUpdProc="[Orders_bulkupdate]" 
           BulkDelProc="[Orders_bulkdelete]" 
           InsTrig="[Orders_insert_trigger]" 
           UpdTrig="[Orders_update_trigger]" 
           DelTrig="[Orders_delete_trigger]">
    <Col name="OrderId" type="int" param="@P_1" pk="true" />
    <Col name="ProductId" type="int" param="@P_2" />
    <Col name="Quantity" type="int" param="@P_3" />
    <Col name="OriginState" type="nvarchar" size="2" param="@P_4" />
    <FilterClause>[side].[OriginState] = ‘NC’</FilterClause>
    <FilterCol>OriginState</FilterCol>
  </Adapter>
</SqlSyncProviderScopeConfiguration>

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.

Parameter-based Filters

Using parameter-based filters is a two step process: Defining the filter/scope template and creating a scope based on a template.

Filter Templates

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).

Code Snippet

// Create a template named "Orders_OriginState_Filter_template"
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("Orders_OriginState_Filter_template");
 
// Set a friendly description of the template.
scopeDesc.UserComment = "Filter template for Orders.OriginState";
 
// Definition for tables.
DbSyncTableDescription ordersDescription =
    SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", peer1);
scopeDesc.Tables.Add(ordersDescription);
 
// Create a provisioning object for "Orders_OriginState_Filter_template" that can be used to create a template
// from which filtered synchronization scopes can be created. 
SqlSyncScopeProvisioning serverTemplate = new SqlSyncScopeProvisioning(peer1, scopeDesc,SqlSyncScopeProvisioningType.Template);
            
// Specify the column in the Orders table to use for filtering data, 
// and the filtering clause to use against the tracking table.
// "[side]" is an alias for the tracking table.
// The OriginState column that defines the filter is set up as a parameter in this template.
// An actual OriginState will be specified when the synchronization scope is created.
serverTemplate.Tables["Orders"].AddFilterColumn("OriginState");
serverTemplate.Tables["Orders"].FilterClause = "[side].[OriginState] = @originState";
SqlParameter param = new SqlParameter("@originState", SqlDbType.NVarChar, 2);
serverTemplate.Tables["Orders"].FilterParameters.Add(param);
 
// create a new select changes stored proc for this scope
serverTemplate.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
 
// Create the "Orders_OriginState_Filter_template" template in the database.
serverTemplate.Apply();

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.

Code Snippet

<SqlSyncProviderScopeConfiguration 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot; 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema&quot; 
  IsTemplate="true">
  <Adapter Name="[Orders]" 
           GlobalName="[Orders]" 
           TrackingTable="[Orders_tracking]" 
           SelChngProc="[Orders_selectchanges_d5e09135-c7aa-4c3b-83f1-46ab8be9517a]" 
           SelRowProc="[Orders_selectrow]" 
           InsProc="[Orders_insert]" 
           UpdProc="[Orders_update]" 
           DelProc="[Orders_delete]" 
           InsMetaProc="[Orders_insertmetadata]" 
           UpdMetaProc="[Orders_updatemetadata]" 
           DelMetaProc="[Orders_deletemetadata]" 
           BulkTableType="[Orders_BulkType]" 
           BulkInsProc="[Orders_bulkinsert]" 
           BulkUpdProc="[Orders_bulkupdate]" 
           BulkDelProc="[Orders_bulkdelete]" 
           InsTrig="[Orders_insert_trigger]" 
           UpdTrig="[Orders_update_trigger]" 
           DelTrig="[Orders_delete_trigger]">
    <Col name="OrderId" type="int" param="@P_1" pk="true" />
    <Col name="ProductId" type="int" param="@P_2" />
    <Col name="Quantity" type="int" param="@P_3" />
    <Col name="OriginState" type="nvarchar" size="2" param="@P_4" />
    <FilterParam name="@originState" />
    <FilterClause>[side].[OriginState] = @originState</FilterClause>
    <FilterCol>OriginState</FilterCol>
  </Adapter>
</SqlSyncProviderScopeConfiguration>

Filtered Scopes

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”.

Code Snippet

// Create a synchronization scope for OriginState=WA.
SqlSyncScopeProvisioning serverProvWA = new SqlSyncScopeProvisioning(serverConn);
            
// populate the scope description using the template
serverProvWA.PopulateFromTemplate("Orders-OriginState-WA", "Orders_OriginState_Filter_template");
            
// specify the value we want to pass in the filter parameter, in this case we want only orders from WA
serverProvWA.Tables["Orders"].FilterParameters["@originState"].Value = "WA";
 
// Set a friendly description of the template.
serverProvWA.UserComment = "Orders data includes only WA.";
 
// Create the new filtered scope in the database.
serverProvWA.Apply();

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.

Code Snippet

<SqlSyncProviderScopeParameters 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot; 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema&quot;>
  <AdapterParams name="[Orders]">
    <FilterParam name="@originState">
      <Value xsi:type="xsd:string">WA</Value>
    </FilterParam>
  </AdapterParams>
</SqlSyncProviderScopeParameters>

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.

Deprovisioning

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.

Code Snippet

// Remove the scope from the database

SqlSyncScopeDeprovisioning scopeDeprovisioning = new SqlSyncScopeDeprovisioning(serverConn);

// Remove the scope.

scopeDeprovisioning.DeprovisionScope("OrdersScope");

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 .

Code Snippet

// Remove the "Orders_OriginState_Filter_template" template from the server database.

// This also removes all of the scopes that depend on the template.

SqlSyncScopeDeprovisioning templateDeprovisioning = new SqlSyncScopeDeprovisioning(serverConn);

// Remove the scope.

templateDeprovisioning.DeprovisionTemplate("Orders_OriginState_Filter_template");

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.

Code Snippet

// Remove all scopes from the database.

SqlSyncScopeDeprovisioning storeDeprovisioning = new SqlSyncScopeDeprovisioning(serverConn);

storeDeprovisioning.DeprovisionStore();

That’s all for now. I hope this post helps clarify what goes on during provisioning.

As always, I’d love to hear feedback.

Cheers.

114 comments

Israel · September 2, 2010 - 10:52 pm · Reply→

Thank you very much June, great entry and walkthrough to show the new improvements made in ver. 2.1Please keep the good work and enlightening!Regards.Great entry.
1. Leonardo Lee · January 7, 2011 - 2:13 pm · Reply→
  
  Hi,
  
  How can I provision FK constraints?
  I am trying to provision the FK constraints, but these are not applied to Express clients.
  Data sync works well, but FKs are not copied..
  Would you like to share your code for this?
  Best Regards
ims · February 10, 2011 - 9:49 am · Reply→

Hi,
It is really a awesome article!,One question on the dynamic parameter based filters,In the above example you are executing this filter to fetch the records based in “WA”,Is this value changeable or dynamically provided by a calling program Or Once I start syncing within the “WA” scope I can’t sync the other records by providing different values for the other states like “TX”,”AR” etc.

Thanks for a wonderful article ,
Irfan
1. jtabadero · February 10, 2011 - 9:43 pm · Reply→
  
  Hi Irfan,
  
  the filter value is included in the scope definition. the filter template is just so you can re-use the same scope definition with different filter values. but once you provision a scope using a template, the filter value you specified is saved with the scope definition.
Modifying Sync Framework Scope Definition – Part 2 – Workarounds « JuneT's idle thoughts · March 24, 2011 - 12:06 am · Reply→

[…] 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 […]
Kay-Christian Wessel · March 31, 2011 - 4:52 am · Reply→

Superb article! Makes it much easier to understand and use.

Is it possible to have more than one set of procedures? (1 set for each of the scopetemplates?)

Kay
1. jtabadero · April 1, 2011 - 6:05 pm · Reply→
  
  try setting different ObjectPrefix for each scope during provisioning
Modifying Sync Framework Scope Definition – Part 1 – Introduction « JuneT's idle mind = idle thoughts · April 3, 2011 - 1:08 pm · Reply→

[…] 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 […]
RickG · April 21, 2011 - 3:38 am · Reply→

Nice article explaining the “magic” with Sync Framework and Provisioning. Question concerning the use of Templates and scopes. In your example, User#1 wants to filter on ‘WA’, make some modifications and sync. Should we drop the scope when the user is finished?

I ask because our users will need to retrieve a subset of data, make modifications, sync, and then exit. I don’t want to have to create/maintain an infinite number of scopes for each key value (SSN, customer number, etc.)
1. jtabadero · April 26, 2011 - 8:07 pm · Reply→
  
  Are your users going to synch just once? if its just a one-off get record, modify then submit back to server, i wouldnt even consider using Sync Fx. Sync Framework is for synchronization or keeping replicas of data in sync over time thru change tracking and application of incremental changes.
  
  if you drop the scope everytime you sync, you lose the ability to sync incremental changes only since dropping a scope also clears metadata on what has been sync already.
  1. RickG · April 27, 2011 - 3:45 am ·
    
    We will have approximately 10-20 reference tables that will be sync’ed at start-up of our user application. These will be small tables which will not change much over time and will reside on the client database.
    
    Then we have tables which have hundreds of thousands of records. We don’t want to load our client database with all of these tables. Only want to grab the small subset of data for the user to manipulate and then sync back and then clear these tables.
    
    Investigating whether to use WCF only or use WCF for the load of the detail table information but use SyncFramework for the 10-20 reference tables.
    
    Thoughts?
  2. jtabadero · June 1, 2011 - 5:01 pm ·
    
    Hi RickG,
    
    If you’re using Sync Fx, you might as well do Sync Fx for everything. One thing to note though is that there is nothing in Sync Fx that allows you to selectively mark which rows you want to take offline. So you’ll have to code (via filtered scopes).
    
    when you sync back the rows, you can simply drop the filtered scope.
    
    JuneT
Divya · August 5, 2011 - 3:35 am · Reply→

Thank you for the post. It was really helpful. I have one question about this. I am using Sync Framework 1 (Sync service for ADO .Net for devices) . I can’t move to sync framework 2 because I am developing for mobile devices. So, In my case can I use Provisioning . Otherwise how can I sync specific columns from a table.

Thank you
1. jtabadero · August 9, 2011 - 1:35 pm · Reply→
  
  Hi Divya,
  
  I think I’ve already answered your question in the Sync Framework forums. You can’t use the provisioning API as they apply to a different provider. If you want to sync specific columns, you can modify the code generated by the Local Database Cache wizard to include only the columns you’re interested or you can use the adapter builder to manually configure your queries.
DocMeyer · August 12, 2011 - 6:05 pm · Reply→

Hi June,

thank you for your brilliant post. I have some questions concering specifying the value to pass in the filter parameter of a parameter-based filter:

First our scenario: we have a server (MS SQL Server) database with about 20 tables. Currently the clients sync the data with their local database (MS Server Compact). They sync serveral tables (Orders: “Download”, “Upload” and “UploadAndDownload”) as a whole. Just because the local database is getting to big we want to use parameter-based filters to bring the local database size down. So every client will only sync the data which is associated with his userid.
The tables have serveral constraints, so we have to specify the filter parameter values of the filter clause of one filter template depending on the result of another sync process (or query). Think of something like that:

get all “order entries” for a user with ID = UserID from a table “ORDER”, then get all “order detail entries” for all OrderID’s you found (in the previous query) from a table “ORDERDETAILS”, then get all products associated with those “order detail entries” from a table “PRODUCT” … etc. Then build the scopes and sync this data. So in the end every client just sync the min. amount of data.

Wenn it comes to the UserID it is easy because we can use a simple value assignment:

serverProvOrders.Tables[“Orders”].FilterParameters[“@userid”].Value = UseriD;

It is getting more complex if we have a number of IDs …so we need to pass a list of ID’s to a filter parameter of a parameter-based filter template. Something like this:

Collection col_orderids = new Collection();
col_orderids.Add(123);
col_orderids.Add(456);
….

serverProvOrderdetails.Tables[“Ordersdetails”].FilterParameters[“@orderid”].Value = col_orderids;

You said the filter parameter acts like a “WHERE param = value” statement without the “WHERE”. But what we actually need is “WHERE param IN (…)” statement.

Do we have to create multiple scopes for each single entry in a collection, or can we pass a list of values to a filter parameter?

Imagine we have (for one UserID) 10 “order entries” and 100 “order detail entries” and 500 “product entries” associated with those “order detail entries” .. so will we end up with 610 scopes just for one sync?

Moreover I think in the client app we have to get all relevant ID from all server tables first (by using a normal sql query against the server database) to build all scopes for the whole sync process. It would be perfect to tell the filter templates about the database constraints (primary – foreign Keys relations between the tables). But as far as I see that, it is far beyond the capabilities of parameter-based filtering.

So what do you think is the best way to deal with such an scenario when it comes to sync?

For now we plan to speed up the local database by setting serveral table indexes to the local database after the first sync. Btw. can the sync framework deal with the fact that there are individual table indexes only in the local database (the server database have different indexes)?
I know this question isn’t right here but I don’t want to start a new entry for that.

Many thanks in advance for stealing your time with that ….
1. DocMeyer · August 12, 2011 - 7:52 pm · Reply→
  
  Hi again,
  
  after spending the last hour on searching for an answer I guess I have a solution for my first question:
  I build the filter template by specifying the filtering clause to use against the tracking table in a way that I will get my “WHERE param IN (…)”. Something like this for the template:
  
  serverProvOrderdetails.Tables[“Ordersdetails”].AddFilterColumn(“OrderID”);
  serverProvOrderdetails.Tables[“Ordersdetails”].FilterClause = “[Ordersdetails_tracking].[OrderID] IN (@orderidliststring)”;
  SqlParameter param = new SqlParameter(“@orderidliststring”, SqlDbType.NText);
  serverProvOrderdetails.Tables[“Ordersdetails”].FilterParameters.Add(param);
  
  ([Ordersdetails_tracking] is the corresponding tracking table for the table “Ordersdetails”)
  
  When creating the synchronization scope I convert my Collection to a string and pass the value. Something like that:
  ….
  string akt_orderidliststring = col_orderids.toString();
  …
  serverProvOrderdetailsforOrders.Tables[“Ordersdetails”].FilterParameters[“@orderidliststring”].Value = akt_orderidliststring;
  
  But can I work with more complex filtering clauses containing a subquery? Something like this:
  
  serverProvOrderdetails.Tables[“Ordersdetails”].AddFilterColumn(“OrderID”);
  serverProvOrderdetails.Tables[“Ordersdetails”].FilterClause = “[Ordersdetails_tracking].[OrderID] IN (SELECT OrderID FROM [Orders] WHERE userid=@UserID)”;
  SqlParameter param = new SqlParameter(“@UserID”, SqlDbType.Int);
  serverProvOrderdetails.Tables[“Ordersdetails”].FilterParameters.Add(param);
  
  So when creating the synchronization scope I just have to pass the UserID:
  serverProvOrderdetailsforOrders.Tables[“Ordersdetails”].FilterParameters[“@UserID”].Value = akt_userid;
  
  If you continue this idea I could use “JOIN” statements in the subquery as well to reduce the numbers of scopes. Is there anything that votes against that strategie (apart from the fact that joins have an heavy impact on the performance)?
  
  Thank you again …
  1. jtabadero · August 12, 2011 - 8:22 pm ·
    
    using the IN clause with a subquery is the most common approach. btw, you can alias Orderddetails_tracking to “side” like this: [side].[OrderID]
    
    Sync Framework uses ‘side’ to alias the tracking table and ‘base’ to alias the table being synched.
    
    Do you delete orders? if you do, I suggest you change your subquery to select from the Orders base table. If a row is deleted, its no longer in the base table, so the subquery fails to return deleted rows. so you can change your filter as : “[side].[OrderID] IN (SELECT OrderID FROM [Orders_tracking] WHERE userid=@UserID)”
    
    Do orders get reassigned to another user?
  2. DocMeyer · August 12, 2011 - 10:51 pm ·
    
    Hi June,
    
    thank you for the quick response. Your are right with your objection .. we need the subquery to select deleted rows! And this will answer your question: orders will not be reassigned, they will be deleted and added as new orders for another user.
    I am aware of the fact that if they are only reassigned they won’t disappear in the scope .. so it is a good thing that our solution work with delete/add in this case.
    
    What is your opinion on my second question ( individual table indexes only in the local database)? Because we want to implement that as an interim solution.
    
    Thanks …
  3. jtabadero · August 12, 2011 - 11:02 pm ·
    
    sync framework only cares primarily about your PK. it doesnt care how many indeces you have.
  4. DocMeyer · August 12, 2011 - 11:12 pm ·
    
    perfect .. thank you again June 😉
  5. DocMeyer · October 4, 2011 - 9:39 pm ·
    
    Hi June,
    
    after a while I would like to ask another question. I am almost finished with my implementation of the filter templates for the scenario I described above. I managed to optimize all filter clauses to use the “UserID” as the only filter parameter (thanks to multiple Sub-Selects). In my scenario many clients synchronizing to a single central server. So they all use the same parameter-based filter templates and because just the UserID is different for each client application, they all provisioning the same scope (meaning they all use the same Scopename). That means that the scope_info table on the server contains a single row to represent that scope for all clients. The filter templates select distinct rows for some tables (depending on the UserID) but sometimes the affected rows are the same for different users (these “shared rows” only concerns data which is only synchronised from the server to each client and not back).
    I read that synchronizations by many clients can lead to contention over the data in the server database, which can slow down synchronization for the entire community. A solution might be to create a new scope for each client and use the same filter templates for that individual scope. This creates a separate row in the scope_info table for each client. Because each scope handles the tables in the same way, all per-table elements are reused, so the scope_info entry and a scope_config entry are the only additional elements created in the database.
    Does it make sense to use that strategy to improve the synchronisation performance for my scenario? Just keep in mind that there is no overlapping of rows when it comes to the data which is synchronised back to the server (by each client). Just some data that is synchronised down to each client is shared by many users.
    
    ——————-
    
    Another thing: if I use Sub-Selects for the filter clauses of the template like this:
    
    serverTemplate_diffsync.Tables[“SOMETABLE”].AddFilterColumn(“OrderID”);
    serverTemplate_diffsync.Tables[“SOMETABLE”].FilterClause = “[side].[OrderID] IN (SELECT [OrderID] FROM [WORKER_tracking] WHERE [UserID] = @userid)”;
    
    I query the “WORKER_tracking” table to make sure that I will detect deleted rows in the “WORKER” table (Btw. UserID is not the the PK in the “WORKER” table and changes to that table are made by a server application which is not using the Sync Framework). I synchronise the “WORKER” table completely because it isn’t that big, so I just added the table to the scope without any filter column. Therefore in the “WORKER_tracking” table is no column “UserID”. The Sync Framework adds a column to a “_tracking” table if it is part of the “AddFilterColumn” statement, but does it add that column as well, if it only appears in the filter clause? Or do I have to declare a AddFilterColumn” statement for the “WORKER” table as well to make sure I can query the “WORKER_tracking” table the way I described above?
    
    Thank you again …
  6. jtabadero · October 4, 2011 - 10:42 pm ·
    
    yes, a scope per client is the preferred approach. less contention when updating the scope info knowledge, size of knowledge is smaller and clients isolated from each other. if in the future you need to deprovision/reprovision a client, less impact on other clients.
    
    use the AddFilterClause as the provisioning uses that to identify which columns to add in the tracking table in addition to the PK and which columns should be included in the trigger to update the tracking tables.
  7. DocMeyer · October 4, 2011 - 11:23 pm ·
    
    Thank you very much June for that fast answer …
  8. DocMeyer · October 5, 2011 - 12:05 am ·
    
    Hi June,
    
    let me get that right … because I use many Sub-Selects I need a certain column in one “_tracking” table to be able to use that column in another filter clause of another table. Eg.:
    
    serverTemplate_diffsync.Tables[“SOMETABLE”].AddFilterColumn(“OrderID”);
    serverTemplate_diffsync.Tables[“SOMETABLE”].AddFilterColumn(“ProductID”);
    serverTemplate_diffsync.Tables[“SOMETABLE”].FilterClause = “[side].[OrderID] IN (SELECT [OrderID] FROM [site].[WORKER] WHERE [UserID] = @userid)”;
    …
    serverTemplate_diffsync.Tables[“SOMETABLE2”].AddFilterColumn(“ProductRefID”);
    serverTemplate_diffsync.Tables[“SOMETABLE2”].FilterClause = “[side].[ProductRefID] IN (SELECT [ProductID] FROM [site].[SOMETABLE] WHERE [OrderID] IN (SELECT [OrderID] FROM [site].[WORKER] WHERE [UserID] = @userid))”;
    
    You see I need the column “ProductID” of “SOMETABLE” in the filter clause of “SOMETABLE2” and not in the first filter clause. Because I query the _tracking table of “SOMETABLE” ([site].[SOMETABLE]), I have to make sure that the column “ProductID” will be added to the _tracking table of “SOMETABLE”. Therefore I call AddFilterColumn(“ProductID”) without using that column in the first filter clause. I hope the Sync Framework can handle that.
    
    Thanks
  9. jtabadero · October 5, 2011 - 10:29 am ·
    
    afaik, you can add a FilterColumn on a table without specifying a corresponding FilterClause for it in the same table. so in your case, you can add ProductId as FilterColumn of SomeTable but use @ProductId in the FilterClause of another table.
    
    the FilterColumn is to make sure the column is added in the tracking tables. the FilterClause will take any SQL construct that is valid in a WHERE clause. The FilterClause is actually just concatenated with the rest of the Sync Framework’s own filters.
  10. DocMeyer · October 5, 2011 - 8:24 pm ·
    
    thank you June …
Kash · October 20, 2011 - 11:32 pm · Reply→

Hi June,

Based on your response a scope per client is the preferred approach. If we go this way we will end up more stored procs. For each parameter in each client it will create a store proc. If you have 2000 clients we will end up with 2000 more stored procs. Is there any other way we can avoid store procs.
Thank you,
Kash
1. jtabadero · October 20, 2011 - 11:59 pm · Reply→
  
  use Filter Templates and create Filtered Scopes based on the template.
Kash · October 21, 2011 - 3:18 am · Reply→

June,

Thank you for your quick response. I have to pass two parameters on each client. I have to add new template per client in order to avoid store procs? Or need to add new scope per client?
Thank you in advance…
here is my sample code which i am trying to do.

string userspecific = “UserKashTemplate”;
string userscope = “Kashscope”;
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(userspecific);
scopeDesc.UserComment = userspecific; //”Filter template for ReviewerFilter filter”;

// Definition for tables.
DbSyncTableDescription ordersDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable(“tableName”, serverConnection);
scopeDesc.Tables.Add(ordersDescription);

SqlSyncScopeProvisioning serverTemplate = new SqlSyncScopeProvisioning(serverConnection, scopeDesc, SqlSyncScopeProvisioningType.Template);

serverTemplate.Tables[“queue”].AddFilterColumn(“colName”);
serverTemplate.Tables[“queue”].FilterClause = “[side].[colName] = @colName1”;
SqlParameter param = new SqlParameter(“@colName1”, SqlDbType.NVarChar, 100);
serverTemplate.Tables[“queue”].FilterParameters.Add(param);

serverTemplate.Tables[“queue”].AddFilterColumn(“colName”);
serverTemplate.Tables[“queue”].FilterClause = “[side].[colName] = @colName2”;
SqlParameter param1 = new SqlParameter(“@colName2”, SqlDbType.NVarChar, 100);
serverTemplate.Tables[“queue”].FilterParameters.Add(param1);

serverTemplate.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);
serverTemplate.SetCreateTriggersDefault(DbSyncCreationOption.Skip);
serverTemplate.SetCreateProceduresDefault(DbSyncCreationOption.Skip);

serverTemplate.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);

if (!serverTemplate.ScopeExists(userscope))
{
// start the provisioning process
serverTemplate.Apply();
}

serverProvWA_BillUserTest.PopulateFromTemplate(userscope, userspecific);

serverProvWA_BillUserTest.Tables[“queue”].FilterParameters[“@colName1”].Value = “Kash”;

serverProvWA_BillUserTest.Tables[“queue”].FilterParameters[“@ColName2”].Value = Kash;
1. jtabadero · October 21, 2011 - 9:14 am · Reply→
  
  one template only. then provision one scope based on the template for each variation of the client filter value.
  
  let’s say we rename your template to simply say UserTemplate, same template definition as you have.
  you would the provision a scope “KashScope” based on the UserTemplate passing in “Kash” as parameter
  then we could provision another scope “JuneScope” based on the UserTemplate passing in “June” as parameter.
  1. Kash · October 21, 2011 - 10:05 pm ·
    
    Hi JUne,
    
    Thank you very much for your response. It is working for one parameter but it is not syncing the second parameter record. I have to write seperate method for second paramter? In my above example i have two parameters one is Author and another one is Reviewer. When i add two rows in my DB one record for author and an other one for reviewer. It is syncing author record not syncing reviewer record. Please advise.
    And also an other question is i want to avoid syncing deleted records what i ahve to do? Meaning when I deleted records from compact Db i do not want to delete in central Db.
    Thank you in Advance.
    Kash
  2. jtabadero · October 22, 2011 - 12:26 am ·
    
    can you post the actual code?
    
    if you dont want to upload deleted records, you can intercept the change dataset in the ChangesSelected event of the local provider (your CE DB) and remove the deleted rows. look up my blog post on manipulating change datasets here. i have a sample there.
Kash · October 21, 2011 - 10:20 pm · Reply→

Hi June,

I am getting the following message: Could not create a template with name ‘UserTemplate’ as a template with that name already exists.

here is my code
serverTemplate.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.CreateOrUseExisting);

// Create the “Orders_OriginState_Filter_template” template in the database.
//serverTemplate.Apply();

if (!serverTemplate.ScopeExists(userscope))
{
// start the provisioning process
serverTemplate.Apply(); // I am getting that error here. What I am missing? Please advise.
}
1. DocMeyer · October 21, 2011 - 11:36 pm · Reply→
  
  Hi Kash,
  
  I had the same problem … test the existing of the template with the Provisioning object you create for the template instead of using the Provisioning object for the user (and use “TemplateExists” instead of “ScopeExists”):
  
  DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(userspecific);
  …
  SqlSyncScopeProvisioning serverTemplate = new SqlSyncScopeProvisioning(serverConnection, scopeDesc, SqlSyncScopeProvisioningType.Template);
  if (!serverTemplate.TemplateExists(userspecific))
  {
  …
  // Template definitions incl. SetCreateProceduresForAdditionalScopeDefault and Apply goes here
  …
  }
  
  just keep in mind that you need two Provisioning object .. one for the template and one for the user where you assign the values for the filters
  
  Hope that helps,
  
  Greetings,
  DocMeyer
  1. jtabadero · October 22, 2011 - 12:27 am ·
    
    thanks DocMeyer.
2. jtabadero · October 22, 2011 - 12:21 am · Reply→
  
  just follow DocMeyer’s advice on using TemplateExists to check if the template already exists as ScopeExists only applies to Scopes.
Kash · October 21, 2011 - 10:58 pm · Reply→

June,

I did more test on different cases, This test is for two filter parameters. If i add two records one for each on client Db then my sync process working fine.(syncing back to server Db no issues).
Where are if added two records one for each parameter on the server then one record is not syncing back to client Db. Please advise.
Thank you,
Kash
1. DocMeyer · October 21, 2011 - 11:45 pm · Reply→
  
  Hi Kash,
  
  instead of:
  
  serverTemplate.Tables[“queue”].AddFilterColumn(“colName”);
  serverTemplate.Tables[“queue”].FilterClause = “[side].[colName] = @colName1″;
  SqlParameter param = new SqlParameter(“@colName1″, SqlDbType.NVarChar, 100);
  serverTemplate.Tables[“queue”].FilterParameters.Add(param);
  
  serverTemplate.Tables[“queue”].AddFilterColumn(“colName”);
  serverTemplate.Tables[“queue”].FilterClause = “[side].[colName] = @colName2″;
  SqlParameter param1 = new SqlParameter(“@colName2″, SqlDbType.NVarChar, 100);
  serverTemplate.Tables[“queue”].FilterParameters.Add(param1);
  
  try this:
  
  serverTemplate.Tables[“queue”].AddFilterColumn(“colName”);
  serverTemplate.Tables[“queue”].FilterClause = “[side].[colName] = @colName1 OR [side].[colName] = @colName2″;
  SqlParameter param = new SqlParameter(“@colName1″, SqlDbType.NVarChar, 100);
  SqlParameter param1 = new SqlParameter(“@colName2″, SqlDbType.NVarChar, 100);
  serverTemplate.Tables[“queue”].FilterParameters.Add(param);
  serverTemplate.Tables[“queue”].FilterParameters.Add(param1);
  
  change “OR” to “AND” in case of need …
  
  Greetings,
  DocMeyer
  1. DocMeyer · October 21, 2011 - 11:48 pm ·
    
    forget about the tipp “change “OR” to “AND” in case of need …” .. it makes no sense in your case ;))
2. jtabadero · October 22, 2011 - 12:27 am · Reply→
  
  have you verified that you have the right filter for that client? can you post the code?
Kash · October 22, 2011 - 1:47 am · Reply→

Thank you DocMeyer for your response. I will try atht way also.
Kash · October 22, 2011 - 1:53 am · Reply→

June,

Here is my code
SqlConnection serverConnection = new SqlConnection(“Data Source=localhost; Initial Catalog=xxxx; Integrated Security=True”);
string userspecific = “UserTemplate”;
string userscope = “Kashscope”;

DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(userspecific);

// Set a friendly description of the template.
scopeDesc.UserComment = userspecific;

// Definition for tables.
DbSyncTableDescription ordersDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable(“queue”, serverConnection);
scopeDesc.Tables.Add(ordersDescription);

SqlSyncScopeProvisioning serverTemplate = new SqlSyncScopeProvisioning(serverConnection, scopeDesc, SqlSyncScopeProvisioningType.Template);

serverTemplate.Tables[“queue”].AddFilterColumn(“Reviewer”);
serverTemplate.Tables[“queue”].FilterClause = “[side].[Reviewer] = @Reviewer”;
SqlParameter param = new SqlParameter(“@Reviewer”, SqlDbType.NVarChar, 100);
serverTemplate.Tables[“queue”].FilterParameters.Add(param);

// An other parameter Author

serverTemplate.Tables[“queue”].AddFilterColumn(“Author”);
serverTemplate.Tables[“queue”].FilterClause = “[side].[Author] = @Author”;
SqlParameter param1 = new SqlParameter(“@Author”, SqlDbType.NVarChar, 100);
serverTemplate.Tables[“queue”].FilterParameters.Add(param1);

//skip creating the tracking table
////serverTemplate.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);
//skip creating the triggers
//// serverTemplate.SetCreateTriggersDefault(DbSyncCreationOption.Skip);
//skip creating the stored procedures
//// serverTemplate.SetCreateProceduresDefault(DbSyncCreationOption.Skip);

// create a new select changes stored proc for this scope
serverTemplate.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Skip);

// Create the “Orders_OriginState_Filter_template” template in the database.
//serverTemplate.Apply();

if (!serverTemplate.ScopeExists(userscope))
{
// start the provisioning process
serverTemplate.Apply();
}

// Create a synchronization scope for OriginState=WA.
SqlSyncScopeProvisioning serverProvWA_BillUserTest = new SqlSyncScopeProvisioning(serverConnection);

// populate the scope description using the template
serverProvWA_BillUserTest.PopulateFromTemplate(userscope, userspecific);

string Reviewer;
string Author;
Reviewer = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();

if (Reviewer.Contains(“T306933”))
{
Reviewer = “Kash”;
Author = “Kash”;
}
else
{
Reviewer = “Unknown”;
Author = “”;
}

//string Author = “Kash”;
// specify the value we want to pass in the filter parameter, in this case we want only orders from WA
serverProvWA_BillUserTest.Tables[“queue”].FilterParameters[0].Value = Reviewer;

serverProvWA_BillUserTest.Tables[“queue”].FilterParameters[1].Value = Author;

// Set a friendly description of the template.
serverProvWA_BillUserTest.UserComment = “queue data includes only Kash.”;

// Create the new filtered scope in the database.
//serverProvWA.Apply();
if (!serverProvWA_BillUserTest.ScopeExists(userscope))
{
// start the provisioning process
serverProvWA_BillUserTest.Apply();
}

SqlCeConnection clientConn1 = new SqlCeConnection(@”Data Source=’C:\Sync\MySyncDatabase.sdf'”);

// get the description of allwfatablesync from the SyncDB server database
DbSyncScopeDescription OrdersscopeDescCe2 = SqlSyncDescriptionBuilder.GetDescriptionForScope(userscope, serverConnection);

// create CE provisioning object based on the allwfatablesync
SqlCeSyncScopeProvisioning clientProvisionCe1 = new SqlCeSyncScopeProvisioning(clientConn1, OrdersscopeDescCe2);
if (!clientProvisionCe1.ScopeExists(userscope))
{
// start the provisioning process
clientProvisionCe1.Apply();
}

// create a sync orchestration object
SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
syncOrchestrator.LocalProvider = new SqlCeSyncProvider(userscope, clientConn1);

// set the remote provider of sync orchestrator to a server sync provider that is
// associated with the OrdersScope-NC scope in the SyncDB database
syncOrchestrator.RemoteProvider = new SqlSyncProvider(userscope, serverConnection);

// set the direction to Upload and Download
syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;

// subscribe for errors that occur when applying changes to the client
((SqlCeSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += new EventHandler(Program_ApplyChangeFailed);

// starts the synchornization session
SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

// prints statistics from the sync session
Console.WriteLine(“Start Time: ” + syncStats.SyncStartTime);
Console.WriteLine(“Total Changes Uploaded: ” + syncStats.UploadChangesTotal);
Console.WriteLine(“Total Changes Downloaded: ” + syncStats.DownloadChangesTotal);
Console.WriteLine(“Complete Time: ” + syncStats.SyncEndTime);
Console.WriteLine(String.Empty);
1. jtabadero · October 22, 2011 - 9:08 am · Reply→
  
  you can follow DocMeyer’s suggestion of doing and AND or OR
  
  //add reviewer filter parameter
  serverTemplate.Tables[“queue”].AddFilterColumn(“Reviewer”);
  SqlParameter param = new SqlParameter(“@Reviewer”, SqlDbType.NVarChar, 100);
  serverTemplate.Tables[“queue”].FilterParameters.Add(param);
  
  // An other parameter Author
  serverTemplate.Tables[“queue”].AddFilterColumn(“Author”);
  SqlParameter param1 = new SqlParameter(“@Author”, SqlDbType.NVarChar, 100);
  serverTemplate.Tables[“queue”].FilterParameters.Add(param1);
  
  //set filter clause
  serverTemplate.Tables[“queue”].FilterClause = “([side].[Author] = @Author AND [side].[Reviewer] = @Reviewer)”;
Kash · October 24, 2011 - 9:10 pm · Reply→

Hi June and DocMeyer,

Thank you very much for your time. It is working fine for now.but I made change on stored proc select changes reviewer pamater was not added so added then it was working WHERE (side.Author = @Author) OR (side.Reviewer = @Reviewer).
Thank you again for your responses.
Kash
shawn · October 31, 2011 - 10:27 pm · Reply→

Hi June:
Have you tested deprovisioning a scope created from a filtered template where there are multiple such scopes?
In my test, when I delete one of two scopes created from the same template, all of the tracking tables associated with the scopes are removed from the SqlCe database.
My understanding was that these would not be deleted if another scope referencing them exists. This has caught me by surprise; now I need to figure out how to manage client data another way.
When I run a test, the tracking tables are successfully created, and data is synced down from the server to the client for two offenders.
But when I deprovision one of the scopes, all of the tracking tables are deleted???

This is the unit test:
[DeploymentItem(“lsior.sdf”),
TestMethod()]
public void ProvisionThenDeprovisionOffenderClientScopeTest()
{
ProvisionServerDbTest serverDbTest = new ProvisionServerDbTest();
serverDbTest.ProvisionServerDbTestInitialize();
serverDbTest.ProvisionOffenderTemplateTest();
ServerSync serverSync = new ServerSync(schemaName);
ClientSync clientSync = new ClientSync();
string serverConnString = “Data Source=xxx;Initial Catalog=lsiorSyncUTest;User ID=xxx;Password=xxx”;

offenderId = Guid.Parse(“55A75922-5A06-4F1F-8E5F-1CE4B6E80EAF”);
scopeName = “Offender” + offenderId.ToString().ToUpper();
//provision scope on server
serverSync.ProvisionOffenderScope(offenderId);
//provision scope on client
clientSync.ProvisionClientScope2(schemaName, objectPrefix, scopeName, clientConnString, serverConnString);

offenderId = Guid.Parse(“5129EB9E-0791-446B-B81D-45497BC23296”);
string scopeName2 = “Offender” + offenderId.ToString().ToUpper();
//provision scope on server
serverSync.ProvisionOffenderScope(offenderId);
//provision scope on client
clientSync.ProvisionClientScope2(schemaName, objectPrefix, scopeName2, clientConnString, serverConnString);

//sync offender data from server to client.
ProvisionClientDb provisionClient = new ProvisionClientDb(schemaName, objectPrefix, clientConnString, serverConnString, clientConnString);
provisionClient.SyncClientScope(scopeName, SyncDirectionOrder.Download);
provisionClient.SyncClientScope(scopeName2, SyncDirectionOrder.Download);

//deprovision one of the scopes.
clientSync.DeprovisionClientScope(objectPrefix, scopeName, clientConnString);
Assert.IsTrue(true);
}

called modules:
Run a test to create an Offender-based filtered template.

public void ProvisionOffenderTemplateTest()
{
ProvisionServerDb target = new ProvisionServerDb(schemaName, serverConnString);
//create the offender template.
templateName = “Offender”;
templateComment = “Filtered template for Offender root”;
FilterTable offenderFilterTable = new FilterTable(“Offender”, “OffenderId”, “[side].[OffenderId] = @offenderId”, “@offenderId”, SqlDbType.UniqueIdentifier);
FilterTable alertFilterTable = new FilterTable(“Alert”, “OffenderId”, “[side].[OffenderId] = @offenderIdId”, “@offenderIdId”, SqlDbType.UniqueIdentifier);
FilterTable controlDateFilterTable = new FilterTable(“ControlDate”, “OffenderId”, “[side].[OffenderId] = @offenderId”, “@offenderId”, SqlDbType.UniqueIdentifier);
FilterTable profileFilterTable = new FilterTable(“Profile”, “OffenderId”, “[side].[OffenderId] = @offenderIdId”, “@offenderIdId”, SqlDbType.UniqueIdentifier);
FilterTable externalOffenderMapFilterTable = new FilterTable(“ExternalOffenderMap”, “OffenderId”, “[side].[OffenderId] = @offenderIdId”, “@offenderIdId”, SqlDbType.UniqueIdentifier);
filterTables.Clear();
filterTables.Add(offenderFilterTable);
filterTables.Add(alertFilterTable);
filterTables.Add(controlDateFilterTable);
filterTables.Add(profileFilterTable);
filterTables.Add(externalOffenderMapFilterTable);
try
{
target.ProvisionFilteredTemplate(templateName, templateComment, filterTables);
Assert.IsTrue(true);
}
catch (Exception e)
{
Assert.Fail(e.Message);
}
}

Create the filtered template:
public void ProvisionFilteredTemplate(string templateName, string templateComment, List filterTables)
{
ValidateParameter(templateName, “templateName”);
ValidateParameter(templateComment, “TemplateComment”);
ValidateFilterTables(filterTables);
TemplateName = templateName;
TemplateComment = templateComment;
FilterTables = filterTables;
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(TemplateName);
scopeDesc.UserComment = TemplateComment;
DbSyncTableDescription tableDesc;

foreach (FilterTable table in FilterTables)
{
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(table.TableName, ServerConn);
scopeDesc.Tables.Add(tableDesc);
}

SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(ServerConn, scopeDesc, SqlSyncScopeProvisioningType.Template);
serverProvision.ObjectSchema = SchemaName;

foreach (FilterTable table in FilterTables)
{
serverProvision.Tables[table.TableName].AddFilterColumn(table.FilterColumn);
serverProvision.Tables[table.TableName].FilterClause = table.FilterClause;
SqlParameter param = new SqlParameter(table.FilterParameter, table.FilterParameterType);
serverProvision.Tables[table.TableName].FilterParameters.Add(param);
}

if (serverProvision.TemplateExists(TemplateName) == false)
{
serverProvision.SetCreateProceduresDefault(DbSyncCreationOption.CreateOrUseExisting);
serverProvision.SetUseBulkProceduresDefault(true);
serverProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);
serverProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.CreateOrUseExisting);
serverProvision.Apply();
}
}

Provision Offender Scope on the server
public void ProvisionOffenderScope(Guid offenderId)
{
ValidateParameter(offenderId, “offenderId”);
ScopeName = OffenderTemplateName + offenderId.ToString().ToUpper();
ScopeComment = string.Format(“Offender scope for Id: {0}”, offenderId.ToString().ToUpper());

SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(ServerConn);
serverProvision.ObjectSchema = SchemaName;
serverProvision.PopulateFromTemplate(ScopeName, OffenderTemplateName);
serverProvision.UserComment = ScopeComment;

//populate the table filter parameters with the offender id.
foreach (SqlSyncTableProvisioning tbl in serverProvision.Tables)
{
tbl.FilterParameters[0].Value = offenderId;
}

serverProvision.Apply();
}

Provision Offender Scope on the client:
public void ProvisionClientScope2(string schemaName, string objectPrefix, string scopeName, string clientConnString, string serverConnString)
{
SqlConnection serverConn = new SqlConnection(serverConnString);
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, null, schemaName, serverConn);
SqlCeConnection clientConn = new SqlCeConnection(clientConnString);
SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc);
clientProvision.ObjectPrefix = objectPrefix;
clientProvision.Apply();
clientConn.Close();
serverConn.Close();
}

Deprovision Offender Scope on client public void DeprovisionClientScope(string objectPrefix, string scopeName, string connString)
{
//schemaName is validated in property setter and defaulted.
ValidateParameter(objectPrefix, “ObjectPrefix”);
ValidateParameter(scopeName, “ScopeName”);
ValidateParameter(connString, “ConnString”);
ObjectPrefix = objectPrefix;
ScopeName = scopeName;
ConnString = connString;

if (ClientScopeExists == true)
{
using (ClientConn = new SqlCeConnection(ConnString))
{
ClientConn.Open();
SqlCeSyncScopeDeprovisioning clientDeprovision = new SqlCeSyncScopeDeprovisioning(ClientConn);
clientDeprovision.ObjectPrefix = ObjectPrefix;
clientDeprovision.DeprovisionScope(ScopeName);
}
}
}
1. jtabadero · November 1, 2011 - 10:11 am · Reply→
  
  @shawn – it seems the SqlCeSyncScopeDeprovisioning.DeprovisionScope behaves differently than the SqlSyncScopeDeprovisioning counterpart and it doesnt check for other dependent scope. am confirming if the docs is wrong or if its a bug.
Kash · November 7, 2011 - 9:36 pm · Reply→

June,

My post are not showing up?
1. jtabadero · November 7, 2011 - 9:37 pm · Reply→
  
  what post?
Kash · November 7, 2011 - 9:41 pm · Reply→

I just added couple of questions. Some reasons i am not seeing those here?
Kash · November 7, 2011 - 9:41 pm · Reply→

June,

I have issue with guid. When i pass guid it is nor syncing. Basically i have 3 parameters and OR Conditions. I have 3 level of approval any of the approval changes status it has to sync all their local db from centralDB. Other two parameters are working fine. But guid is not. Please take a loo on my code and advise me.
Thank you,
Kash
Kash · November 7, 2011 - 9:45 pm · Reply→

June
It looks like if I add any sample code it is not showing up.
1. jtabadero · November 7, 2011 - 9:46 pm · Reply→
  
  just post it on the Sync Framework Developer forums…
Kash · November 7, 2011 - 10:14 pm · Reply→

June,

I just posted on http://social.microsoft.com/Forums/en/syncdevdiscussions/thread/34ecf65e-739e-432f-92f5-b57aa749567a
Please take a look and advise me thnak you in advance Kash
DocMeyer · November 14, 2011 - 7:36 am · Reply→

Hi June,

i have an additional question referring to your answer to one of my questions above. We talked about the problem with scopes when it comes to reassigning rows in a way that another scope will include them (because of a different filter value the rows are now belong to a “new scope”). I know that if rows are reassigned they won’t disappear in the “old” scope.
Now that I have to extent my app I will have a new table with reassigned rows. My filter value is still the “UserID” and for the new table this UserID will change (the row isn’t deleted and inserted as a new row).

What is your advice to deal with that? I don’t really find something about that in the snyc framework docs .. so maybe you have a good link to an article as a starting point for that.

Thanks,
DocMeyer
1. jtabadero · November 14, 2011 - 7:04 pm · Reply→
  
  if your filter column the PK as well?
  
  if its a new table, then are you provisioning a new scope for this new table?
  
  can you post the schema for the tables involved? and the scope and filter definitions?
  1. DocMeyer · November 14, 2011 - 7:50 pm ·
    
    Hi June,
    
    I will have a new scope with the new table included. So it will be a hard break for all users using the old scope (deprovisioning the old one and switch to the new one). I paste the code for the server and client configuration.
    
    First the server code:
    
    public void ConfigureServer(string hostName, string database, string userName, string password)
    {
    try
    {
    SqlSyncProvider provider = new SqlSyncProvider();
    
    // new scope name
    provider.ScopeName = ScopeName;
    
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    builder.DataSource = hostName;
    if (String.IsNullOrEmpty(userName) && String.IsNullOrEmpty(password))
    builder.IntegratedSecurity = true;
    else
    {
    builder.IntegratedSecurity = false;
    builder.UserID = userName;
    builder.Password = password;
    }
    
    builder.InitialCatalog = database;
    builder.ConnectTimeout = 900;
    UserTable.ServerConnectionString = builder.ToString();
    provider.Connection = new SqlConnection(UserTable.ServerConnectionString);
    provider.CommandTimeout = 900;
    
    provider.ApplyChangeFailed += new EventHandler(provider_ApplyChangeFailed);
    provider.ApplyMetadataFailed += new EventHandler(provider_ApplyMetadataFailed);
    
    providersCollection[“Server”] = provider;
    this.orchestrator.RemoteProvider = provider;
    
    int aktWorkerID = DataModel.WorkerId;
    
    DbSyncScopeDescription scopeDesc_diffsync = new DbSyncScopeDescription(ScopeNameTemplate);
    // Add all tables first
    foreach (var item in SynUploadAdapterTables)
    {
    scopeDesc_diffsync.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(item, (System.Data.SqlClient.SqlConnection)provider.Connection));
    }
    
    SqlSyncScopeProvisioning serverTemplate_diffsync = new SqlSyncScopeProvisioning((System.Data.SqlClient.SqlConnection)provider.Connection, scopeDesc_diffsync, SqlSyncScopeProvisioningType.Template);
    if (!serverTemplate_diffsync.TemplateExists(ScopeNameTemplate))
    {
    // Specify the column in the tables to use for filtering data
    
    // COWORKER
    serverTemplate_diffsync.Tables[“COWORKER”].AddFilterColumn(“WorkerID”);
    serverTemplate_diffsync.Tables[“COWORKER”].AddFilterColumn(“DocumentID”);
    serverTemplate_diffsync.Tables[“COWORKER”].FilterClause = “[side].[WorkerID] = @workerid”;
    SqlParameter param_coworker = new SqlParameter(“@workerid”, SqlDbType.Int);
    serverTemplate_diffsync.Tables[“COWORKER”].FilterParameters.Add(param_coworker);
    // REPORDER:
    serverTemplate_diffsync.Tables[“REPORDER”].AddFilterColumn(“DocumentID”);
    serverTemplate_diffsync.Tables[“REPORDER”].AddFilterColumn(“CustomerID”);
    serverTemplate_diffsync.Tables[“REPORDER”].FilterClause = “[side].[DocumentID] IN (SELECT [DocumentID] FROM [COWORKER_tracking] WHERE [WorkerID] = @workerid)”;
    SqlParameter param_reporder = new SqlParameter(“@workerid”, SqlDbType.Int);
    serverTemplate_diffsync.Tables[“REPORDER”].FilterParameters.Add(param_reporder);
    // EQUIPMENT
    serverTemplate_diffsync.Tables[“EQUIPMENT”].AddFilterColumn(“CustomerID”);
    serverTemplate_diffsync.Tables[“EQUIPMENT”].AddFilterColumn(“EquipmentID”);
    serverTemplate_diffsync.Tables[“EQUIPMENT”].FilterClause = “[side].[CustomerID] IN (SELECT [CustomerID] FROM [REPORDER_tracking] WHERE [DocumentID] IN ( SELECT [DocumentID] FROM [COWORKER_tracking] WHERE [WorkerID] = @workerid))”;
    SqlParameter param_equipment = new SqlParameter(“@workerid”, SqlDbType.Int);
    serverTemplate_diffsync.Tables[“EQUIPMENT”].FilterParameters.Add(param_equipment);
    // EQUIPHIST
    serverTemplate_diffsync.Tables[“EQUIPHIST”].AddFilterColumn(“EquipmentI”);
    serverTemplate_diffsync.Tables[“EQUIPHIST”].FilterClause = “[side].[EquipmentI] IN (SELECT [EquipmentID] FROM [EQUIPMENT_tracking] WHERE [CustomerID] IN (SELECT [CustomerID] FROM [REPORDER_tracking] WHERE [DocumentID] IN ( SELECT [DocumentID] FROM [COWORKER_tracking] WHERE [WorkerID] = @workerid)))”;
    SqlParameter param_equiphist = new SqlParameter(“@workerid”, SqlDbType.Int);
    serverTemplate_diffsync.Tables[“EQUIPHIST”].FilterParameters.Add(param_equiphist);
    // SPAREPART
    serverTemplate_diffsync.Tables[“SPAREPART”].AddFilterColumn(“DocumentID”);
    serverTemplate_diffsync.Tables[“SPAREPART”].AddFilterColumn(“ArticleID”);
    serverTemplate_diffsync.Tables[“SPAREPART”].FilterClause = “[side].[DocumentID] IN (SELECT [DocumentID] FROM [REPORDER_tracking] WHERE [DocumentID] IN ( SELECT [DocumentID] FROM [COWORKER_tracking] WHERE [WorkerID] = @workerid))”;
    SqlParameter param_sparepart = new SqlParameter(“@workerid”, SqlDbType.Int);
    serverTemplate_diffsync.Tables[“SPAREPART”].FilterParameters.Add(param_sparepart);
    // LAGERBESTAND
    serverTemplate_diffsync.Tables[“LAGERBESTAND”].AddFilterColumn(“LALAGER”);
    serverTemplate_diffsync.Tables[“LAGERBESTAND”].AddFilterColumn(“ARTICLEID”);
    serverTemplate_diffsync.Tables[“LAGERBESTAND”].FilterClause = “[side].[LALAGER] IN (SELECT [StorageID] FROM [USER] WHERE [WorkerID] = @workerid)”;
    SqlParameter param_lagerbestand = new SqlParameter(“@workerid”, SqlDbType.Int);
    serverTemplate_diffsync.Tables[“LAGERBESTAND”].FilterParameters.Add(param_lagerbestand);
    // ARTICLE
    serverTemplate_diffsync.Tables[“ARTICLE”].AddFilterColumn(“ArticleID”);
    serverTemplate_diffsync.Tables[“ARTICLE”].FilterClause = “[side].[ArticleID] IN (SELECT [ARTICLEID] FROM [LAGERBESTAND_tracking] WHERE [LALAGER] IN (SELECT [StorageID] FROM [USER] WHERE [WorkerID] = @workerid)) OR [side].[ArticleID] IN (SELECT [ArticleID] FROM [SPAREPART_tracking] WHERE [DocumentID] IN (SELECT [DocumentID] FROM [REPORDER_tracking] WHERE [DocumentID] IN ( SELECT [DocumentID] FROM [COWORKER_tracking] WHERE [WorkerID] = @workerid))) OR [side].[ArticleID] = ‘1000@163001′”;
    SqlParameter param_article = new SqlParameter(“@workerid”, SqlDbType.Int);
    serverTemplate_diffsync.Tables[“ARTICLE”].FilterParameters.Add(param_article);
    
    // create a new select changes stored proc for this scope
    serverTemplate_diffsync.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
    
    // Create the “serverTemplate_diffsync” template in the database.
    serverTemplate_diffsync.Apply();
    }
    
    // Assign Filter Values
    SqlSyncScopeProvisioning serverProv_diffsync = new SqlSyncScopeProvisioning((System.Data.SqlClient.SqlConnection)provider.Connection);
    if (!serverProv_diffsync.ScopeExists(ScopeName))
    {
    serverProv_diffsync.PopulateFromTemplate(ScopeName, ScopeNameTemplate);
    serverProv_diffsync.Tables[“COWORKER”].FilterParameters[“@workerid”].Value = aktWorkerID;
    serverProv_diffsync.Tables[“REPORDER”].FilterParameters[“@workerid”].Value = aktWorkerID;
    serverProv_diffsync.Tables[“EQUIPMENT”].FilterParameters[“@workerid”].Value = aktWorkerID;
    serverProv_diffsync.Tables[“EQUIPHIST”].FilterParameters[“@workerid”].Value = aktWorkerID;
    serverProv_diffsync.Tables[“SPAREPART”].FilterParameters[“@workerid”].Value = aktWorkerID;
    serverProv_diffsync.Tables[“LAGERBESTAND”].FilterParameters[“@workerid”].Value = aktWorkerID;
    serverProv_diffsync.Tables[“ARTICLE”].FilterParameters[“@workerid”].Value = aktWorkerID;
    serverProv_diffsync.Apply();
    }
    
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.ToString());
    }
    }
    
    now the client code:
    
    public void ConfigureClient(string path)
    {
    try
    {
    CEDatabase client = new CEDatabase();
    client.CreationMode = CEDatabaseCreationMode.FullInitialization;
    client.Location = Path.Combine(path, “localedatabase.sdf”);
    client.Name = “Client”;
    
    SyncUtility.PathToLocalDatabase = client.Location;
    if (!File.Exists(client.Location))
    {
    SqlCeEngine engine = new SqlCeEngine(client.Connection.ConnectionString);
    engine.CreateDatabase();
    engine.Dispose();
    }
    
    SqlCeSyncProvider clientProvider = new SqlCeSyncProvider();
    clientProvider.ChangesApplied += clientProvider_ChangesApplied;
    clientProvider.SyncProgress += clientProvider_SyncProgress;
    clientProvider.ApplyChangeFailed += clientProvider_ApplyChangeFailed;
    clientProvider.ApplyMetadataFailed += clientProvider_ApplyMetadataFailed;
    clientProvider.ScopeName = ScopeName;
    clientProvider.Connection = client.Connection;
    providersCollection.Add(client.Name, clientProvider);
    this.orchestrator.LocalProvider = clientProvider;
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.ToString());
    }
    }
    
    and:
    
    private void CheckIfProviderNeedsSchema(SqlCeSyncProvider localProvider)
    {
    try
    {
    if (localProvider != null)
    {
    SqlCeConnection ceConn = (SqlCeConnection)localProvider.Connection;
    
    DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(ScopeName, (System.Data.SqlClient.SqlConnection)(this.providersCollection[“Server”]).Connection);
    
    SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning(ceConn, scopeDesc);
    if (!ceConfig.ScopeExists(scopeName))
    {
    ceConfig.Apply();
    }
    }
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.ToString());
    }
    }
    
    Wenn it comes to the server config I separated the “Download”, “Upload” and “UploadAndDownload” SyncDirectionOrder in seperate classes. The code above is for the Download SyncDirectionOrder.
    As you can see all filterclauses work in the end with the workerid … and this is the new table. The new strategy is that the workerid is reassigned to a new Worker … so a given repairorder will be managed by a different worker. Until now a repairorder was deleted and added as a new entry for the new worker.
    
    The scope name for the template is always the same. The scope name for each user is unique (I use the workerid to build a dynamic scopename, this function is not included in the code above). Moreover I haven’t add the code for the event handler just to keep this post a bit smaller ;). The variable “ScopeName” is a class Member, so the server and client code access the same ScopeName.
    
    When it comes to the PKs it depends on the table. For most tables we use GUIDs (especially when it comes to the “UploadAndDownload” SyncDirectionOrder because the Sync Frameworks needs unique PKs ) but for the “COWORKER” table the PK is not included. Therefore we have a column “ID” which is a GUID.
    
    Greetings,
    DocMeyer
2. Rafael Azevedo · January 24, 2013 - 9:31 pm · Reply→
  
  Hey DocMeyer,
  I have a similiar problem that concerns to reassign rows ( in your case: orders ) which belongs to one scope to another scope. What did you do about?
  
  Thanks in advance!
Patrice · January 4, 2012 - 11:02 pm · Reply→

Hello June

I use for provisioning my client database a template with static filter for reduce the number of rows. The synchronization direction is only in download.
The server had for this table 2 000 000 rows and I synchronize only useful rows (about 1%)

The first synchronization is OK with tracking table on the server for all rows and inserted rows on the client with corresponding tracking table.

In some use case, server UPDATE rows for match the filter parameters and enlarge the client database.
This rows are never INSERTED on the client database and the tracking table flag then with ROW_IS_TOMBSTONE to 1.

With SqlProfiler, I see the Synchronization Framework use for these rows BULKUPDATE (and not BUILINSERTED) stored procedure. But like the client database never knew these rows, the MERGE is not possible because the stored procedure created by framework doesn’t have NOT MATCHED BY TARGET instruction.

Do you have a solution for this kind of problem? I Think ALTER the stored procedure created by framework is not a good way. And I don’t want to synchronize all the rows.

Thank You
Patrice
1. jtabadero · January 6, 2012 - 3:02 pm · Reply→
  
  Sync Framework OOTB doesnt support “partition realignment” or rows going “in-scope” or “out-of-scope”.
  
  in your case, when you do an update that brings the rows “in-scope”, Sync Framework is behaving normally in that it detects the updates, however, it would fail applying the update since there is nothing to update.
  
  have you tried subscribing to the ApplyChangeFailed event to check if its even complaining about the failed updates?
  
  modifying the stored procs is the most straight forward change, others do a delete and re-insert operation instead of doing an update.
  1. Patrice · January 9, 2012 - 6:48 pm ·
    
    Hello June,
    
    Thank you for yours answer.
    About the ApplyChangeFailed event, the conflict is : Stage = ApplyingUpdates {3}, Type = LocalCleanedupDeleteRemoteUpdate {6}.
    
    I made the same post in Microsoft Forum (http://social.microsoft.com/Forums/en/syncdevdiscussions/thread/a21753bd-7a9b-4299-9c29-6cdce20a241b) and I try to find a solution with ConflictResolutionPolicy value.
    
    Regards
  2. jtabadero · January 9, 2012 - 10:05 pm ·
    
    try setting the conflict resolution to apply with force write
Ivi · August 7, 2012 - 4:40 pm · Reply→

Hello,nice articles about sync framework!
I have a problem while provisioning the client. It says :”The specified change tracking operation is not supported. To carry out this operation on the table, disable the change tracking on the table, and enable the change tracking”
Any idea of what can i do? The compact database doesnt have change tracking.
Thanks
1. JuneT · August 7, 2012 - 6:34 pm · Reply→
  
  whats the version of SQL Compact that you have installed?
Things You Need To Know About SQL Data Sync Service « JuneT's idle thoughts · August 23, 2012 - 7:52 pm · Reply→

[…] Data Sync Service is largely based on Sync Framework, this blog post Sync Framework Provisioning, more or less describes what gets provisioned, except the Data Sync Service created object has a […]
Allan · October 3, 2012 - 1:03 pm · Reply→

What changes are required for using the automatic change tracking feature of SQL Server 2008 wrt this provisioning code. I am at the beginning of a project and have a legacy database schema to support, so I really do not want to put all this code into the database if there is a cleaner why to do it.
1. JuneT · October 3, 2012 - 10:23 pm · Reply→
  
  lots of changes… the custom change tracking not only tracks what was changed but which peer made the change…to use SQL Change Tracking, you have to take these into consideration. Plus, not all SQL Editions have SQL Change Tracking.
chandrasekar1 · October 16, 2012 - 6:28 pm · Reply→

Using Sync FW 2.1, working on sync between multiple MS SQL databases

1. need a sample/code which tells about syncing relationship tables like Customer, Order, Orderdetails using Filter template, ex: need to sync, if the customer placed a particular product order, many of the sample code that I found, only syncing single table with filter parameters.
2. What is the use of syncgroup, in below link, they explained, how to sync SQL with SQL compact ed, in that, I m not clear why they are using Syncgroup, anchor command and so & so.., whether we cant achieve this through filter template ?
http://msdn.microsoft.com/en-us/library/bb726003.aspx

Thanks, appreciated for any quick help
1. JuneT · October 16, 2012 - 6:39 pm · Reply→
  
  1. Sync Framework syncs at the table level. am not sure what you mean by synching relationship tables, you might want to expound exactly what it is that you want to achieve. if you want to filter the orders based on the filter applied to customer, am assuming you have a customer id to filter the orders table. if you want to filter the orderdetails table, then you can do an IN clause for your filter (have a look at this thread: http://social.msdn.microsoft.com/Forums/en/synclab/thread/f8516d4d-2352-468f-82e3-b06f18effaaa)
  
  2. There are two types of database providers in Sync Framework, there’s the older SqlCeClientSyncProvider/DBServerSyncProvider/SyncAgent and the newer SqlCeSyncProvider/SqlSyncProvider/SyncOrchestrator combos. SyncGroup is group of tables you want to sync in a particular sync session. it’s used in the older provider. the equivalent part in the newer providers is a Scope.
  1. chandrasekar1 · October 16, 2012 - 6:58 pm ·
    
    Thanks for thundering reply, I will try the link given for point 1 and point 2 explanation is very clear
  2. chandrasekar1 · October 17, 2012 - 5:08 pm ·
    
    i am proceeding with the link given in point 1, while executing below code, i am getting error “Must declare the scalar variable “@ProductID”” on serverprovison.apply(),
    
    even i followed the MSDN post, but no luck.
    http://social.microsoft.com/Forums/en/syncdevdiscussions/thread/fc888f69-9df8-4fba-a2ab-fe8fe11dac3b
    
    the above MSDN link and the approach given in by you on above post “Filter template”, it some what not matching, please provide any solution, thanks
    
    {
    SqlConnection serverConn = new SqlConnection(ConfigurationManager.ConnectionStrings[“ServerCon”].ConnectionString);
    DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(“Shoptemplate”);
    
    DbSyncTableDescription Customer = SqlSyncDescriptionBuilder.GetDescriptionForTable(“Customer”, serverConn);
    DbSyncTableDescription orders = SqlSyncDescriptionBuilder.GetDescriptionForTable(“orders”, serverConn);
    DbSyncTableDescription Product = SqlSyncDescriptionBuilder.GetDescriptionForTable(“Product”, serverConn);
    
    scopeDesc.Tables.Add(Customer);
    scopeDesc.Tables.Add(orders);
    scopeDesc.Tables.Add(Product);
    
    SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc, SqlSyncScopeProvisioningType.Template);
    
    serverProvision.Tables[“Customer”].AddFilterColumn(“ID”);
    serverProvision.Tables[“Product”].AddFilterColumn(“ID”);
    serverProvision.Tables[“orders”].AddFilterColumn(“ProductID”);
    
    serverProvision.Tables[“Customer”].FilterClause = “[side].[ID] in (SELECT customerID from orders O WHERE O.ProductID=@ProductID)”;
    serverProvision.Tables[“Product”].FilterClause = “[side].[ID] =@ProductID”;
    serverProvision.Tables[“orders”].FilterClause = “[side].[productID] =@ProductID”;
    SqlParameter param = new SqlParameter(“@ProductID”, System.Data.SqlDbType.UniqueIdentifier, 16);
    serverProvision.Tables[“orders”].FilterParameters.Add(param);
    
    serverProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
    serverProvision.Apply();
    
    }
  3. chandrasekar1 · October 17, 2012 - 5:25 pm ·
    
    i tired with different approach, like below, it works fine, but dont know the cause of commenting SetCreateProcedures…().
    
    //Introduce below line
    serverProvision.Tables[“orders”].FilterParameters.Add(param);
    serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
    
    //and commented create Procedures
    // serverProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
    serverProvision.Apply();
chandrasekar1 · October 19, 2012 - 8:02 pm · Reply→

I tried the approach given by you on below link
http://social.msdn.microsoft.com/Forums/en/synclab/thread/f8516d4d-2352-468f-82e3-b06f18effaaa

for User table:
filtercolumn = Guid, SpecialId
filterparameter =@guid
filterclause = “[side].[Guid] = @guid”
for Customer table:
filtercolumn = CustomerId, SpecialId
filterparameter =@guid
filterclause = “[side].[CustomerId] IN (Select CustomerId from customer_tracking c, user_tracking u where c.SpecialId = u.SpecialId AND u.Guid=@guid)”
for Product table:
filtercolumn = ProductId, CustomerId
filterparameter =@guid
filterclause = “[side].[ProductId] IN (Select ProductId from product_tracking where CustomerId IN (Select CustomerId from customer_tracking c, user_tracking u where c.SpecialId = u.SpecialId AND u.Guid=@guid))”

Seems, its not working, as far FilterClause, its work’s for only one table like Order, if I try to apply like below sample code, it throws error “Must declare the scalar variable “@ProductID””, Please provide any working sample code, i am stuck for so long time in this, thanks

Sync framework Version : 2.1 with updated Hotfixes

SqlConnection serverConn = new SqlConnection(“Data Source=…….;”);

DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(“Shop_template2”);
DbSyncTableDescription Customer = SqlSyncDescriptionBuilder.GetDescriptionForTable(“Customer”, serverConn);
scopeDesc.Tables.Add(Customer);
DbSyncTableDescription Product = SqlSyncDescriptionBuilder.GetDescriptionForTable(“Product”, serverConn);
scopeDesc.Tables.Add(Product);
DbSyncTableDescription Orders = SqlSyncDescriptionBuilder.GetDescriptionForTable(“Orders”, serverConn);
scopeDesc.Tables.Add(Orders);

SqlSyncScopeProvisioning serverTemplate = new SqlSyncScopeProvisioning(serverConn, scopeDesc, SqlSyncScopeProvisioningType.Template);

serverTemplate.Tables[“Customer”].AddFilterColumn(“ID”);
serverTemplate.Tables[“Customer”].FilterClause = “[side].[ID] in (SELECT customerID from orders O WHERE O.ProductID=@ProductID)”;

serverTemplate.Tables[“Product”].AddFilterColumn(“ID”);
serverTemplate.Tables[“Product”].FilterClause = “[side].[ID] =@ProductID”;

serverTemplate.Tables[“Orders”].AddFilterColumn(“ID”);
serverTemplate.Tables[“Orders”].FilterClause = “[side].[ID] =@ProductID”;

SqlParameter param = new SqlParameter(“@ProductID”, SqlDbType.UniqueIdentifier, 16);
serverTemplate.Tables[“Orders”].FilterParameters.Add(param);
serverTemplate.Apply();

SqlSyncScopeProvisioning serverProvRetail = new SqlSyncScopeProvisioning(serverConn);
serverProvRetail.PopulateFromTemplate(“RetailCustomers”, “Shop_template”);
serverProvRetail.Tables[“Customer”].FilterParameters[“ID”].SqlValue = “[side].[ID] in (SELECT customerID from orders O WHERE O.ProductID=@ProductID)”;
serverProvRetail.Tables[“Product”].FilterParameters[“ID”].SqlValue = “[side].[ID] =@ProductID”;
serverProvRetail.Tables[“Orders”].FilterParameters[“@ProductID”].Value = new Guid(“0FEF06BD-7EF5-4E19-BA96-904E5DCF9C63”);
serverProvRetail.Apply();
1. JuneT · October 22, 2012 - 11:22 am · Reply→
  
  check your code carefully, you are only adding a SQLParameter to your Orders table but you’re trying to use the parameter on the other tables. also, why are you setting a filter condition as a FilterParameter value?
  1. chandrasekar1 · October 23, 2012 - 1:40 pm ·
    
    after adding SQL parameter to each table and removing filter condition as a FilterParameter, then it works fine, Thanks
chandrasekar1 · November 9, 2012 - 6:37 pm · Reply→

Now I facing different issue with template filter scope, I created a template with one parameter ID having 10 tables, created 2 scopes A and B based on the template with having parameter ID 1 for scope A and 2 for Scope B on the server,
Scope A is provisioned by client A and scope B by client B, entries created in the server with ID 1 which affects 15 rows in different tables relation like Customer, Product, Order… etc.
When sync happens from Client A, it only transfers the data match to the filters having ID = 1,fine it works,
But when I initiate sync for 1st time, it transfer around only 10 rows from customer table
Then, I hit the sync again, then it transfer remainder 5 rows to client A from server.

Why its partially transferring the data from server in 2 sync’s or more, but not in single shot, any idea ?
nishant · December 7, 2012 - 6:23 pm · Reply→

Sir,
I am working on a project and i want to know that if i am deprovisioning the scope should i also deprovision the template scope…???

I am using Filter template currently
1. JuneT · December 7, 2012 - 8:23 pm · Reply→
  
  deprovisioning a scope only does not deprovision the corresponding template that was used to provision the scope.
  
  deprovisioning a template also deprovisions all scopes created from it.
nishant · December 17, 2012 - 12:56 pm · Reply→

JuneT can you please tell me that while changing filter condition for the synchronization is it necessary to deprovision the previous scopes and again create a new template scope for this filter.
Or we can just create a new template scope without deprovisioning the previous template scope and still be using the new template not the older one.
Because in my case if i dont deprovision the previous template scope the sync framework still uses the older template which has the older filter condition.
1. JuneT · December 17, 2012 - 2:55 pm · Reply→
  
  if you’re changing the filter condition itself, then you need to deprovision as it needs to recreate the selectchanges sp to include the new fitler condition. Note that Sync Fx doesn’t support partition realignment or rows going in and out of scope. For example, if you initially created a condition that says State=WA, then you changed the Filter to State=OH, the previously synched row with State=WA will still be there. Sync Fx will not clean that up. So, if you’re changing filter conditions, I suggest you deprovision, clean up your tables of out-of-scope rows, then re-provision.
Synchronizing WinRT/SQLite using Sync Framework toolkit « JuneT's idle thoughts · January 9, 2013 - 10:54 pm · Reply→

[…] a config file that we can use to provision our database to participate in synchronization (see: Sync Framework Provisioning on what happens during […]
Leon · February 6, 2013 - 10:27 pm · Reply→

Hello JuneT can you please help me out .

I would like to use Sync Framework only for Uploads to the central server database .

1.I have provision the master database with one scope and one table. (TableA)
2.Created created a copy of the master database using SMO methods.
3.Populated records in the Sync table (TableA) using sqlbulk insets with complex query for the filtering .
4.Provision the local database with the scope that created in step 1.
Problem:
When I sync the local DB with the master using Upload option it’s show that is uploaded x records, but when I query the central database the record not there.
However when I use the filtered scope option and download this the records via sysnc process, and then create a new records in the provisioning table(TableA) and uploading back to the server works fine.
I would like to using the sync Framework for Uploads only .
Populating the client table with data using my custom method because the schema very complicated and I need to write a complex query in order to get a filtered subset of data for this scope table(TableA) I have to repeat this steps many time for all client databases (300).
I

Could you please advice how to achieve this using Sync Framework and why when I am uploading the records looks like they are uploaded but in fact they are not.

Thank you in advance

Leo
1. JuneT · February 7, 2013 - 9:06 am · Reply→
  
  i replied to your post in the Sync Framework forums : http://social.msdn.microsoft.com/Forums/en-US/syncdevdiscussions/thread/78923c92-58b7-4e13-9300-9bc5ea69065e
Ben · February 6, 2013 - 11:57 pm · Reply→

Hi, is it possible to create a filter template scope directly in SQL CE? The SqlCeSyncScopeProvisioning constructor doesn’t have an overload that takes a Type unlike the SqlSyncScopeProvisioning constructor.
1. JuneT · February 7, 2013 - 9:10 am · Reply→
  
  unfortunately, no. Are you restricted to using SQL CE on the client? you might want to use SQL Express or LocalDB instead.
thomas · April 3, 2013 - 2:12 am · Reply→

Hello,
Thank you for all of your fantastic Posts. Without you i would be lost. 🙂
One Thing is not clear for me. When creating another filterd scope another “selectchanges” procedure will be create with SetCreateProceduresForAdditionalScopeDefault.

Why only selectchanges? why are the bulkinsert and all other procedures untouched?
1. JuneT · April 3, 2013 - 9:31 pm · Reply→
  
  if you’re not using a scope template and you just want scopes to have different filter values but same table/column definitions, the actual filter clause and value is hardcoded in the selectchanges stored proc, thus the need to have a separate selectchanges sp. i dont think you’d like to have separate triggers firing for every scope.
SeaDog Mariner · April 3, 2013 - 2:43 pm · Reply→

Hello again JuneT:
Like Chandrasekari (Oct 19 2012, I also am getting error, “Must declare the scalar variable “@officeName”.
code so far:
2. SqlConnection sqlServerConn = new SqlConnection(sqllocalConnectionString);
3. SqlConnection sqlAzureConn = new SqlConnection(sqlazureConnectionString);
4.
5. DbSyncScopeDescription myScope = new DbSyncScopeDescription(“AllTables_OfficeName_Filter_template”);
6. myScope.UserComment = “Filter template for AllTables.OfficeName “;
7.
8. DbSyncTableDescription tblRequisition = SqlSyncDescriptionBuilder.GetDescriptionForTable(“dbo.tblRequisition”, sqlServerConn);
9. myScope.Tables.Add(tblRequisition);
10. SqlSyncScopeProvisioning serverTemplate = new SqlSyncScopeProvisioning(sqlServerConn, myScope,SqlSyncScopeProvisioningType.Template);
11.
12. serverTemplate.Tables[“dbo.tblRequisition”].AddFilterColumn(“OfficeName”);
13. serverTemplate.Tables[“dbo.tblRequisition”].FilterClause=”[side].[OfficeName]=@officeName”;
14. SqlParameter parameter = new SqlParameter(“@officeName”,SqlDbType.NVarChar, 50);
15. serverTemplate.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
16. serverTemplate.Apply();
17.
18. SqlSyncScopeProvisioning serverProvOfficeName = new SqlSyncScopeProvisioning(sqlServerConn);
19. serverProvOfficeName.PopulateFromTemplate(“dbo.tblRequisitions-OfficeName-WA”,”AllTables_OfficeName_Filter_template”);
20. serverProvOfficeName.Tables[“dbo.tblRequisitions”].FilterParameters[“@officeName”].Value = “WA”;
21. serverProvOfficeName.UserComment = “data includes only OfficeName.”;
22. serverProvOfficeName.Apply();

Scalar Variable error is being thrown at line 14.
Also, the value “WA” is from your demo and is just here for test purposes. I am hoping to substitute value from WinForm text box (i.e. OfficeNameTextBox) for “WA”so that once provisioned for the variable @officeName, the sync will dynamically filter data relative to the OfficeName that originates the sync process (i.e. spoke and hub to Azure). Is this possible?
Thanks again for all.
1. JuneT · April 3, 2013 - 9:35 pm · Reply→
  
  i think you’re missing a line, you specified the column to be used for filtering, you defined the filter clause, you created the filter parameter, but you didnt add the filter parameter to the table.
  e.g.,
  
  serverTemplate.Tables["dbo.tblRequisition"].AddFilterColumn(“OfficeName”); serverTemplate.Tables["dbo.tblRequisition"].FilterClause=”[side].[OfficeName]=@officeName”; SqlParameter parameter = new SqlParameter(“@officeName”,SqlDbType.NVarChar, 50);
  serverTemplate.Tables["dbo.tblRequisition"].FilterParameters.Add(parameter);
SeaDog Mariner · April 3, 2013 - 11:20 pm · Reply→

Thanks June- that solves Scalar Variable error…
for the next section:
SqlSyncScopeProvisioning serverProvVesselName = new SqlSyncScopeProvisioning(sqlServerConn);
serverProvVesselName.PopulateFromTemplate(“dbo.tblRequisitions-VesselName-WA”,”AllTables_VesselName_Filter_template”);
serverProvVesselName.Tables[“dbo.tblRequisitions”].FilterParameters[“@vesselName”].Value = “WA”;
serverProvVesselName.UserComment = “data includes only VesselName.”;
serverProvVesselName.Apply();
I am now getting error:
Could not create a template with name ‘AllTables_VesselName_Filter_template’ as a template with that name already exists.
1. JuneT · April 4, 2013 - 7:45 am · Reply→
  
  you should use the TemplateExist() or ScopeExist() to check if a template or scope is already present before creating them. I suggest you go thru the walkthroughs/tutorials in the documentation.
SeaDog Mariner · April 3, 2013 - 11:27 pm · Reply→

Setting up a fresh db to test- guessing that’s throwing error.
SeaDog Mariner · April 3, 2013 - 11:39 pm · Reply→

OK- here is real error for the last block of code: Object reference not set to an instance of object.
SeaDog Mariner · April 4, 2013 - 3:43 am · Reply→

…or entire error syntax: System.NullReferenceException: Object reference not set to an instance of an object.
SeaDog Mariner · April 4, 2013 - 7:36 am · Reply→

Here is trial code that is throwing the error:
System.NullReferenceException: Object reference not set to an instance of an object
*****************
SqlSyncScopeProvisioning serverProvOfficeName = new SqlSyncScopeProvisioning(sqlServerConn);
serverProvOfficeName.PopulateFromTemplate(“dbo.tblRequisitions-OfficeName-WA”,”AllTables_OfficeName_Filter_template”);
serverProvOfficeName.Tables[“dbo.tblRequisitions”].FilterParameters[“@officeName”].Value = “WA”;
serverProvOfficeName.UserComment = “data includes only OfficeName.”;
serverProvOfficeName.Apply();
SeaDog Mariner · April 4, 2013 - 9:37 am · Reply→

Sometimes it’s hard to see the forest thru the trees! Found misspelled word (Requisition(s)).
Thanks for your help June.
chandrasekar1 · April 19, 2013 - 8:12 pm · Reply→

Hi JuneT,

Is there any way to sync the files, between two systems present in the same network using file sync provider through FTP/some other way in secured, I have tried with map network drive on share path, its working fine, but not secured, please let me know any possibilities

Thanks
Chandrasekar
1. JuneT · April 25, 2013 - 9:26 pm · Reply→
  
  the file sync providers doesn’t work with FTP as well as over HTTP.
Shashikant · August 6, 2013 - 12:04 am · Reply→

i am getting error
“Template definitions are not allowed to have values assigned to a FilterParameter. Parameter ‘@UserID’ on Table ‘[dbo].[ActivityNote]’ was found to have a value. Values are only defined in scopes that are created using the template.”

//filtering data
if (table.FilterCriterias != null && remoteConfig != null && table.FilterCriterias.Count > 0 && table.SyncOrder == Microsoft.Synchronization.SyncDirectionOrder.Download && table.isApplyFilter == true)
{
SqlSyncScopeProvisioning serverTemplate = null;

try
{
//need for server for creating tracking
remoteConfig.SetCreateProceduresDefault(DbSyncCreationOption.CreateOrUseExisting);
remoteConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);
remoteConfig.SetCreateTriggersDefault(DbSyncCreationOption.CreateOrUseExisting);
remoteConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.CreateOrUseExisting);
remoteConfig.SetPopulateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);
remoteConfig.Apply();
}
catch (Exception ex) { }
//create template, based on template creating the scope
serverTemplate = new SqlSyncScopeProvisioning(remoteConnection, remoteDescription, SqlSyncScopeProvisioningType.Template);
serverTemplate.ObjectSchema = “sync”;
foreach (FilterCriteria criteria in table.FilterCriterias)
{
try
{
serverTemplate.Tables[table.FullTableName].AddFilterColumn(criteria.ColumnName);
serverTemplate.Tables[table.FullTableName].FilterClause = string.Format(“[side].[{0}] = @{0}”, criteria.ColumnName);
criteria.FilterParam.Value = criteria.filterValue;
serverTemplate.Tables[table.FullTableName].FilterParameters.Add(criteria.FilterParam);

}
catch (Exception ex) { Console.WriteLine(ex.Source); }
}

//if template already exist then skip
try
{
serverTemplate.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.CreateOrUseExisting);
serverTemplate.Apply();
} catch (Exception ex) { }

//creating scope from template
try
{
remoteConfig.PopulateFromTemplate(“DataByIdFor” + tableName,table.ScopeName);
}
catch (Exception exc) { }
foreach (FilterCriteria criteria in table.FilterCriterias)
{
try
{
//adding the value to the table
remoteConfig.Tables[table.FullTableName].FilterParameters[criteria.FilterParam.ToString()].Value = criteria.filterValue;
}
catch (Exception ex) { Console.WriteLine(ex.Source); }
}
}
1. JuneT · October 18, 2013 - 4:24 pm · Reply→
  
  templates are used to create other scopes. you pass the actual parameter in the scope that derives from the template
Shashikant · August 6, 2013 - 12:12 am · Reply→

i solved previous error , but i got another error

An SqlParameter with ParameterName ‘@UserID’ is not contained by this SqlParameterCollection.

what its mean?
1. JuneT · October 18, 2013 - 4:23 pm · Reply→
  
  check your provisioning again to make sure you added the parameter to the correct table.
shane ham · September 28, 2013 - 3:52 am · Reply→

June,

I have a scenario where we will have 200+ clients that need to pull down data specifically assigned to them. So I can filter on UserId. However, what happens when widget x that was assigned to User 1 is assigned to User 2??

I thought MS said that Sync does not handle this.
1. JuneT · October 1, 2013 - 9:29 am · Reply→
  
  Sync Fx doesnt support rows going in and out of scope… am assuming you’re using some kind of a bridge/linking table for the assignments. so if you change the assignments, you’re making the change on the assignment table, and not the actual assigned rows. Sync Fx does change tracking and change enumeration per table. so another table’s change is not visible to the other.
  1. shaneham · October 1, 2013 - 10:01 am ·
    
    Sorry if I missed something or did not explain my scenario in enough detail. So if I have a Widget table with WidgetId, UserId, WidgetName, as columns I should be able to filter on UserId right. So User A syncs and pulls down all widgets where UserId = someId. Same for User B. So if on my widget table a widget that was previously assigned to User A (and synced with User A’s device) is chnaged and assigned to User B, what happens? The change is only made on the server in the widget table. I would think that the next time User B syncs, it would pick up its newly assigned widget correct?
    
    And thanks for you prompt response. You are my first resource when it comes to Sync Fx!
  2. JuneT · October 1, 2013 - 10:14 am ·
    
    ok. so assuming you have a record assigned to User A. you defined filters User = A and User = B. You sync, User A gets the record assigned to it. You then update the record to be User = B. You sync User B, it get’s the newly assigned record. However, when User A syncs, it will not get a change that says remove this record because it now belongs to User B. So your User A will continue to have the record that’s now assigned to User B. your record is now on both clients A and B.
    
    when you re-assigned the row to User B, it goes “in-scope” to User B’s filter and goes “out of scope” from User A. However the going out-of-scope will not be reflected on User A.
nailaakbar00 · October 10, 2013 - 9:13 pm · Reply→

hey nice work…
but m facing some problem. it works for me first time but when I do it again for an other scope it does not work. sometimes it gives error of bulk insertion and sometimes it gives 0 changes(uploaded, downloaded) whether there is huge data on server side to be downloaded. I dnt know why its happening. Can u suggest me some solution?
1. JuneT · October 18, 2013 - 4:19 pm · Reply→
  
  have you checked for errors/conflicts? subscribe to the ApplyChangeFailed event and you should see if there errors/conflicts applying the changes. you should also elaborate about the exact error you’re getting.
yusuf · April 13, 2014 - 1:39 am · Reply→

your lesspns are really useful for me to solve my problem. you added great artıcle what I desire. Can you solve this problem:

http://stackoverflow.com/questions/23017199/how-can-i-automatically-compare-and-synchronize-sql-server-data-to-another-datab
Sync construction selection WCF NTier software | CodersDiscuss.com · June 3, 2014 - 1:17 am · Reply→

[…] Additionally I’ve apply fixed selection utilizing https://jtabadero.wordpress.com/2010/09/02/sync-construction-provisioning/ […]
Venkat Narasimhan · July 21, 2014 - 8:16 pm · Reply→

How do you provision a SQLite DB?
1. JuneT · July 24, 2014 - 5:45 pm · Reply→
  
  there’s no out of the box sync provider for SQLite. you will have to use the Sync Framework Toolkit if you want.
Klaus · July 23, 2014 - 3:02 pm · Reply→

very nice blog about sync framework; it helped us to get our prototyp up and running.

But now I have one question and cannot find the answer:

Is it possible to make dynamic provisioning at runtime in a scenario where the sync is going over an IIS-webservice?
1. JuneT · July 24, 2014 - 5:45 pm · Reply→
  
  not sure exactly what you mean by “dynamic provisioning”. you can always check if a scope is provisioned or not and provision accordingly.
Scott Winterrowd · February 2, 2018 - 3:36 am · Reply→

Nice article June.

It was very informative and easily understood when compared to the Microsoft documentation I have been reading. Apparently this is still the way to go 7 years later. I think it is worth mentioning that the de-provisioning process is especially critical for removing all the objects that the provisioning process created. I tried to de-provision manually. Because I had not read this article and did not really understand the provisioning and de-provisioning process as well as I should have. Eventually, I found out there are some User-Defined Table Types that are also created and used by the stored procedures the provisioning process creates. These will keep the generated provisioning script from working if not removed. The generated provisioning script does not check for their existence and does not like it if they are in existence. I found the problem by using the methods shown in June’s other article at this link: (https://jtabadero.wordpress.com/2011/04/13/modifying-sync-framework-scope-definition-part-3-workarounds-addingremoving-columns/) to capture the generated script and examine it using SQL Server Management Studio’s query editor.

Many thanks to June.
Scott W.