How to: Provision a Server Database for Collaborative Synchronization (Non-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:

1. Enable snapshot isolation for the database

2. Identify tables to synchronize

3. Create tracking tables to store per-table metadata; and create indexes on those tables

4. Create triggers on each base table to populate and update the tracking tables

5. (Optional) Handle existing data in the database

6. Create a tracking table to store per-scope metadata; and create an index on this table

7. Define scopes to synchronize, which specifies which tables are synchronized as a unit

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

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.

1. Row Q is updated at client A and then synchronized with the database.

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

3. 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:

1. Node1 and Node2 synchronize all changes.

2. Node1 synchronizes with Node3.

3. A user performs an update at Node2.

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

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.

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:

1. Add entries to the scope information table and to the scope mapping table, such as in the following code example.

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

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.

• SelectIncrementalChangesCommand

• InsertCommand

• UpdateCommand

• DeleteCommand

• InsertMetadataCommand

• UpdateMetadataCommand

• DeleteMetadataCommand

• SelectRowCommand

• SelectMetadataForCleanupCommand

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.

関連項目

概念

Synchronizing Other ADO.NET Compatible Databases

How to: Synchronize Other ADO.NET Compatable Databases