How to: Execute Database Synchronization (SQL Server)
This topic describes how to execute synchronization between a SQL Server and SQL Compact and provides examples. For more information about how to run sample code, see "Example Applications in the How to Topics" in Synchronizing SQL Server and SQL Server Compact.
Note
As described in Architecture and Classes for Database Synchronization, synchronization can occur between two instances of SqlSyncProvider, 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.
Executing Synchronization
To execute synchronization, you need to start with creating a SyncOrchestrator object and setting synchronization options such as LocalProvider, RemoteProvider, and Direction on the object.
Then, you can kick off synchronization process by invoking the Synchronize method on the SyncOrchestrator object.
The following code example instantiates providers for three different synchronization sessions: between the server and the SQL Server client; between the SQL Server client and one of the SQL Server Compact clients; and between the server and the other SQL Server Compact client. Seven rows are synchronized during the first two sessions: all four rows from CustomerContact, and the three rows from Customer that satisfy the filtering criteria. Rows are not synchronized in the third session because the snapshot already contains all of the data from the first SQL Server Compact database. The application displays statistics that the Synchronize() method returns.
This code sample use the SampleSyncOrchestrator class, which is a subclass of SyncOrchestrator class that is included in Sync Framework SDK. Here is a constructor for the SampleSyncOrchestration class that takes two parameters. The constructor takes two RelationalSyncProvider objects because SqlSyncProvider and SqlCeSyncProvider both derive from RelationalSyncProvider. 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.
Important
When a node is added to a synchronization community, its first synchronization must complete before the new node synchronizes with other nodes in the community. Otherwise, the new node might be detected as out-of-date.
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.
Comparing Provider Types
This topic describes how to synchronize SQL Server and SQL Server Compact databases by using the two synchronization providers that were introduced in Sync Framework 2.0: SqlSyncProvider and SqlCeSyncProvider. Sync Framework includes other providers that can synchronize these databases, but the new providers are generally better-suited to the task for the following reasons:
SqlSyncProvider is as capable as DbSyncProvider, but it requires significantly less code and less understanding of the queries that Sync Framework uses to synchronize data. DbSyncProvider is still appropriate for non-SQL Server databases.
SqlSyncProvider and SqlCeSyncProvider can be used for client-server, peer-to-peer, and mixed topologies, whereas DbServerSyncProvider and SqlCeClientSyncProvider are appropriate only for client-server topologies. SqlSyncProvider and SqlCeSyncProvider also support more advanced features, such as batching of changes based on the size of the data instead of the number of rows.
SqlSyncProvider and SqlCeSyncProvider are flexible and easy to set up. They enable you to synchronize all editions of SQL Server, including SQL Server Express and SQL Server Compact.