Provisioning additional scope using Sync Framework’s sync scope provisioning


One of the common questions asked about new scope provisioning in Sync Framework is on how to provision a new or subsequent scope in addition to an existing one. However, while the Sync Framework documenation mentions provisioning multiples scopes is possible, it doesn’t explicitly mention how to go about it.

Here’s the example from the documentation for provisioning a new scope :

Code Snippet
  1. DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("filtered_customer");
  2.  
  3. // Definition for Customer.
  4. DbSyncTableDescription customerDescription =
  5.     SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn);
  6.  
  7. scopeDesc.Tables.Add(customerDescription);
  8.  
  9. SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
  10. serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
  11. serverConfig.ObjectSchema = "Sync";
  12.  
  13. // Specify which column(s) in the Customer table to use for filtering data,
  14. // and the filtering clause to use against the tracking table.
  15. // "[side]" is an alias for the tracking table.
  16. serverConfig.Tables["Sales.Customer"].AddFilterColumn("CustomerType");
  17. serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = ‘Retail’";
  18.  
  19. // Configure the scope and change-tracking infrastructure.
  20. serverConfig.Apply(serverConn);

So let’s say you want to add a new scope for the customer type “Wholesale”.  Most people would simply duplicate the code above by simply modifying the filter and setting it to “Wholesale” as follows:

Code Snippet
  1. DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("filtered_customer");
  2.  
  3. // Definition for Customer.
  4. DbSyncTableDescription customerDescription =
  5.     SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn);
  6.  
  7. scopeDesc.Tables.Add(customerDescription);
  8.  
  9. SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
  10. serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
  11. serverConfig.ObjectSchema = "Sync";
  12.  
  13. // Specify which column(s) in the Customer table to use for filtering data,
  14. // and the filtering clause to use against the tracking table.
  15. // "[side]" is an alias for the tracking table.
  16. serverConfig.Tables["Sales.Customer"].AddFilterColumn("CustomerType");
  17. serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = ‘Wholesale’";
  18.  
  19. // Configure the scope and change-tracking infrastructure.
  20. serverConfig.Apply(serverConn);

The problem is when you run the above code, you’ll encounter an error indicating that the tracking tables already exists for the table.

This is because the first time you create a scope, tracking tables, triggers and stored procedures are created by sync scope provisioning to enable change tracking on the table.

To add a new scope, you need to tell the Sync Framework sync scope provisioning to skip creating those objects by specifying DbSynCreation.Skip for the following:

SetCreateTrackingTableDefault
SetCreateTriggersDefault
SetCreateProceduresDefault

Code Snippet
  1. //skip creating the tracking table
  2. serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);
  3. //skip creating the triggers
  4. serverConfig.SetCreateTriggersDefault(DbSyncCreationOption.Skip);
  5. //skip creating the stored procedures
  6. serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Skip);

Then you need to tell it to create a new stored procedure for the additional scope via a call to SetCreateProceduresForAdditionalScopeDefault and specifying DbSynCreation.Create. This setting instructs the scope provisioning to create an additional stored procedure for selecting changes in the table (it has the same name as the first select changes stored procedure created by the first provisioning, however it has a GUID suffix).

Code Snippet
  1. //specify that we want a new stored procedure for an additional scope
  2. serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);

Here’s the revised code:

Code Snippet
  1. DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("filtered_customer");
  2.  
  3. // Definition for Customer.
  4. DbSyncTableDescription customerDescription =
  5.     SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn);
  6.  
  7. scopeDesc.Tables.Add(customerDescription);
  8.  
  9. SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
  10. serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
  11. serverConfig.ObjectSchema = "Sync";
  12.  
  13. // Specify which column(s) in the Customer table to use for filtering data,
  14. // and the filtering clause to use against the tracking table.
  15. // "[side]" is an alias for the tracking table.
  16. serverConfig.Tables["Sales.Customer"].AddFilterColumn("CustomerType");
  17. serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = ‘Wholesale’";
  18.  
  19. //skip creating the tracking table
  20. serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);
  21. //skip creating the triggers
  22. serverConfig.SetCreateTriggersDefault(DbSyncCreationOption.Skip);
  23. //skip creating the stored procedures
  24. serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Skip);
  25.  
  26. //specify that we want a new stored procedure for an additional scope
  27. serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
  28.  
  29. // Configure the scope and change-tracking infrastructure.
  30. serverConfig.Apply(serverConn);

 

Hope this helps.

Advertisements

One comment

  1. June, you can generalize this by trapping error 2714 as shown below. You will also see that I have a check for doing static versus dynamic filtering as discussed on your other posts. When doing dynamic filtering (on the server side), you do not want to create the SPs either. // Provision the database. Maybe there is already another scope on one or more of these // tables. If that is the case, we end up with error 2714 ("There is already an object // named \’xxx_tracking_table\’ in the database."). try { serverConfig.Apply(provider.Connection as SqlConnection); } catch (SqlException sqlException) { // Check to see what sort of error we got. If this is the one we might/will be // expecting, then that means we have the whole infrastructure and we may just need to // create the change selecting stored procedures. if (sqlException.Number == 2714) { // No to creating most things serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Skip); serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip); serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip); serverConfig.SetCreateTriggersDefault(DbSyncCreationOption.Skip); // Yes to the separate stored procedures if we are doing static filtering // and no if we are doing dynamic filtering if (this.Configuration.DynamicFilteringEnabled) serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Skip); else serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create); // Retry the provisioning. serverConfig.Apply(provider.Connection as SqlConnection); } }

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: