DataBase Tool Database Synchronization with Microsoft Sync Framework

Microsoft Sync Framework is a comprehensive synchronization platform enabling collaboration and offline for applications, services and devices. This is actually shipped with Visual Studio 2008 for the first time and now the current stable version is Microsoft Sync Framework 2.1. There are number of providers in Sync Framework which supports many common data sources.The following are the providers included,

I am not going to write much intro about Microsoft Sync Framework as you can find many articles on internet. Let’s see how database synchronization works with Microsoft Sync Framework by a simple example.

I have two Microsoft SQL Server 2008R2 Databases which are “SyncDBServer” and “SyncDBClient”. Unfortunately I am having them on the same server and actually what I want to demonstrate is data synchronization between two remote database servers. Since I don’t have such environment I am using the same server, because it will not do any harm to the logic I am using here.

In “SyncDBServer”, I have three tables which are CUSTOMER, PRODUCT and ORDER. I have filled up these tables with sample data and “SyncDBClient” database has no tables. What I am going to do today is synchronize only the CUSTOMER and PRODUCT tables of “SyncDBServer” with “SyncDBClient”. In this case Microsoft Sync Framework will create CUSTOMER and PRODUCT tables in “SyncDBClient” for me.

First I need to prepare or provision these two databases for the synchronization. For that I will have to write some codes. Let’s create a console application for provisioning “SyncDBServer” and I am naming it as “ProvisionServer”. I am adding following references (I am using Microsoft Sync Framework 2.1 Software Development Kit (SDK)).

using System.Data.SqlClient;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;

namespace ProvisionServer
{
    class Program
    {
        static void Main(string[] args)
        {
            // connect to server database
            SqlConnection serverConn = new SqlConnection("Data Source=.; Initial Catalog=SyncDBServer; Integrated Security=True");

            // define a new scope named MySyncScope
            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("MySyncScope");

            // get the description of the CUSTOMER & PRODUCT table from SERVER database
            DbSyncTableDescription cusTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("CUSTOMER", serverConn);
            DbSyncTableDescription prodTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("PRODUCT", serverConn);

            // add the table description to the sync scope definition
            scopeDesc.Tables.Add(cusTableDesc);
            scopeDesc.Tables.Add(prodTableDesc);

            // create a server scope provisioning object based on the MySyncScope
            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);

            // skipping the creation of table since table already exists on server
            serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);

            // start the provisioning process
            serverProvision.Apply();

            Console.WriteLine("Server Successfully Provisioned.");
            Console.ReadLine();
        }
    }
}

Here I am accessing “SyncDBServer” database and specified my synchronization scope. Now when I run this project, I can see the message “Server Successfully Provisioned.” and most importantly I can see some new tables created in  “SyncDBServer” database.

Now I am creating a console application for provisioning “SyncDBClient” and I am naming it as “ProvisionClient”. Again I am adding the same references.

using System;
using System.Data.SqlClient;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;

namespace ProvisionClient
{
    class Program
    {
        static void Main(string[] args)
        {
            // create a connection to the client database
            SqlConnection clientConn = new SqlConnection(@"Data Source=.; Initial Catalog=SyncDBClient; Integrated Security=True");

            // create a connection to the server database
            SqlConnection serverConn = new SqlConnection("Data Source=.; Initial Catalog=SyncDBServer; Integrated Security=True");

            // get the description of SyncScope from the server database
            DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("MySyncScope", serverConn);

            // create server provisioning object based on the SyncScope
            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);

            // starts the provisioning process
            clientProvision.Apply();

            Console.WriteLine("Client Successfully Provisioned.");
            Console.ReadLine();
        }
    }
}

Here I am accessing both “SyncDBServer” and “SyncDBClient”. I am getting the synchronization scope from “SyncDBServer” and applying it on the “SyncDBClient”. Now when I run this project, I can see the message “Client Successfully Provisioned.” and here also I can see some new tables created in “SyncDBClient" database including CUSTOMER and PRODUCT tables. But there will be no data in these two tables.

using System;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;

namespace ExecuteSync
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection clientConn = new SqlConnection(@"Data Source=.; Initial Catalog=SyncDBClient; Integrated Security=True");

            SqlConnection serverConn = new SqlConnection("Data Source=.; Initial Catalog=SyncDBServer; Integrated Security=True");

            // create the sync orhcestrator
            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

            // set local provider of orchestrator to a sync provider associated with the 
            // MySyncScope in the client database
            syncOrchestrator.LocalProvider = new SqlSyncProvider("MySyncScope", clientConn);

            // set the remote provider of orchestrator to a server sync provider associated with
            // the MySyncScope in the server database
            syncOrchestrator.RemoteProvider = new SqlSyncProvider("MySyncScope", serverConn);

            // set the direction of sync session to Upload and Download
            syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;

            // subscribe for errors that occur when applying changes to the client
            ((SqlSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(Program_ApplyChangeFailed);

            // execute the synchronization process
            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

            // print statistics
            Console.WriteLine("Start Time: " + syncStats.SyncStartTime);
            Console.WriteLine("Total Changes Uploaded: " + syncStats.UploadChangesTotal);
            Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal);
            Console.WriteLine("Complete Time: " + syncStats.SyncEndTime);
            Console.WriteLine(String.Empty);
            Console.ReadLine();
        }

        static void Program_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)
        {
            // display conflict type
            Console.WriteLine(e.Conflict.Type);

            // display error message 
            Console.WriteLine(e.Error);
        }
    }
}

 

That's it. I am uploading sample databases and project files to my SkyDrive. You can download and play around.