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." (https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&;id=23217)

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) from here: https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&;id=23217

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

• Figure 1: The components of Microsoft Sync Framework

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.

Figure 2: A Sync Session is established for synchronizing data between the source and the destination.

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:

1. Open the Visual Studio 2017 IDE.
2. To create a new project, click on File > New > Project.
3. Select Console App (.NET Framework) from the list of project templates displayed.
4. Specify a name and location for the project.
5. Select the Framework version you would like to use for this project.
6. 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:

1. Launch the Microsoft SQL Server Management Studio.
2. In the Object Explorer Window, select Databases.
3. Right click and select New database....
4. 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.

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.

Figure 3: Displaying the data inserted in the Products table

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.

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.

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 the CleanUp methods 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.

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.

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 7 shows the complete code listing of the DataSynchronizer class for your reference.

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.

Figure 4: Data in the source and the destination databases has been synchronized.

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.

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)

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();                
            }
        } 
      

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();
        }
    }

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();
                }                
            }               
        }

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();
                }
            }            
        }

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();
                }
            }            
        }
    }