How to: Synchronize Other ADO.NET Compatable Databases

Article
07/07/2014

Note

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 the key parts of an application that uses Sync Framework to synchronize several databases. The code in this application focuses on the following Sync Framework classes:

For information about how to run sample code, see "Example Applications in the How to Topics" in Synchronizing Other ADO.NET Compatible Databases.

As described in Architecture and Classes for Database Synchronization, synchronization can occur between two instances of DbSyncProvider, two instances of SqlCeSyncProvider, or one instance of each. The example code in this topic is from a two-tier application; therefore it does not demonstrate synchronizing two instances of SqlCeSyncProvider, which requires an n-tier configuration. For an example of an n-tier configuration, see the sample WebSharingAppDemo-CEProviderEndToEnd that is included with the Sync Framework SDK.

Configuring synchronization involves the following steps:

Creating tracking tables to store metadata, and stored procedures to update data and metadata in each server database. For more information, see How to: Provision a Server Database for Collaborative Synchronization (Non-SQL Server).
Initializing each server database with schema, data, and change tracking infrastructure.

Executing synchronization involves the following steps:

Creating server synchronization adapters and a synchronization provider, and client synchronization provider.
Initializing each client database.
Creating a synchronization orchestrator, and synchronizing the nodes.

Initializing a Server Database

Initializing a database involves copying to each database the table schema and change tracking infrastructure, and any initial data that is required. For databases that are synchronized by using DbSyncProvider, Sync Framework does not automatically create table schema or tracking infrastructure in each database. An application must ensure that these objects exist before it tries to synchronize nodes. You can use backup and restore or another technology to copy the objects to each node, but you should do so only if changes are not occurring in the database from which the backup is taken. If the first database is in use and being updated, we strongly recommend that you copy only schema and change tracking infrastructure to each node, and use Sync Framework to copy the data. If you copy the data by using another method, a node might miss changes that have been committed at the first node. Sync Framework can initialize the data at each node as long as at least one node has the data. The example code in this topic uses this approach: each database contains two tables, but only the tables in SyncSamplesDb_Peer1 contain data. The data is copied to the other nodes during the first synchronization session.

For information about the initialization options for SqlCeSyncProvider, see Initializing a Client Database later in this topic.

Executing Synchronization

The code examples in this section are divided into the following categories:

The DbSyncAdapter object and commands for each table that will be synchronized in the server database.
The DbSyncProvider object and commands for the server.
The SqlCeSyncProvider object for the client.
The SyncSchema object and GenerateSnapshot(SqlCeConnection, String) method, which are used to initialize the client database.
The SyncOrchestrator object, which is used to synchronize the server and client databases.

Synchronization Adapter

The following properties are set on the DbSyncAdapter object for each table.

Synchronization adapter property	Usage
RowIdColumns	Specifies the primary key columns for the table. For example, if a Customer table has a primary key of CustomerId, the code would be similar to the following: adapter.RowIdColumns.Add(CustomerId).
SelectIncrementalChangesCommand	Select all changes since the previous synchronization session by joining the base table and its change-tracking table. This is the maximum set of changes that could be synchronized. This set of changes might be reduced when synchronization knowledge is accounted for.
InsertCommand, UpdateCommand, and DeleteCommand	Apply to a node the inserts, updates, and deletes that were selected from another node. These changes were selected by using the query or procedure that you specified for the SelectIncrementalChangesCommand property.
InsertMetadataCommand, UpdateMetadataCommand, and DeleteMetadataCommand	Update the change-tracking tables at a node to reflect the changes that were selected from the node and applied to another node. These updates enable Sync Framework to track where and when changes occurred.
SelectRowCommand	Select metadata for a row that is in conflict during synchronization.
SelectMetadataForCleanupCommand	Select metadata that can be cleaned up at a node. Cleanup is typically retention-based: metadata is kept for a specific time. However, an application can use other logic to determine when to clean up metadata. For more information, see How to: Clean Up Metadata for Collaborative Synchronization (Non-SQL Server).

The code examples in this section create commands that the DbSyncAdapter object executes during synchronization. These commands call the stored procedures that are described in How to: Provision a Server Database for Collaborative Synchronization (Non-SQL Server). The full set of commands is included in the complete code example at the end of this topic. In the code, parameters in the form "@" + DbSyncSession are session variables. For example, "@" + DbSyncSession.SyncMinTimestamp is a variable that resolves to a value of @sync\_min\_timestamp. For more information, see How to: Use Session Variables for Collaborative Synchronization (Non-SQL Server).

Application Code for SelectRowCommand

After all the commands are defined, the adapter is added to the provider by using the following code.

sampleProvider.SyncAdapters.Add(adapterCustomer);

Server Synchronization Provider

The following properties are set on the DbSyncProvider object for the server.

