How to: Provision a Server Database for Collaborative Synchronization (Non-SQL Server)
Nota
The topics in this section of the documentation, Synchronizing Other ADO.NET Compatible Databases, are designed to demonstrate how databases other than SQL Server can be synchronized by using Sync Framework. In this release, SQL Server is used in code examples, but the code can be used for other ADO.NET compatible databases, with some modifications to the SQL Server-specific objects (such as SqlConnection) and the SQL queries that are shown. For information about SQL Server synchronization, see How to: Execute Database Synchronization (SQL Server).
This topic describes how to provision a database that is synchronized by DbSyncProvider, so that incremental data changes can be tracked in that database. Changes are tracked so that they can be applied to other nodes during a synchronization session. To provision a database for Sync Framework, follow these steps:
Enable snapshot isolation for the database
Identify tables to synchronize
Create tracking tables to store per-table metadata; and create indexes on those tables
Create triggers on each base table to populate and update the tracking tables
(Optional) Handle existing data in the database
Create a tracking table to store per-scope metadata; and create an index on this table
Define scopes to synchronize, which specifies which tables are synchronized as a unit
Create stored procedures to select and update data and metadata
These steps are not required for databases that are synchronized by SqlCeSyncProvider; provisioning is handled by Sync Framework when the database is initialized.
After a database is provisioned, it can be synchronized with other nodes. For more information about how to configure and execute synchronization, see How to: Synchronize Other ADO.NET Compatable Databases.
Enable Snapshot Isolation for the Database
During the change enumeration phase of a synchronization session, Sync Framework starts transactions under snapshot isolation. To start transactions under snapshot isolation, you must set the ALLOW_SNAPSHOT_ISOLATION database option to ON, as shown in the following code example:
ALTER DATABASE [database name] SET ALLOW_SNAPSHOT_ISOLATION ON
For more information, see SQL Server Books Online.
Identify Tables to Synchronize
The first step in provisioning the database is to identity the tables that will be synchronized. Each table must have a primary key. Consider the following code example. It shows the Sales.Customer table schema in the SyncSamplesDb_Peer1 database.
Each table that you synchronize has an associated DbSyncAdapter object, and you specify the primary key in the RowIdColumns collection for that object. For more information, see How to: Synchronize Other ADO.NET Compatable Databases.
Tables can be empty or they can contain existing data. If the table contains existing rows of data that should be synchronized, you must ensure that each row has a metadata entry in the appropriate change-tracking table. For more information, see Handle Existing Data in the Database.
Create Tracking Tables for Per-Table Metadata
Sync Framework requires a way to track which rows have changed since the previous synchronization session between two nodes. Changes are represented by two different types of metadata:
Per-table metadata, which tracks inserts, updates, and deletes for each table that is synchronized.
Per-scope metadata, which tracks which changes each node has received from other nodes.
Per-table metadata is tracked by using one tracking table for each base table. The base table and tracking tables must be present in each database that is synchronized by DbSyncProvider. The primary key of the tracking table is the same as in the base table, and additional columns are required. These columns are described in the following table. The names of the additional columns do not have to be the same as those listed; but they must match in order and type the queries or procedures that access the tracking tables. Some of these procedures are included in Create Stored Procedures to Select and Update Data and Metadata.
Column |
Description |
Updated when… |
|---|---|---|
<Primary key (PK) of the base table> - include a column for each PK column. |
Primary key columns of the base table. |
A row is inserted into the base table. The insert could originate from the local or remote node. |
update_scope_local_id |
The ID of the scope that performed the last update or delete. For updates or deletes that originate at the local node, this column is NULL. Refers to the scope_local_id column in the scope information table. For more information, see "Create Tracking Tables for Per-Scope Metadata". |
An update or delete from a remote node is applied to the base table. |
scope_update_peer_key |
The identity of the node that performed the last update or delete. |
An update or delete from a remote node is applied to the base table. |
scope_update_peer_timestamp |
The timestamp value at the remote database when the row was originally updated or deleted. |
An update or delete from a remote node is applied to the base table. |
local_update_peer_key |
The identity of the local node. This column will contain a value of 0 for each row unless the local database was restored from a backup. 1 |
An update or delete from a local operation or from a remote node is applied to the base table. |
local_update_peer_timestamp |
The timestamp value at the local database when the row was updated or deleted in the local database. 1 |
An update or delete from a local operation or from a remote node is applied to the base table. |
create_scope_local_id |
The identity of the scope that performed the insert. For updates or deletes that originate at the local node, this column is NULL. Refers to the scope_local_id column in the scope information table. For more information, "Create Tracking Tables for Per-Scope Metadata" in How to: Provision a Server Database for Collaborative Synchronization (Non-SQL Server). |
An insert from a remote node is applied to the base table. |
scope_create_peer_key |
The identity of the node that performed the insert. |
An insert from a remote node is applied to the base table. |
scope_create_peer_timestamp |
The timestamp value at the remote database when the row was originally inserted. |
An insert from a remote node is applied to the base table. |
local_create_peer_key |
The identity of the local node. This column will contain a value of 0 for each row unless the local database was restored from a backup. 1 |
An insert from a local operation or from a remote node is applied to the base table. |
local_create_peer_timestamp |
The timestamp value at the local database when the row was inserted into the local database. 1 |
An insert from a local operation or from a remote node is applied to the base table. |
sync_row_is_tombstone |
A value of 1 indicates that a metadata entry is for a delete in the base table. |
A row is deleted from the base table. The delete could originate from the local or remote node. |
last_change_datetime |
The date and time when the metadata row was last updated. |
A row in this tracking table is inserted or updated. |
restore_timestamp |
Stores the value of local_update_peer_timestamp at the time of a database restore. The value is then used as the local update timestamp value. |
Typically NULL, but can be set by the restore process. Set to NULL whenever a row is updated. |
<Filter Columns> - add a column for each non-PK column used in the filtering WHERE clause for any scope. |
Required only if a table is filtered for one or more scopes. Stores the value of the filtered columns for inserts, updates, and deletes. |
A row is inserted, updated, or deleted from the base table. The delete could originate from the local or remote node. |
1 Used by Sync Framework when overlapping scopes are synchronized. Consider the following example of updates for a database that synchronizes scope X with client A and scope Y with client B. Both scopes contain row Q.
Row Q is updated at client A and then synchronized with the database.
Client B synchronizes with the database and receives the update to row Q.
Client B is not aware of scope X, so the change from client A must appear as if it originated at the database. This is accomplished by using the values from local_update_peer_key and local_update_peer_timestamp when synchronizing with client B or any other client that does not synchronize the scope stored in update_scope_local_id for row Q.
Row Q is updated at the database and then synchronized with client A.
Client A is aware of scope X, so the values from scope_update_peer_key and scope_update_peer_timestamp are used when synchronizing with client A or any other client that synchronizes scope X.
We recommend that you create a separate database schema for the tracking table and all other objects that are related to synchronization metadata. This helps to isolate the metadata from the data in the base tables. For optimal performance, create indexes on each tracking table:
If data is not filtered: use the same primary key as the base table; and create a non clustered index on (local_update_peer_timestamp)
If data is filtered: use the same primary key as the base table; and create a non clustered index on (local_update_peer_timestamp, <filter columns>, <primary key columns>)
The following code examples create a table in the Sync schema that tracks changes for the Sales.Customer table, and add an index to the table.
Create Triggers to Populate and Update Tracking Tables
After creating tracking tables, add an INSERT, UPDATE, and DELETE trigger to each base table. When a user or Sync Framework inserts, updates, or deletes a row in a base table, a trigger fires and metadata for that row is inserted or updated in the change-tracking table. If Sync Framework applied the change to the base table (because it came from another node), Sync Framework updates the change-tracking table to reflect the origin of the change.
The following code example creates a trigger that updates change tracking metadata in the Sales.Customer_Tracking table when an update is made to the Sales.Customer table. For examples of insert and delete triggers, see Setup Scripts for Database Provider How-to Topics.
Handle Existing Data in the Database
Change-tracking metadata for each table is inserted and updated by triggers on the base table. Therefore, change-tracking tables do not contain information about any rows that were inserted into a base table before the triggers were added. To handle existing data in the database, metadata should be inserted for the existing data. Then, during the first synchronization session, all rows are sent as new inserts to the destination database. The following code example shows the command to execute for each base table after you have added triggers to the base table:
INSERT INTO [tracking table] ([pk columns], create_scope_local_id, local_create_peer_key, local_create_peer_timestamp, update_scope_local_id, local_update_peer_key, restore_timestamp, sync_row_is_tombstone)
SELECT [pk columns], NULL, 0, @@DBTS+1, NULL, 0, NULL, 0 from [base table] baseT left outer join [tracking table] trackingT
On baseT.[pk columns]=trackingT.[pk columns]
where tracking.[pk columns] is null
Create Tracking Tables for Per-Scope Metadata
Per-scope metadata is typically tracked by using two tables in each database:
The scope information table stores synchronization knowledge in a binary format for each scope. A scope is a logical grouping of tables that defines what data should be synchronized as a unit.
The scope mapping table identifies which tables in a database belong to a particular scope. It is possible for a table to belong to more than one scope. The mapping table must contain one entry for each <scope, table> pair.
Sync Framework uses knowledge to determine which changes to send to each database during synchronization. Applications do not have to work with knowledge directly. Consider a bidirectional synchronization topology with three nodes:
Node1 and Node2 synchronize all changes.
Node1 synchronizes with Node3.
A user performs an update at Node2.
Node3 synchronizes with Node2.
When Node3 synchronizes with Node2, Node3 already has most of the changes from Node2, because Node3 synchronized with Node1 first. Knowledge enables Sync Services to recognize this, and to synchronize only the update that occurred at Node2. For more information about knowledge, see Understanding Synchronization Knowledge.
The columns of the scope information table are described in the following table.
Column |
Description |
Updated when… |
|---|---|---|
scope_id |
An identifier for the scope, typically a GUID. |
Never updated |
scope_local_id |
An integer identifier for the scope. This should be an IDENTITY column. |
Never updated |
scope_name |
The name of the scope. |
Never updated |
scope_sync_knowledge |
A binary representation of the synchronization knowledge for each scope. |
All changes have been applied to a destination for a synchronization session. |
scope_tombstone_cleanup_knowledge |
A binary representation of the forgotten knowledge for each scope. Forgotten knowledge is used for metadata that has been cleaned up. |
All changes have been applied to a destination for a synchronization session. |
scope_timestamp |
The timestamp value when the metadata row was last updated. |
A row in this tracking table is updated. |
scope_cleanup_timestamp |
Timestamp value when the most recent tombstone cleanup was performed for this scope. |
Tombstones are cleaned up for another scope that has overlapping tables. |
The following code example creates a scope information table.
The scope information table is almost always queried to retrieve information about a particular scope, based on the scope name. Therefore, the primary key is defined on the scope_name column.
The columns of the scope mapping table are described in the following table.
Column |
Description |
Updated when… |
|---|---|---|
table_name |
The name of the table. |
Never updated |
scope_name |
The name of the scope. |
Never updated |
The following code examples create a scope mapping table and an index on that table.
Define Scopes to Synchronize
After you create the scope tables, decide on one or more scopes to synchronize. For example, you could define a scope named Sales and include the tables Customer, CustomerContact, OrderHeader, and OrderDetail in that scope. When the Sales scope is synchronized, the changes made to the four tables are exchanged between the two nodes. Defining a scope is a two-part process:
Add entries to the scope information table and to the scope mapping table, such as in the following code example.
Specify the scope name for the ScopeName property of the DbSyncProvider object and add a DbSyncAdapter object for each table that you want to include in the scope. For more information, see "Application Code for ScopeName and Connection" in How to: Synchronize Other ADO.NET Compatable Databases.
Importante
After a scope has been synchronized for the first time, the scope should not be changed. Changing tables in the scope or filtering clauses for those tables can lead to non-convergence of data.
Filtered and Overlapping Scopes
A scope is filtered if only a subset of the rows from the base table is included in the scope. For example, you could define a filtered scope named sales-WA that contains only the sales data for the state of Washington. To filter the data, the query or procedure that you specify for the SelectIncrementalChangesCommand property of the DbSyncAdapter object must include a WHERE clause that selects the appropriate data. The query or procedure should select changes based on the filter columns in the tracking table rather than the filter columns in the base table.
The following types of filtering are not supported:
Column filtering: all columns must be included in the queries or procedures that select and apply changes.
Updates to columns that are used for filtering: if a user updates a value in a column that is used for filtering, a row moves from one scope to another. The row is sent to the new scope that the row now belongs to, but the row is not deleted from the old scope.
Two scopes overlap if they share common data between them. For example, the table products could be included in a sales scope and an inventory scope. Scopes can be both overlapping and filtered. The following scenarios demonstrate ways in which filtering and overlap can occur:
Scenario 1:
Scope 1 is sales-WA. This scope includes: products; orders, with a filter of state=WA; and order_details, with a filter of state=WA.
Scope 2 is sales-OR. This scope includes: products; orders, with a filter of state=OR; and order_details, with a filter of state=OR.
In this scenario, the entire products table is shared by both scopes. The orders and order_details tables are in both scopes, but the filters do not overlap; therefore the scopes do not share rows from these tables.
Scenario 2:
Scope 1 is sales-WA. This scope includes: products; orders, with a filter of state=WA; and order_details, with a filter of state=WA.
Scope 2 is sales-Northwest. This scope includes: products; orders, with a filter of state=WA OR state=ID; and shippers.
In this scenario, the entire products table is again shared by both scopes. The orders table is in both scopes and the filters overlap: both scopes share the rows that satisfy the filter state=WA. The shippers and order_details tables are not shared between the scopes.
There are many different ways in which scopes can be defined, but the following principle must be followed: any data that is synchronized between a pair of databases in the synchronization topology can belong to only one scope. For example in Scenario 2 above, Database A and Database B could synchronize Scope 1; and Database A and Database C could synchronize Scope 2. Database A and Database B cannot also synchronize Scope 2 because of the products and orders rows that belong to both scopes.
Create Stored Procedures to Select and Update Data and Metadata
After you create metadata tables, create SQL queries or stored procedures (recommended) to select and apply changes to the base tables and metadata tables. Stored procedures are recommended for performance and security reasons. These queries or procedures are specified for the following DbSyncAdapter commands. These commands are described in "Synchronization Adapter" in How to: Synchronize Other ADO.NET Compatable Databases.
The following code examples create a set of stored procedures to handle data and metadata changes for the Sales.Customer table. For brevity, the procedures to select data and to handle updates are included, but those for inserts and deletes are not. For examples of insert and delete procedures, see Setup Scripts for Database Provider How-to Topics. For a template that enables you to more easily create all of these stored procedures, see Server Provisioning Template (Non-SQL Server).
In the complete code example at the end of this topic, many of the values that are passed to these procedures come from session variables. These are built-in variables that enable Sync Framework to pass values to commands during a synchronization session. For more information about session variables, see How to: Use Session Variables for Collaborative Synchronization (Non-SQL Server).
Conclusion
This topic walked you through provisioning a database for change tracking. After a database is provisioned, it can be synchronized with other nodes. For more information about how to configure and execute synchronization, see the following topics: How to: Synchronize Other ADO.NET Compatable Databases.