How to: Configure and Execute Collaborative Synchronization (SQL Server)
This topic describes the key parts of an application that uses Sync Framework to synchronize SQL Server and SQL Server Compact databases. The code in this application focuses on the following Sync Framework classes:
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.
As described in Architecture and Classes for Collaborative 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.
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.
Setting up Nodes and Executing Synchronization
Synchronizing nodes in a topology can be divided into two phases: setting up nodes to be synchronized, and actually executing synchronization between a pair of nodes. For SqlSyncProvider and SqlCeSyncProvider, setting up nodes consists of two tasks:
Defining what you want to synchronize
You define what to synchronize by describing one or more scopes. A scope is a set of tables, some or all of which can be filtered. Tables can already exist in the database or they can be described by using Sync Framework objects, and then generated at runtime when the underlying store is being synchronized. For more information, see "Understanding Scopes" later in this topic.
Important
After a scope has been synchronized for the first time, metadata associated with existing scopes should be dropped and re-created if a scope needs to be changed.
Provisioning databases for Sync Framework change-tracking
After the tables and scopes are described, you use Sync Framework objects to apply provisioning scripts to each node. The scripts create a change-tracking and change-application infrastructure that consists of metadata tables, triggers, and stored procedures.
After nodes have been provisioned, they can be synchronized. From the developer's point of view, setting synchronization options and calling Synchronize() is straightforward. Behind the scenes, Sync Framework uses the scope and table description information you specified to build a configuration object for each scope and for each synchronization adapter (one per table). This enables Sync Framework to take information that is persisted in each database and build the information it requires for each synchronization session between a pair of nodes. Both SqlSyncProvider and SqlCeSyncProvider are aware of the change-tracking tables and other objects created during provisioning, and they generate the required DbSyncAdapter objects automatically. This significantly reduces the code that is required to synchronize data using these providers.
The following table lists the classes that are used to set up databases and providers.
| SQL Server | SQL Server Compact | Description |
|---|---|---|
DbSyncScopeDescription |
DbSyncScopeDescription |
Represents a synchronization scope, which is a logical grouping of tables that are synchronized as a unit. |
SqlSyncScopeProvisioning |
Represents the provisioning of a SQL Server or SQL Server Compact database for a particular scope that is represented by a DbSyncScopeDescription object. |
|
Represents configuration information that is used by SqlSyncProvider or SqlCeSyncProvider for a particular scope. |
||
DbSyncTableDescription |
DbSyncTableDescription |
Represents the schema of a table that is included in a synchronization scope. |
DbSyncColumnDescription |
Represents the properties of a column that is part of a table that is included in a synchronization scope. |
|
SqlSyncDescriptionBuilder |
SqlCeSyncDescriptionBuilder |
Represents scope and table information for a SQL Server or SQL Server Compact database that is involved in synchronization. It is used to extract |
SqlSyncTableProvisioning |
Represents the provisioning of a SQL Server or SQL Server Compact database table (with optional filters) that is represented by a DbSyncTableDescription object. |
|
SqlSyncProviderAdapterConfiguration |
Represents synchronization adapter configuration information for a table in a SQL Server or SQL Server Compact database. |
In addition to these main types, there are four other important types to be aware of:
SqlSyncStoreMetadataCleanup and SqlCeSyncStoreMetadataCleanup enable you to clean up old metadata from the Sync Framework change-tracking tables. For more information, see How to: Clean Up Metadata for Collaborative Synchronization (SQL Server).
SqlCeSyncStoreSnapshotInitialization enables you to generate a snapshot of an existing SQL Server Compact database file that can be used to initialize another SQL Server Compact database for synchronization. For more information, see Provisioning the Clients in this topic.
SqlSyncStoreRestore enables you to update change-tracking metadata after a SQL Server database is restored from a backup, which is required before a restored database can be synchronized again. For more information, see How to: Backup and Restore a Database (SQL Server).
Understanding Scopes
Note
This section of the topic provides additional information about synchronization scopes. You can go straight to "Code Examples" at this point, but we recommend that you read this section if you plan to use filtered scopes or more than one scope in an application.
It is important to understand that a scope is the combination of tables and filters. For example, you could define a filtered scope named sales-WA that contains only the sales data for the state of Washington from the customer_sales table. If you define another filter on the same table, such as sales-OR, this is a different scope. If you define filters, be aware that Sync Framework does not automatically handle the deletion of rows that no longer satisfy a filter condition. For example, if a user or application 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. Your application must handle this situation.
Scopes can be distinct or they can overlap with each other. 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 ofstate=WA; andorder_details, with a filter ofstate=WA.Scope 2 is
sales-OR. This scope includes:products;orders, with a filter ofstate=OR; andorder_details, with a filter ofstate=OR.
In this scenario, the entire
productstable is shared by both scopes. Theordersandorder_detailstables 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 ofstate=WA; andorder_details, with a filter ofstate=WA.Scope 2 is
sales-Northwest. This scope includes:products;orders, with a filter ofstate=WA OR state=ID; andshippers.
In this scenario, the entire
productstable is again shared by both scopes. Theorderstable is in both scopes and the filters overlap: both scopes share the rows that satisfy the filterstate=WA. Theshippersandorder_detailstables 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.
Code Examples
The code examples in this section include many of the objects described above and cover the following areas:
Describing the scope and tables
Provisioning the server
Provisioning the clients
Setting synchronization options
Synchronizing the nodes
After we cover each of these areas, we provide a complete console application that combines each of these examples and some additional code to synchronize a four node topology. The topology consists of a SQL Server server, a SQL Server client, and two SQL Server Compact clients.
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 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.
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("filtered_customer");
// Definition for Customer.
DbSyncTableDescription customerDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn);
scopeDesc.Tables.Add(customerDescription);
// Definition for CustomerContact, including the list of columns to include.
Collection<string> columnsToInclude = new Collection<string>();
columnsToInclude.Add("CustomerId");
columnsToInclude.Add("PhoneType");
DbSyncTableDescription customerContactDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", columnsToInclude, serverConn);
scopeDesc.Tables.Add(customerContactDescription);
Dim scopeDesc As New DbSyncScopeDescription("filtered_customer")
' Definition for Customer.
Dim customerDescription As DbSyncTableDescription = _
SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn)
scopeDesc.Tables.Add(customerDescription)
' Definition for CustomerContact, including the list of columns to include.
Dim columnsToInclude As New Collection(Of String)()
columnsToInclude.Add("CustomerId")
columnsToInclude.Add("PhoneType")
Dim customerContactDescription As DbSyncTableDescription = _
SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", columnsToInclude, serverConn)
scopeDesc.Tables.Add(customerContactDescription)
Provisioning the Server
The following code example creates a provisioning object for the filtered_customer scope, specifies that base tables should not be created in the server database, 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 that filter will be synchronized. 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.
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverConfig.ObjectSchema = "Sync";
// Specify which column(s) in the Customer table to use for filtering data,
// and the filtering clause to use against the tracking table.
// "[side]" is an alias for the tracking table.
serverConfig.Tables["Sales.Customer"].AddFilterColumn("CustomerType");
serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = 'Retail'";
// Configure the scope and change-tracking infrastructure.
serverConfig.Apply(serverConn);
// Write the configuration script to a file. You can modify
// this script if necessary and run it against the server
// to customize behavior.
File.WriteAllText("SampleConfigScript.txt",
serverConfig.Script("SyncSamplesDb_SqlPeer1"));
Dim serverConfig As New SqlSyncScopeProvisioning(scopeDesc)
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip)
serverConfig.ObjectSchema = "Sync"
' Specify which column(s) in the Customer table to use for filtering data,
' and the filtering clause to use against the tracking table.
' "[side]" is an alias for the tracking table.
serverConfig.Tables("Sales.Customer").AddFilterColumn("CustomerType")
serverConfig.Tables("Sales.Customer").FilterClause = "[side].[CustomerType] = 'Retail'"
' Configure the scope and change-tracking infrastructure.
serverConfig.Apply(serverConn)
' Write the configuration script to a file. You can modify
' this script if necessary and run it against the server
' to customize behavior.
File.WriteAllText("SampleConfigScript.txt", serverConfig.Script("SyncSamplesDb_SqlPeer1"))
Provisioning the Clients
In this application, clients are 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. 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 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.
// Create a SQL Server Compact database and provision it based on scope
// information that is retrieved from the server. Compact databases
// do not support separate schemas, so we prefix the name of all
// synchronization-related objects with "Sync" so that they are easy to
// identify.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, true);
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, false);
DbSyncScopeDescription clientSqlCe1Desc = SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", null, "Sync", serverConn);
SqlCeSyncScopeProvisioning clientSqlCe1Config = new SqlCeSyncScopeProvisioning(clientSqlCe1Desc);
clientSqlCe1Config.ObjectPrefix = "Sync";
clientSqlCe1Config.Apply(clientSqlCe1Conn);
// Provision the existing database SyncSamplesDb_SqlPeer2 based on scope
// information that is retrieved from the SQL Server Compact database. We could
// have also retrieved this information from the server.
DbSyncScopeDescription clientSqlDesc = SqlCeSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", "Sync", clientSqlCe1Conn);
SqlSyncScopeProvisioning clientSqlConfig = new SqlSyncScopeProvisioning(clientSqlDesc);
clientSqlConfig.ObjectSchema = "Sync";
clientSqlConfig.Apply(clientSqlConn);
' Create a SQL Server Compact database and provision it based on scope
' information that is retrieved from the server. Compact databases
' do not support separate schemas, so we prefix the name of all
' synchronization-related objects with "Sync" so that they are easy to
' identify.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, True)
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, False)
Dim clientSqlCe1Desc As DbSyncScopeDescription = _
SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", Nothing, "Sync", serverConn)
Dim clientSqlCe1Config As New SqlCeSyncScopeProvisioning(clientSqlCe1Desc)
clientSqlCe1Config.ObjectPrefix = "Sync"
clientSqlCe1Config.Apply(clientSqlCe1Conn)
' Provision the existing database SyncSamplesDb_SqlPeer2 based on scope
' information that is retrieved from the SQL Server Compact database. We could
' have also retrieved this information from the server.
Dim clientSqlDesc As DbSyncScopeDescription = _
SqlCeSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", "Sync", clientSqlCe1Conn)
Dim clientSqlConfig As New SqlSyncScopeProvisioning(clientSqlDesc)
clientSqlConfig.ObjectSchema = "Sync"
clientSqlConfig.Apply(clientSqlConn)
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.
// Create a snapshot from the SQL Server Compact database, which will be used to
// initialize a second Compact database. Again, this database could be provisioned
// by retrieving scope information from another database, but we want to
// demonstrate the use of snapshots, which provide a convenient deployment
// mechanism for Compact databases.
SqlCeSyncStoreSnapshotInitialization syncStoreSnapshot = new SqlCeSyncStoreSnapshotInitialization("Sync");
syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf");
// The new SQL Server Compact client synchronizes with the server, but
// no data is downloaded because the snapshot already contains
// all of the data from the first Compact database.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("filtered_customer", serverConn, null, "Sync"),
new SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
' Create a snapshot from the SQL Server Compact database, which will be used to
' initialize a second Compact database. Again, this database could be provisioned
' by retrieving scope information from another database, but we want to
' demonstrate the use of snapshots, which provide a convenient deployment
' mechanism for Compact databases.
Dim syncStoreSnapshot As New SqlCeSyncStoreSnapshotInitialization("Sync")
syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf")
' The new SQL Server Compact client synchronizes with the server, but
' no data is downloaded because the snapshot already contains
' all of the data from the first Compact database.
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"), _
New SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")
Setting Synchronization Options
The following code example is a constructor from the SampleSyncOrchestrator class in the application. 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.
public SampleSyncOrchestrator(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
{
this.LocalProvider = localProvider;
this.RemoteProvider = remoteProvider;
this.Direction = SyncDirectionOrder.UploadAndDownload;
}
Public Sub New(ByVal localProvider As RelationalSyncProvider, ByVal remoteProvider As RelationalSyncProvider)
Me.LocalProvider = localProvider
Me.RemoteProvider = remoteProvider
Me.Direction = SyncDirectionOrder.UploadAndDownload
End Sub
Synchronizing the Nodes
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.
SampleSyncOrchestrator syncOrchestrator;
SyncOperationStatistics syncStats;
// Data is downloaded from the server to the SQL Server client.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync"),
new SqlSyncProvider("filtered_customer", serverConn, null, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
// Data is downloaded from the SQL Server client to the
// first SQL Server Compact client.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"),
new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
// Create a snapshot from the SQL Server Compact database, which will be used to
// initialize a second Compact database. Again, this database could be provisioned
// by retrieving scope information from another database, but we want to
// demonstrate the use of snapshots, which provide a convenient deployment
// mechanism for Compact databases.
SqlCeSyncStoreSnapshotInitialization syncStoreSnapshot = new SqlCeSyncStoreSnapshotInitialization("Sync");
syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf");
// The new SQL Server Compact client synchronizes with the server, but
// no data is downloaded because the snapshot already contains
// all of the data from the first Compact database.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("filtered_customer", serverConn, null, "Sync"),
new SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
Dim syncOrchestrator As SampleSyncOrchestrator
Dim syncStats As SyncOperationStatistics
' Data is downloaded from the server to the SQL Server client.
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"), _
New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")
' Data is downloaded from the SQL Server client to the
' first SQL Server Compact client.
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"), _
New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")
' Create a snapshot from the SQL Server Compact database, which will be used to
' initialize a second Compact database. Again, this database could be provisioned
' by retrieving scope information from another database, but we want to
' demonstrate the use of snapshots, which provide a convenient deployment
' mechanism for Compact databases.
Dim syncStoreSnapshot As New SqlCeSyncStoreSnapshotInitialization("Sync")
syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf")
' The new SQL Server Compact client synchronizes with the server, but
' no data is downloaded because the snapshot already contains
' all of the data from the first Compact database.
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"), _
New SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")
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.
using System;
using System.Collections.ObjectModel;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
// Create the connections over which provisioning and synchronization
// are performed. The Utility class handles all functionality that is not
// directly related to synchronization, such as holding connection
// string information and making changes to the server database.
SqlConnection serverConn = new SqlConnection(Utility.ConnStr_SqlSync_Server);
SqlConnection clientSqlConn = new SqlConnection(Utility.ConnStr_SqlSync_Client);
SqlCeConnection clientSqlCe1Conn = new SqlCeConnection(Utility.ConnStr_SqlCeSync1);
SqlCeConnection clientSqlCe2Conn = new SqlCeConnection(Utility.ConnStr_SqlCeSync2);
// Create a scope named "filtered_customer", and add two tables to the scope.
// GetDescriptionForTable gets the schema of each table, so that tracking
// tables and triggers can be created for that table. For Customer, we add
// the entire table. For CustomerContact, we add only two of the columns.
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("filtered_customer");
// Definition for Customer.
DbSyncTableDescription customerDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn);
scopeDesc.Tables.Add(customerDescription);
// Definition for CustomerContact, including the list of columns to include.
Collection<string> columnsToInclude = new Collection<string>();
columnsToInclude.Add("CustomerId");
columnsToInclude.Add("PhoneType");
DbSyncTableDescription customerContactDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", columnsToInclude, serverConn);
scopeDesc.Tables.Add(customerContactDescription);
// Create a provisioning object for "filtered_customer". We specify that
// base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1),
// and that all synchronization-related objects should be created in a
// database schema named "Sync". If you specify a schema, it must already exist
// in the database.
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverConfig.ObjectSchema = "Sync";
// Specify which column(s) in the Customer table to use for filtering data,
// and the filtering clause to use against the tracking table.
// "[side]" is an alias for the tracking table.
serverConfig.Tables["Sales.Customer"].AddFilterColumn("CustomerType");
serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = 'Retail'";
// Configure the scope and change-tracking infrastructure.
serverConfig.Apply(serverConn);
// Write the configuration script to a file. You can modify
// this script if necessary and run it against the server
// to customize behavior.
File.WriteAllText("SampleConfigScript.txt",
serverConfig.Script("SyncSamplesDb_SqlPeer1"));
// Provision each of the client databases.
// Create a SQL Server Compact database and provision it based on scope
// information that is retrieved from the server. Compact databases
// do not support separate schemas, so we prefix the name of all
// synchronization-related objects with "Sync" so that they are easy to
// identify.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, true);
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, false);
DbSyncScopeDescription clientSqlCe1Desc = SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", null, "Sync", serverConn);
SqlCeSyncScopeProvisioning clientSqlCe1Config = new SqlCeSyncScopeProvisioning(clientSqlCe1Desc);
clientSqlCe1Config.ObjectPrefix = "Sync";
clientSqlCe1Config.Apply(clientSqlCe1Conn);
// Provision the existing database SyncSamplesDb_SqlPeer2 based on scope
// information that is retrieved from the SQL Server Compact database. We could
// have also retrieved this information from the server.
DbSyncScopeDescription clientSqlDesc = SqlCeSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", "Sync", clientSqlCe1Conn);
SqlSyncScopeProvisioning clientSqlConfig = new SqlSyncScopeProvisioning(clientSqlDesc);
clientSqlConfig.ObjectSchema = "Sync";
clientSqlConfig.Apply(clientSqlConn);
// Initial synchronization sessions. 7 rows are synchronized:
// all rows (4) from CustomerContact, and the 3 rows from Customer
// that satisfy the filtering criteria.
SampleSyncOrchestrator syncOrchestrator;
SyncOperationStatistics syncStats;
// Data is downloaded from the server to the SQL Server client.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync"),
new SqlSyncProvider("filtered_customer", serverConn, null, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
// Data is downloaded from the SQL Server client to the
// first SQL Server Compact client.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"),
new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
// Create a snapshot from the SQL Server Compact database, which will be used to
// initialize a second Compact database. Again, this database could be provisioned
// by retrieving scope information from another database, but we want to
// demonstrate the use of snapshots, which provide a convenient deployment
// mechanism for Compact databases.
SqlCeSyncStoreSnapshotInitialization syncStoreSnapshot = new SqlCeSyncStoreSnapshotInitialization("Sync");
syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf");
// The new SQL Server Compact client synchronizes with the server, but
// no data is downloaded because the snapshot already contains
// all of the data from the first Compact database.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("filtered_customer", serverConn, null, "Sync"),
new SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
// Make changes on the server: 1 insert, 1 update, and 1 delete.
Utility.MakeDataChangesOnNode(Utility.ConnStr_SqlSync_Server, "Customer");
// Synchronize again. Three changes were made on the server, but
// only two of them applied to rows that are in the "filtered_customer"
// scope. The other row is not synchronized.
// Notice that the order of synchronization is different from the initial
// sessions, but the two changes are propagated to all nodes.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"),
new SqlSyncProvider("filtered_customer", serverConn, null, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "subsequent");
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync"),
new SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "subsequent");
syncOrchestrator = new SampleSyncOrchestrator(
new SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync"),
new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync")
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "subsequent");
serverConn.Close();
serverConn.Dispose();
clientSqlConn.Close();
clientSqlConn.Dispose();
clientSqlCe1Conn.Close();
clientSqlCe1Conn.Dispose();
clientSqlCe2Conn.Close();
clientSqlCe2Conn.Dispose();
Utility.CleanUpSqlNode(Utility.ConnStr_SqlSync_Server);
Utility.CleanUpSqlNode(Utility.ConnStr_SqlSync_Client);
Console.Write("\nPress any key to exit.");
Console.Read();
}
}
public class SampleSyncOrchestrator : SyncOrchestrator
{
public SampleSyncOrchestrator(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
{
this.LocalProvider = localProvider;
this.RemoteProvider = remoteProvider;
this.Direction = SyncDirectionOrder.UploadAndDownload;
}
public void DisplayStats(SyncOperationStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Uploaded: " + syncStatistics.UploadChangesTotal);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.DownloadChangesTotal);
Console.WriteLine("Complete Time: " + syncStatistics.SyncEndTime);
Console.WriteLine(String.Empty);
}
}
}
Imports System
Imports System.Collections.ObjectModel
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.SqlServer
Imports Microsoft.Synchronization.Data.SqlServerCe
Namespace Microsoft.Samples.Synchronization
Class Program
Public Shared Sub Main(ByVal args As String())
' Create the connections over which provisioning and synchronization
' are performed. The Utility class handles all functionality that is not
' directly related to synchronization, such as holding connection
' string information and making changes to the server database.
Dim serverConn As New SqlConnection(Utility.ConnStr_SqlSync_Server)
Dim clientSqlConn As New SqlConnection(Utility.ConnStr_SqlSync_Client)
Dim clientSqlCe1Conn As New SqlCeConnection(Utility.ConnStr_SqlCeSync1)
Dim clientSqlCe2Conn As New SqlCeConnection(Utility.ConnStr_SqlCeSync2)
' Create a scope named "filtered_customer", and add two tables to the scope.
' GetDescriptionForTable gets the schema of each table, so that tracking
' tables and triggers can be created for that table. For Customer, we add
' the entire table. For CustomerContact, we add only two of the columns.
Dim scopeDesc As New DbSyncScopeDescription("filtered_customer")
' Definition for Customer.
Dim customerDescription As DbSyncTableDescription = _
SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn)
scopeDesc.Tables.Add(customerDescription)
' Definition for CustomerContact, including the list of columns to include.
Dim columnsToInclude As New Collection(Of String)()
columnsToInclude.Add("CustomerId")
columnsToInclude.Add("PhoneType")
Dim customerContactDescription As DbSyncTableDescription = _
SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", columnsToInclude, serverConn)
scopeDesc.Tables.Add(customerContactDescription)
' Create a provisioning object for "filtered_customer". We specify that
' base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1),
' and that all synchronization-related objects should be created in a
' database schema named "Sync". If you specify a schema, it must already exist
' in the database.
Dim serverConfig As New SqlSyncScopeProvisioning(scopeDesc)
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip)
serverConfig.ObjectSchema = "Sync"
' Specify which column(s) in the Customer table to use for filtering data,
' and the filtering clause to use against the tracking table.
' "[side]" is an alias for the tracking table.
serverConfig.Tables("Sales.Customer").AddFilterColumn("CustomerType")
serverConfig.Tables("Sales.Customer").FilterClause = "[side].[CustomerType] = 'Retail'"
' Configure the scope and change-tracking infrastructure.
serverConfig.Apply(serverConn)
' Write the configuration script to a file. You can modify
' this script if necessary and run it against the server
' to customize behavior.
File.WriteAllText("SampleConfigScript.txt", serverConfig.Script("SyncSamplesDb_SqlPeer1"))
' Provision each of the client databases.
' Create a SQL Server Compact database and provision it based on scope
' information that is retrieved from the server. Compact databases
' do not support separate schemas, so we prefix the name of all
' synchronization-related objects with "Sync" so that they are easy to
' identify.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, True)
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, False)
Dim clientSqlCe1Desc As DbSyncScopeDescription = _
SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", Nothing, "Sync", serverConn)
Dim clientSqlCe1Config As New SqlCeSyncScopeProvisioning(clientSqlCe1Desc)
clientSqlCe1Config.ObjectPrefix = "Sync"
clientSqlCe1Config.Apply(clientSqlCe1Conn)
' Provision the existing database SyncSamplesDb_SqlPeer2 based on scope
' information that is retrieved from the SQL Server Compact database. We could
' have also retrieved this information from the server.
Dim clientSqlDesc As DbSyncScopeDescription = _
SqlCeSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", "Sync", clientSqlCe1Conn)
Dim clientSqlConfig As New SqlSyncScopeProvisioning(clientSqlDesc)
clientSqlConfig.ObjectSchema = "Sync"
clientSqlConfig.Apply(clientSqlConn)
' Initial synchronization sessions. 7 rows are synchronized:
' all rows (4) from CustomerContact, and the 3 rows from Customer
' that satisfy the filtering criteria.
Dim syncOrchestrator As SampleSyncOrchestrator
Dim syncStats As SyncOperationStatistics
' Data is downloaded from the server to the SQL Server client.
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"), _
New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")
' Data is downloaded from the SQL Server client to the
' first SQL Server Compact client.
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"), _
New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")
' Create a snapshot from the SQL Server Compact database, which will be used to
' initialize a second Compact database. Again, this database could be provisioned
' by retrieving scope information from another database, but we want to
' demonstrate the use of snapshots, which provide a convenient deployment
' mechanism for Compact databases.
Dim syncStoreSnapshot As New SqlCeSyncStoreSnapshotInitialization("Sync")
syncStoreSnapshot.GenerateSnapshot(clientSqlCe1Conn, "SyncSampleClient2.sdf")
' The new SQL Server Compact client synchronizes with the server, but
' no data is downloaded because the snapshot already contains
' all of the data from the first Compact database.
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"), _
New SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")
' Make changes on the server: 1 insert, 1 update, and 1 delete.
Utility.MakeDataChangesOnNode(Utility.ConnStr_SqlSync_Server, "Customer")
' Synchronize again. Three changes were made on the server, but
' only two of them applied to rows that are in the "filtered_customer"
' scope. The other row is not synchronized.
' Notice that the order of synchronization is different from the initial
' sessions, but the two changes are propagated to all nodes.
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"), _
New SqlSyncProvider("filtered_customer", serverConn, Nothing, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "subsequent")
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"), _
New SqlCeSyncProvider("filtered_customer", clientSqlCe1Conn, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "subsequent")
syncOrchestrator = New SampleSyncOrchestrator( _
New SqlCeSyncProvider("filtered_customer", clientSqlCe2Conn, "Sync"), _
New SqlSyncProvider("filtered_customer", clientSqlConn, Nothing, "Sync"))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "subsequent")
serverConn.Close()
serverConn.Dispose()
clientSqlConn.Close()
clientSqlConn.Dispose()
clientSqlCe1Conn.Close()
clientSqlCe1Conn.Dispose()
clientSqlCe2Conn.Close()
clientSqlCe2Conn.Dispose()
Utility.CleanUpSqlNode(Utility.ConnStr_SqlSync_Server)
Utility.CleanUpSqlNode(Utility.ConnStr_SqlSync_Client)
Console.Write(vbLf & "Press any key to exit.")
Console.Read()
End Sub
End Class
Public Class SampleSyncOrchestrator
Inherits SyncOrchestrator
Public Sub New(ByVal localProvider As RelationalSyncProvider, ByVal remoteProvider As RelationalSyncProvider)
Me.LocalProvider = localProvider
Me.RemoteProvider = remoteProvider
Me.Direction = SyncDirectionOrder.UploadAndDownload
End Sub
Public Sub DisplayStats(ByVal syncStatistics As SyncOperationStatistics, ByVal syncType As String)
Console.WriteLine([String].Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Uploaded: " & syncStatistics.UploadChangesTotal)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.DownloadChangesTotal)
Console.WriteLine("Complete Time: " & syncStatistics.SyncEndTime)
Console.WriteLine([String].Empty)
End Sub
End Class
End Namespace
See Also
Concepts
Overview of Collaborative Synchronization
Architecture and Classes for Collaborative Synchronization