Microsoft Sync Framework is a flexible, comprehensive synchronization platform that can be leveraged to synchronize data between two or more data stores. The Sync Framework can be used to synchronize any type of data residing in any data store using any protocol or network topology. Microsoft states: “Microsoft Sync Framework is a comprehensive synchronization platform that enables collaboration and offline scenarios for applications, services, and devices. Using Microsoft Sync Framework, developers can build applications that synchronize data from any source using any protocol over any network.”
This article examines how the Sync framework can be used to synchronize data between two databases.
Getting Started
To work with the code examples illustrated in this article, have the following installed in your system:
- Visual Studio 2017
- SQL Server 2016 or 2017
- Microsoft Sync Framework SDK
Note that even if you can work with Microsoft Sync Framework using the earlier versions of Visual Studio and SQL Server, it's always advisable that you use the latest versions of Visual Studio and SQL Server. You can download a copy of SQL Server from here: https://www.microsoft.com/en-gb/sql-server/sql-server-downloads.
If you don't have Visual Studio installed in your system, you can download a free copy of it from here: https://visualstudio.microsoft.com/downloads/
To get started using Microsoft Sync Framework in your application, you should download and install a copy of the Microsoft Sync Framework 2.1 Software Development Kit (SDK).
Core Components of the Sync Framework
The Sync Framework is protocol- and database-independent and can be used to synchronize data irrespective of the network protocol in use. The salient features of the Microsoft Sync Framework include the following:
- Data store and protocol independent
- Built-in support for filters and data conflict resolution
- Support for file systems, databases, and SSEs
Microsoft Sync Framework consists mainly of the following components, as seen in Figure 1:
- A sync runtime
- Participants
- Synchronization providers
- Metadata services
Let's examine the core components.
Sync Runtime
The sync runtime provides the necessary infrastructure for synchronizing data between data sources.
Participants
A participant is the location where the data to be synchronized resides. Participants can be of three types: full participants, partial participants, and simple participants.
Synchronization Providers
A synchronization provider is a component that can participate in a synchronization process. The Microsoft Sync Framework ships with a few built-in providers that can be leveraged to synchronize data residing in flat files, RSS, and atom feeds and ADO.NET-enabled databases. Sync Framework includes database synchronization providers, which can be leveraged to build Occasionally Connected Systems (OCAs). The database synchronization providers available as part of the Sync Framework can be used to synchronize data between ADO.NET-enabled databases. An ADO.NET-enabled database is one that has a corresponding ADO.NET Data Provider.
The built-in providers in Microsoft Sync Framework include the following:
- Sync Services for ADO.NET: Provides synchronization for ADO.NET-enabled data sources
- Sync Services for File Systems: Provides synchronization for files and folders
- Sync Services for Feed Sync: Provides synchronization for RSS and Atom feeds
Metadata Services
When data residing in two data sources need to be synchronized, the Sync Framework establishes a Sync Session, as shown in Figure 2.
A data source refers to the location where the data to be synchronized is stored. A data source can be any of the following:
- A relational database
- A Web service
- RSS or Atom feeds
- A custom data source
The Sync Framework stores the state and change information about a data store in metadata. This metadata can in turn reside in a flat file, a database, or within the data source being synchronized. Metadata of a data store includes the following:
- Version
- Knowledge
- Tick count
- Replica ID
- Tombstone
Note that you can also take advantage of Sync Framework to build your own custom Sync Provider.
Programming the Microsoft Sync Framework
Let's look at how to synchronize data between two SQL Server databases. For the sake of simplicity, assume that both of the databases reside in the same system. I'll use a Console application in this example to write the code to synchronize data between the two databases. To create a new Console application in Visual Studio 2017, follow these steps:
- Open the Visual Studio 2017 IDE.
- To create a new project, click on File > New > Project.
- Select Console App (.NET Framework) from the list of project templates displayed.
- Specify a name and location for the project.
- Select the Framework version you would like to use for this project.
- Save the project by clicking OK.
You'll use this application to write the code for synchronizing data using Microsoft Sync Framework. I'll come back to this later.
Creating the Source and Destination Databases
Let's now create the source and destination databases. To do this:
- Launch the Microsoft SQL Server Management Studio.
- In the Object Explorer Window, select Databases.
- Right click and select New database….
- Specify a name for the database and click OK.
Repeat the above steps to create the source and the destination databases. Name the databases ServerDB (the destination database) and ClientDB (the source database).
Next, create a table called Products in both the databases. You can use the script in the following snippet:
CREATE TABLE [dbo].[Products](
[ID] [int] NOT NULL,
[Code] [nvarchar](50) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Price] [money] NOT NULL
CONSTRAINT [PK_Products]
PRIMARY KEY CLUSTERED
([ID] ASC)
)
GO
Next, use the script in Listing 1 to insert records in the Products table pertaining to the ServerDB database.
Listing 1: Insert recoeds into the Products table
INSERT INTO Products VALUES
(1, 'Laptop-001', 'Lenovo Z51', 64000)
INSERT INTO Products VALUES
(2, 'Laptop-002', 'DELL Inspiron', 75000)
INSERT INTO Products VALUES
(3, 'Laptop-003', 'Asus VivoBook', 85000)
INSERT INTO Products VALUES
(4, 'Laptop-004', 'HP 15', 45000)
In the Products table of the ClientDB database, use the following script to insert a few records.
INSERT INTO Products VALUES
(1, 'Mobile-001', 'Samsung A9', 40000)
INSERT INTO Products VALUES
(2, 'Mobile-002', 'Samsung C9', 45000)
INSERT INTO Products VALUES
(3, 'Mobile-003', 'HTC', 35000)
You can run a select query to verify that the records have been properly inserted in the Products table of the ServerDB and ClientDB databases, as shown in Figure 3.
So far, so good. In the section that follows, I'll use Microsoft Sync Framework to synchronize data between the Products table of these two databases.
Building the Data Synchronizer
Refer to the Console application you created earlier. Open the Program.cs file and add references to the following assemblies:
- Microsoft.Synchronization.dll
- Microsoft.Synchronization.Data.dll
- Microsoft.Synchronization.Data.SqlServer.dll
Now, write the code from Listing 2 into the Main method of the Program class.
Listing 2: Specify the source and destination database connection strings
class Program
{
static void Main(string[] args)
{
string serverConnectionString = @"Data Source=JOYDIP\SQLEXPRESS;
Initial Catalog=ServerDB;
Trusted_Connection=Yes";
string clientConnectionString = @"Data Source=JOYDIP\SQLEXPRESS;
Initial Catalog=ClientDB;
Trusted_Connection=Yes";
string tableName = "Products";
DataSynchronizer.Synchronize (tableName, serverConnectionString,
clientConnectionString);
Console.WriteLine("Databases synchronized...");
Console.Read();
}
}
Notice how the connection strings for the source and the destination databases have been specified. To synchronize data between the Products tables of the source and the destination databases, the Synchronize method of the DataSynchronizer class is called. Note that DataSynchronizer is a custom class; you'll implement it shortly.
Next, create a new class and add the code in Listing 3 to it. Note the usage of the static keyword in the class declaration. In the Listing 3, only the public version of the Synchronize method has been implemented. Note the use of SyncDirectionOrder in Listing 3. This indicates the direction of synchronization. In this example, you use SyncDirectionOrder.DownloadAndUpload as the synchronization direction.
Listing 3: Specify the synchronization direction
public static class DataSynchronizer
{
private static void Initialize (
string table,
string serverConnectionString,
string clientConnectionString
)
{
throw new NotImplementedException();
}
public static void Synchronize(
string tableName,
string serverConnectionString,
string clientConnectionString)
{
Initialize(
tableName,
serverConnectionString,
clientConnectionString
);
Synchronize(
tableName,
serverConnectionString,
clientConnectionString,
SyncDirectionOrder.DownloadAndUpload
);
CleanUp(
tableName,
serverConnectionString,
clientConnectionString
);
}
private static void Synchronize(
string scopeName,
string serverConnectionString,
string clientConnectionString,
SyncDirectionOrder syncDirectionOrder
)
{
throw new NotImplementedException();
}
private static void Deprovision(
string scopeName,
string serverConnectionString,
string clientConnectionString
)
{
throw new NotImplementedException();
}
}
The DataSynchronizer static class contains four methods:
- Initialize: Used to make the databases ready for synchronization
- Synchronize (public): Used to invoke the
Initialize,Synchronize (private), and theCleanUpmethods in this order - Synchronize (private): Used to perform the actual synchronization
- CleanUp: Used to deprovision the server and the client and remove all synchronization objects
The Initialize method creates SqlSyncProvisioning instances for both the server and the client, as shown in Listing 4. The SqlSyncDescriptionBuilder.GetDescriptionForTable method is called to retrieve the schema information of the database table passed to it as an argument. In this example, the table name is Products.
Listing 4: Create SqlSyncProvisioning instances
private static void Initialize(
string table,
string serverConnectionString,
string clientConnectionString
)
{
using (SqlConnection serverConnection = new SqlConnection(serverConnectionString))
{
using (SqlConnection clientConnection = new SqlConnection(clientConnectionString))
{
DbSyncScopeDescription scopeDescription = new DbSyncScopeDescription(table);
DbSyncTableDescription tableDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable(table,
serverConnection);
scopeDescription.Tables.Add(tableDescription);
SqlSyncScopeProvisioning serverProvision = new
SqlSyncScopeProvisioning(serverConnection,
scopeDescription);
serverProvision.Apply();
SqlSyncScopeProvisioning clientProvision = new
SqlSyncScopeProvisioning(clientConnection, scopeDescription);
clientProvision.Apply();
}
}
}
The private Synchronize method performs the synchronization between the source and the destination databases. Note how an instance of SyncOrchestrator is created and the LocalProvider and RemoteProvider properties are initialized. Lastly, the Synchronize method is called on the instance of the SyncOrchestrator in Listing 5.
Listing 5: Call the Synchronize method
private static void Synchronize(
string scopeName,
string serverConnectionString,
string clientConnectionString,
SyncDirectionOrder
syncDirectionOrder
)
{
using (SqlConnection serverConnection =
new SqlConnection(serverConnectionString))
{
using (SqlConnection clientConnection =
new SqlConnection(clientConnectionString))
{
var agent = new SyncOrchestrator
{
LocalProvider = new
SqlSyncProvider(scopeName, clientConnection),
RemoteProvider = new
SqlSyncProvider(scopeName, serverConnection),
Direction = syncDirectionOrder
};
(agent.RemoteProvider as
RelationalSyncProvider).SyncProgress += new
EventHandler<DbSyncProgressEventArgs>
(dbProvider_SyncProgress);
(agent.LocalProvider as
RelationalSyncProvider).ApplyChangeFailed += new
EventHandler<DbApplyChangeFailedEventArgs>
(dbProvider_SyncProcessFailed);
(agent.RemoteProvider as
RelationalSyncProvider).ApplyChangeFailed += new
EventHandler<DbApplyChangeFailedEventArgs>
(dbProvider_SyncProcessFailed);
agent.Synchronize();
}
}
}
The Synchronize method of the SyncOrchestrator class returns a SyncOperationStatistics instance. If the synchronization is successful, this instance is populated with the applied changes. You can register event handlers to retrieve this information, both if the synchronization process is a success and if it's a failure. The following code snippet illustrates how you can attach event handlers to achieve this.
(syncOrchestrator.RemoteProvider as RelationalSyncProvider).SyncProgress
+= new EventHandler<DbSyncProgressEventArgs> (dbProvider_SyncProgress);
(syncOrchestrator.LocalProvider as RelationalSyncProvider).ApplyChangeFailed
+= new EventHandler <DbApplyChangeFailedEventArgs>(dbProvider_SyncProcessFailed);
(syncOrchestrator.RemoteProvider as RelationalSyncProvider).ApplyChangeFailed
+= new EventHandler <DbApplyChangeFailedEventArgs>(dbProvider_SyncProcessFailed);
The type of conflicts that can occur during synchronization process are represented using the DbConflict enum pertaining to the Microsoft.Synchronization.Data namespace.
public enum DbConflictType
{
ErrorsOccured = 0,
LocalUpdateRemoteUpdate = 1,
LocalUpdateRemoteDelete = 2,
LocalDeleteRemoteUpdate = 3,
LocalInsertRemoteInsert = 4,
LocalDeleteRemoteDelete = 5
}
The next snippet contains the event handlers you just registered. You can write the necessary code in these event handlers to display the progress of the synchronization process or the information related to failure if the synchronization process fails.
private static void
dbProvider_SyncProcessFailed
(object sender, DbApplyChangeFailedEventArgs e)
{
//Write your code here
}
private static void
dbProvider_SyncProgress(object sender, DbSyncProgressEventArgs e)
{
//Write your code here
}
The CleanUp method shown in Listing 6 is used to deprovision the server and the client and remove all synchronization objects from the memory.
Listing 6: The CleanUp method
private static void CleanUp(string scopeName,
string serverConnectionString,
string clientConnectionString
{
using (SqlConnection serverConnection = new SqlConnection(serverConnectionString))
{
using (SqlConnection clientConnection = new SqlConnection(clientConnectionString))
{
SqlSyncScopeDeprovisioning serverDeprovisioning = new
SqlSyncScopeDeprovisioning(serverConnection);
SqlSyncScopeDeprovisioning clientDeprovisioning = new
SqlSyncScopeDeprovisioning(clientConnection);
serverDeprovisioning.DeprovisionScope(scopeName);
serverDeprovisioning.DeprovisionStore();
clientDeprovisioning.DeprovisionScope(scopeName);
clientDeprovisioning.DeprovisionStore();
}
}
}
Listing 7 shows the complete code listing of the DataSynchronizer class for your reference.
Listing 7: The complete DataSynchronizer class
public static class DataSynchronizer
{
private static void Initialize
(string table,
string serverConnectionString,
string clientConnectionString)
{
using (SqlConnection serverConnection = new
SqlConnection(serverConnectionString))
{
using (SqlConnection clientConnection = new
SqlConnection(clientConnectionString))
{
DbSyncScopeDescription scopeDescription = new
DbSyncScopeDescription(table);
DbSyncTableDescription tableDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable(table,
serverConnection);
scopeDescription.Tables.Add(tableDescription);
SqlSyncScopeProvisioning serverProvision = new
SqlSyncScopeProvisioning(serverConnection,
scopeDescription);
serverProvision.Apply();
SqlSyncScopeProvisioning clientProvision = new
SqlSyncScopeProvisioning(clientConnection,
scopeDescription);
clientProvision.Apply();
}
}
}
public static void Synchronize(string tableName,
string serverConnectionString, string clientConnectionString)
{
Initialize(tableName, serverConnectionString, clientConnectionString);
Synchronize(tableName, serverConnectionString,
clientConnectionString, SyncDirectionOrder.DownloadAndUpload);
CleanUp(tableName, serverConnectionString, clientConnectionString);
}
private static void Synchronize(string scopeName,
string serverConnectionString,
string clientConnectionString, SyncDirectionOrder syncDirectionOrder)
{
using (SqlConnection serverConnection = new
SqlConnection(serverConnectionString))
{
using (SqlConnection clientConnection
= new SqlConnection(clientConnectionString))
{
var agent = new SyncOrchestrator
{
LocalProvider = new
SqlSyncProvider(scopeName, clientConnection),
RemoteProvider = new SqlSyncProvider(scopeName, serverConnection),
Direction = syncDirectionOrder
};
(agent.RemoteProvider as RelationalSyncProvider).SyncProgress +=
new EventHandler<DbSyncProgressEventArgs>
(dbProvider_SyncProgress);
(agent.LocalProvider as RelationalSyncProvider).ApplyChangeFailed +=
new EventHandler<DbApplyChangeFailedEventArgs> (dbProvider_SyncProcessFailed);
(agent.RemoteProvider as RelationalSyncProvider).ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>
(dbProvider_SyncProcessFailed);
agent.Synchronize();
}
}
}
private static void CleanUp(string scopeName,
string serverConnectionString,
string clientConnectionString)
{
using (SqlConnection serverConnection = new
SqlConnection(serverConnectionString))
{
using (SqlConnection clientConnection = new
SqlConnection(clientConnectionString))
{
SqlSyncScopeDeprovisioning serverDeprovisioning = new
SqlSyncScopeDeprovisioning(serverConnection);
SqlSyncScopeDeprovisioning clientDeprovisioning = new
SqlSyncScopeDeprovisioning(clientConnection);
serverDeprovisioning.DeprovisionScope(scopeName);
serverDeprovisioning.DeprovisionStore();
clientDeprovisioning.DeprovisionScope(scopeName);
clientDeprovisioning.DeprovisionStore();
}
}
}
}
Verify That the Data has Been Synchronized
After you execute the application, the Products table in the source and the destination databases are synchronized. You can verify this by executing a select statement on the Products table of both the ServerDB and ClientDB databases, as shown in Figure 4 below.
Summary
Sync Framework can be used to synchronize data between data stores independent of the protocol in use. However, Sync Framework needs some common metadata to be maintained for each replica that needs to be synchronized. You can take advantage of the Sync Framework library to build your own synchronization provider, as well. You can also take advantage of Sync Framework to provide offline capabilities for applications and build Occasionally Connected Systems.