Synchronization provider property	Usage
ScopeName	The name of the scopes to synchronize for a particular session. Each scope should already exist in the scope information table in the server database.
Connection	The connection over which Sync Framework selects and applies changes to the server database.
SelectNewTimestampCommand	Returns a timestamp value that is used to select and apply sets of changes to each database. During the current synchronization session, the command provides a new timestamp value. Changes that are made after the timestamp value from the previous synchronization session and before the new timestamp value are synchronized. The new value is then stored and used as the starting point for the next session.
SelectTableMaxTimestampsCommand	Selects the maximum timestamp from each base table or tracking table, to determine whether for each table the destination already has all of the changes from the source. If the destination already has the changes, Sync Framework can often avoid running enumeration queries, which can improve performance. This property is optional; if a command is not specified, enumeration queries are executed for all tables.
SelectScopeInfoCommand	Returns information from the scope information table, such as the synchronization knowledge and cleanup knowledge that Sync Framework requires.
UpdateScopeInfoCommand	Updates information in the scope information table.
SelectOverlappingScopesCommand	Returns the scope name and table name for all tables in the specified scope that are also included in other scopes.
UpdateScopeCleanupTimestampCommand	Updates the scope_cleanup_timestamp column for a particular scope in the scope information table, to mark the point up to which cleanup has been performed for the scope. For more information, see How to: Clean Up Metadata for Collaborative Synchronization (Non-SQL Server).

The code examples in this section create commands that the DbSyncProvider object executes during synchronization. The commands for the SelectScopeInfoCommand and UpdateScopeInfoCommand properties are included in the complete code example at the end of this topic.

Application Code for ScopeName and Connection

The following code example sets the name of the scope to synchronize, and the connection over which to synchronize. In the complete code example, the DbSyncProvider object contains a DbSyncAdapter object for the table Customer. When this table is synchronized for the first time, the definition of the Sales scope is set. 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.

For more information about scopes, see "Define Scopes to Synchronize" in How to: Provision a Server Database for Collaborative Synchronization (Non-SQL Server).

Application Code for SelectNewTimestampCommand

The following code example creates a command for the SelectNewTimestampCommand property.

Application Code for SelectTableMaxTimestampsCommand

The following code example creates a command for the SelectTableMaxTimestampsCommand property. In the complete code example, there are synchronization sessions in which no new changes have been made at either node. In these sessions, the call to SelectTableMaxTimestampsCommand indicates that no data changes are available to synchronize, so SelectIncrementalChangesCommand is not called.

Client Synchronization Provider

The following code example creates a SqlCeSyncProvider object, sets the scope and connection, and registers event handlers. For the SqlCeSyncProvider, adapters are private and their commands are created automatically based on the schema that is initialized in the client database.

Initializing a Client Database

Before a SQL Server Compact client database can begin to receive changes from another database, the client database must contain table schema and any initial data, as well as the change-tracking infrastructure that Sync Framework requires. Sync Framework provides two ways to initialize the database:

Full initialization of the client database by using the server database

This enables tables to be created in the client database by using the tables, columns, and type description tables exposed by a DbSyncProvider object that connects to a server database instance. The client database is then prepared for synchronization, and all rows from the server database are downloaded as incremental inserts. The inserts are selected from the server by using the query or procedure that you specify for the SelectIncrementalChangesCommand property.
Snapshot initialization of the client database by using a pre-existing client database

Snapshot initialization is designed to reduce the time required to initialize a client database. After one client database has been initialized by using full initialization, subsequent databases can be initialized by using a snapshot of this first client database. A snapshot is a specially-prepared SQL Server Compact database that contains table schema, data (optional), and change-tracking infrastructure. Copy this snapshot to each client that requires it. During the first synchronization session for a client, client-specific metadata is updated, and any changes that occurred since the snapshot was created are downloaded to the client database.

Important

Snapshots should be generated only when there is no activity in the SQL Server Compact database. Concurrent operations of any type are not supported during snapshot generation.

The following code example performs full initialization. The code checks if a client database requires a schema each time synchronization occurs. If the provider has a connection to a client database that has already been initialized or is a snapshot database, the ScopeExists(String, SqlCeConnection) method returns true. If the method returns false, a schema is retrieved and applied to the client database by using the GetScopeDescription method.

The following code example generates a snapshot. The code connects to a SQL Server Compact database that was just synchronized by using the sample's ConfigureCESyncProvider method. After connecting, the GenerateSnapshot(SqlCeConnection, String) method is called, which creates a copy of the original database. When the client provider subsequently connects to this new copy and synchronization is executed, the database is initialized.

Note

In this example, the snapshot database is generated while the main application is running. In many cases, snapshots are generated at off-peak hours separately from other synchronization activity.

Creating a Synchronization Orchestrator and Synchronizing Databases

The code described in the previous sections of this topic demonstrates how to set the properties necessary for synchronization. Now it is time to synchronize nodes. Nodes are always synchronized as pairs, such as SyncSamplesDb_Peer1 and SyncSampleCe2. For a production application, a copy of the application would typically be deployed to each node so that synchronization could be initiated from any of the nodes.

The following code example creates a SampleSyncAgent class that derives from SyncOrchestrator. The SampleSyncAgent constructor takes two RelationalSyncProvider objects. Because DbSyncProvider and SqlCeSyncProvider both derive from RelationalSyncProvider, a SampleSyncAgent object can synchronize any combination of the two provider types. The code specifies which provider is the local provider and which is the remote provider. It then specifies that changes are first uploaded from the remote database to the local database and then downloaded in the other direction. The code checks if either of the providers are SqlCeSyncProvider objects that require schema initialization for a client database.

The following code sets up synchronization sessions by calling either ConfigureDbSyncProvider or ConfigureCeSyncProvider, methods that the sample application uses to set the appropriate provider properties for each session. The code then calls the Synchronize method of SampleSyncAgent to synchronize each pair of databases.

Complete Code Example

The following complete code example includes the code examples that are described earlier and additional code to display synchronization statistics and event information. The example requires the Utility class that is available in Utility Class for Database Provider How-to Topics.