How to: Provision and Deprovision Synchronization Scopes and Templates (SQL Server)
This topic shows you how to provision and deprovision synchronization scopes and templates from a SQL Server, SQL Azure, or SQL Server Compact database. The examples in this topic focus on the following Sync Framework classes:
For more information about how to run the sample code, see "Example Applications in the How to Topics" in Synchronizing SQL Server and SQL Server Compact.
Understanding Provisioning and Deprovisioning
The first step to configure a database for synchronization is to define a scope that identifies what you want to synchronize. After you define the synchronization scope, you provision the database to create a change-tracking and metadata management infrastructure that consists of metadata tables, triggers, and stored procedures. This infrastructure is required for Sync Framework to synchronize a scope. After a database has been provisioned, it can synchronize with other members of the synchronization community by using a provider, such as SqlSyncProvider, to represent the database, and a SyncOrchestrator object to manage the synchronization session and connect to another synchronization provider. Provisioning a database for synchronization is a separate task from synchronizing with other databases, and the code for provisioning a database is typically contained in a separate application than the one used for synchronization.
The provisioning objects that are used to provision a SQL Server or database can be configured to create some or all of the following elements in a database when it is provisioned. The default option for creating synchronization elements is CreateOrUseExisting, which means that an element will be created unless it already exists in the database:
The base tables that are contained in the synchronization scope. After provisioning, the base tables are empty. The data itself is copied when the database is synchronized the first time. When multiple tables are added to a synchronization scope, they are created in the order in which they are added. When a table contains a foreign key relationship to a primary key in another table in the scope, add the primary key table first and the foreign key table second so that foreign key relationships are properly resolved. Base table creation is controlled by the SetCreateTableDefault method.
One tracking table for each base table that is in the scope. The tracking table tracks changes that are made to the associated base table. Tracking table creation is controlled by the SetCreateTrackingTableDefault method. When a table is already part of another scope and the new scope adds a filter column that was not in the original scope, Sync Framework automatically adds the filter column to the tracking table and populates it with the proper values.
Triggers that update a tracking table when changes are made directly to a base table. Trigger creation is controlled by the SetCreateTriggersDefault method.
Stored procedures for synchronization actions, such as enumerating changes, inserting a change, updating data, or deleting data. Stored procedure creation is controlled by the SetCreateProceduresDefault method. When a table is already part of another scope and the new scope adds a filter column that was not in the original scope, you must call the SetCreateProceduresForAdditionalScopeDefault method and specify the Create option to create an additional enumeration procedure that contains the filter column in its parameter list. Bulk procedures that use table-valued parameters to perform insert, update, and delete actions for multiple rows at a time are also created for SQL Server 2008 and databases. Bulk procedure creation is controlled by the SetUseBulkProceduresDefault method. Be aware that after bulk procedures are created for a table, they are always used for all scopes that include the table, even scopes that specified that bulk procedures should not be used. Also, if a table has already been provisioned for another scope that uses bulk procedures, specifying false to SetUseBulkProceduresDefault has no effect.
Entries are added to special tables, such as a scope_info table, that contain information about the scopes that are contained in the database. If these tables do not already exist, they are created.
The synchronization elements can be collected in a separate schema in the database. To do this, specify the schema name in the SqlSyncScopeProvisioningObjectSchema property. The schema must already exist in the database. If you use a schema when you provision a database, you must specify the same schema whenever the scope is used. For example, you must specify the SqlSyncProviderObjectSchema property when the database is synchronized.
The synchronization elements can be prefixed with a string. To do this, specify the prefix in the SqlSyncScopeProvisioningObjectPrefix or SqlCeSyncScopeProvisioningObjectPrefix method. If you use a prefix when you provision a database, you must specify the same prefix whenever the scope is used. For example, you must specify the SqlSyncProviderObjectPrefix property when the database is synchronized.
Provisioning can be applied directly by calling the SqlSyncScopeProvisioningApply or SqlCeSyncScopeProvisioningApply method, or, for a SQL Server database, a script can be created and run later. To create a script, call the SqlSyncScopeProvisioningScript method.
If you no longer need a scope, you can remove the scope and its associated metadata tables, triggers, and stored procedures by calling SqlSyncScopeDeprovisioningDeprovisionScope(String) or SqlCeSyncScopeDeprovisioningDeprovisionScope(String). After a scope has been removed it can no longer be used for synchronization. If a database element, such as a metadata table, is used by more than one scope it will not be removed until the last scope that uses it is removed. For example, you have a table named Customers, and you define two scopes that are named RetailCustomers and WholesaleCustomers, and that contain the Customers table. When you remove the WholesaleCustomers scope, the metadata table for the Customers table is not removed because it is still used by the RetailCustomers scope.
If you use a parameter-based filter to filter the synchronization data in a SQL Server or database, you first create a filter template and then create filtered scopes based on the filter template. You can also easily remove the filter template and all filtered scopes that were created from it, and also all of the associated metadata tables, triggers, and stored procedures, by calling DeprovisionTemplate(String). For example, you create a customerstate_template template that filters by using a state parameter. You create two filtered scopes, customers_WA and customers_OR, that are based on customerstate_template. When you remove customerstate_template, customers_WA and customers_OR are also removed. For more information about parameter-based filters, see How to: Filter Data for Database Synchronization (SQL Server).
You can also remove all scopes and filters and all synchronization metadata tables, triggers, and stored procedures from a database by calling SqlSyncScopeDeprovisioningDeprovisionStore or SqlCeSyncScopeDeprovisioningDeprovisionStore. Also, because removing individual scopes and filter templates removes only scope- and template-level metadata but not database-level metadata, SqlSyncScopeDeprovisioningDeprovisionStore or SqlCeSyncScopeDeprovisioningDeprovisionStore can be used to clean up the remaining synchronization metadata tables after all scopes and templates have been removed.
Note
Synchronization components are not removed from a database until all scopes that depend on them are deprovisioned. For example, when two scopes that contain the same table are present in a database, deprovisioning one of the scopes does not remove the metadata table for the table contained in the other scope. When the second scope is deprovisioned, the metadata table is removed.
Simultaneously performing another action on a database that is in the process of being provisioned is supported in some scenarios. In other situations, this might cause failures or unexpected results. The following list describes the supported scenarios as well as situations to avoid.
When you create two or more scopes from the same template, you can provision them concurrently to the same database.
When you create two or more scopes that are not from the same template, or are not based on templates, provisioning them concurrently to the same database may produce unexpected results, such as deadlocks in the database.
When you try to synchronize a scope that is currently being provisioned to a SQL Server or database, DbProvisioningException is thrown.
Provisioning Databases
Describing the Scope and Tables
The following code example describes a scope named filtered_customer, and adds two tables to the scope: Customer and CustomerContact. The tables already exist in the server database, so the GetDescriptionForTable(String, SqlConnection) method is used to retrieve the schema from the server database. All columns from the Customer table are included, but only two columns from the CustomerContact table are included.
Provisioning the Server
The following code example creates a provisioning object for the filtered_customer scope, and specifies that all synchronization-related objects should be created in a database schema named "Sync". As part of provisioning the scope, the code defines a filter on the Customer table. Only rows that match the filter will be synchronized. The code to define a filter requires two parts. The call to AddFilterColumn specifies that the CustomerType column is used for filtering and that it must be added to the tracking table that tracks changes for the Customer table. The call to FilterClause specifies the filter itself. The filter clause is a WHERE clause without the WHERE keyword. The [side] alias is an alias for the tracking table. No filter is defined on the CustomerContact table; therefore all rows from that table will be synchronized. After provisioning options are defined, the Apply method is called to create the change-tracking infrastructure in the server database; and the provisioning script is written to a file.
Provisioning the Clients
Client databases can be provisioned in two different ways:
Full initialization of a SQL Server or SQL Server Compact client database based on scope information that is retrieved from the server or another client database.
User objects and synchronization objects are created in the client database based on the schema information that is made available by the SqlSyncDescriptionBuilder and SqlCeSyncDescriptionBuilder objects. As part of the first synchronization session, the client database is prepared for synchronization, and all rows are downloaded to the client database as incremental inserts.
Snapshot initialization of a SQL Server Compact client database by using a pre-existing client database.
Snapshot initialization is designed to reduce the time required to initialize a client database. For more information on using a snapshot to initialize a SQL Server Compact client database, see How To: Provision for Synchronization Using Snapshot Initialization.
The following code example first retrieves scope information from the server and uses the base table and change-tracking schemas that are retrieved to provision a SQL Server Compact client database. The code then provisions a SQL Server client database based on scope information from the SQL Server Compact client database.
The following code example generates a snapshot named SyncSampleClient2.sdf from the SyncSampleClient1.sdf database. The code then synchronizes SyncSampleClient2.sdf with the server database.
Setting Configuration Options
The SqlSyncScopeProvisioning class contains several methods to set configuration options that control how a scope is synchronized, such as the SetCreateTableDefault or SetUseBulkProceduresDefault methods. The configuration options that these settings refer to are created for each table that is contained in the scope, and the settings take effect only for tables that are contained in the Tables collection at the time the configuration option is specified. For example, the following code creates a SqlSyncScopeProvisioning object and two DbSyncTableDescription objects, adds the two DbSyncTableDescription objects to the scope, and configures them so that they do not use bulk procedures by passing false to the SetUseBulkProceduresDefault method.
As an example of what can happen if these methods are called in the wrong order, the following code creates a SqlSyncScopeProvisioning object and two DbSyncTableDescription objects. The first table, myTable1, is added to the scope and false is passed to SetUseBulkProceduresDefault. This method sets the bulk procedures configuration option to false only for myTable1. This is because the method configures only the tables that are contained in the scope at the time the method is called. Therefore, when myTable2 is added to the scope, it is configured to use bulk procedures because that is the default configuration. When Apply is called to provision the scope, bulk procedures are not created for myTable1 but they are created for myTable2.
Default Configuration Options
The following table lists the configuration methods in the SqlSyncScopeProvisioning class, and describes the default behavior that occurs when provisioning is performed without calling the method to change the default.
Configuration method |
Default setting |
Default provisioning behavior |
|---|---|---|
Stored procedures are created for all tables in the scope that do not already have stored procedures provisioned. Any stored procedures that already exist are not changed. This setting does not affect the creation of bulk stored procedures. |
||
No additional selectchanges methods are created. |
||
Base tables are created for all tables in the scope. The tables are not populated with data during provisioning; data population occurs during synchronization. Any tables that already exist are not changed. |
||
Tracking tables are created for all tables in the scope. Any tracking tables that already exist are not changed. |
||
Triggers are created for all tables in the scope. Any triggers that already exist are not changed. |
||
Tracking tables are populated from their associated base tables for all tables in the scope. Tracking tables that are already populated are not changed. |
||
true when provisioning a SQL Server 2008 or database; otherwise, false |
Stored procedures for making bulk changes to the database are created for all tables in the scope. Any bulk stored procedures that already exist are not changed. This setting is independent of the SetCreateProceduresDefault method, and only affects the creation of bulk stored procedures. |
Deprovisioning Databases
The following code examples show you how to perform the following actions:
Deprovision a single scope from one of the client databases.
Deprovision a filtered scope template and all associated scopes from the server database.
Deprovision an entire client database so that all synchronization components are removed.
Removing a Scope
The following example removes the RetailCustomers scope from the client SQL Server database. As an additional example, the script that is used to remove the scope is also saved to a file. This script can be run against other databases to remove the RetailCustomers scope. This step is optional and is not required to deprovision a scope.
Removing a Filter Template
Before removing a filter template, it can be useful to find the filtered scopes that were created from the template, because all of these scopes will be removed when the filter template is deprovisioned. The following example script shows how to find the names of the filtered scopes that were created from the customertype_template filter template.
The following example removes the customertype_template template from the server SQL Server database. Removing the template also removes the RetailCustomers and WholesaleCustomers scopes from this database.
Removing All Synchronization Objects
The following example removes all synchronization metadata tables, triggers, and stored procedures from the client SQL Server Compact database.
Complete Code Example
The following complete code example includes the code examples that are described earlier and additional code to configure and clean up the databases. The example requires the Utility class that is available in Utility Class for Database Provider How-to Topics.