C# – Microsoft Sync Framework for file and database

Data synchronization is always a top feature for any product in IT zone, especially if your product is a database-based product. It provides the customers more flexibilities and “freedom” when using the products. For example, the customer is on construction area, enter data through a concise mini mobile client, then sync them with the server and when they are back to the office, they have all data in the main software and finish the rest of work. That means at any time and anywhere they can access their updated data. However, it’s not easy at all to implement this dreaming feature in any application because of the complexity. However, if you’re using Microsoft products, you can easily build this feature in your apps thanks to Microsoft Sync Framework (MSF) http://msdn.microsoft.com/en-us/sync/bb736753. Maybe you’ll say that there is already the replication feature in MS SQL Server, why do you need to use MSF? Yes, it’s correct that MS SQL has already this feature but obviously, we can only sync between MS SQL Servers. How about if we want to sync with MS SQL Express? The answer can be MSF.

In this small blog, I would like to make a small introduction to MSF through an example showing how we can simply sync 2 databases in MS SQL Express. It’s very simple example so that you have a good start with MSF, don’t expect something advanced here. If you are already professional with MSF, you can skip this blog. First, you need to download Microsoft Sync Framework Software Development Kit with the latest version of 2.1 at http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23217 and install it. In MS SQL Express, create a new database called “MainDb” with 2 tables “Orders” and “OrdersDetails”. The scripts for creating tables are below

CREATE TABLE [dbo].[Orders](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderDate] [datetime] NULL,
 CONSTRAINT [PK__Orders__3214EC077F60ED59] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[OrderDetails](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderId] [int] NOT NULL,
	[Product] [nvarchar](100) NULL,
	[Quantity] [int] NULL,
 CONSTRAINT [PK__OrderDet__3214EC0703317E3D] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  ForeignKey [FK_OrderDetails_Orders]    Script Date: 10/17/2011 16:32:36 ******/
ALTER TABLE [dbo].[OrderDetails]  WITH CHECK ADD  CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY([OrderId])
REFERENCES [dbo].[Orders] ([Id])
GO
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_Orders]
GO

MainDb and its tables

Execute this SQL statement several times to insert some data records into tables

INSERT INTO [MainDb].[dbo].[Orders]
           ([OrderDate])
     VALUES
           (DATEADD(DAY,RAND()*365,GETDATE()))
GO

Muster Data

Now create another new database call “ClientDb01” and let it clear, we’ll sync it with the master database.

Database structure

Now start Visual Studio, create a new Console Application and add references to .dll of MSF. The .dll can be found in .NET reference tab.

Microsoft Sync Framework References

Microsoft Sync Framework References

Then write the listing below to make synchronization between them.

SyncOrchestrator agent = new SyncOrchestrator();
string scopeName = "test";
SqlConnection sqlConnLocal = new SqlConnection(@"Data Source=localhost\sqlexpress;Initial Catalog=MainDb;Integrated Security=True;");
SqlConnection sqlConnRemote = new SqlConnection(@"Data Source=localhost\sqlexpress;Initial Catalog=ClientDb01;Integrated Security=True;");

SqlSyncProvider sqlProviderLocal = new SqlSyncProvider(scopeName, sqlConnLocal);
SqlSyncProvider sqlProviderRemote = new SqlSyncProvider(scopeName, sqlConnRemote);

SqlSyncScopeProvisioning scopeProvisionLocal = new SqlSyncScopeProvisioning(sqlConnLocal);
if (!scopeProvisionLocal.ScopeExists(scopeName))
{
	DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scopeName);
	scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", sqlConnLocal));
	scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("OrderDetails", sqlConnLocal));
	scopeProvisionLocal.PopulateFromScopeDescription(scopeDesc);
	scopeProvisionLocal.SetCreateTableDefault(DbSyncCreationOption.Skip);
	scopeProvisionLocal.Apply();
}

SqlSyncScopeProvisioning scopeProvisionRemote = new SqlSyncScopeProvisioning(sqlConnRemote);
if (!scopeProvisionRemote.ScopeExists(scopeName))
{
	DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, sqlConnLocal);
	scopeProvisionRemote.PopulateFromScopeDescription(scopeDesc);
	scopeProvisionRemote.Apply();
}

agent.LocalProvider = sqlProviderLocal;
agent.RemoteProvider = sqlProviderRemote;
SyncOperationStatistics stats = agent.Synchronize();
Console.WriteLine("Download Applied:\t {0}", stats.DownloadChangesApplied);
Console.WriteLine("Download Failed:\t {0}", stats.DownloadChangesFailed);
Console.WriteLine("Download Total:\t\t {0}", stats.DownloadChangesTotal);
Console.WriteLine("Upload Total:\t\t {0}", stats.UploadChangesApplied);
Console.WriteLine("Upload Total:\t\t {0}", stats.UploadChangesFailed);
Console.WriteLine("Upload Total:\t\t {0}", stats.UploadChangesTotal);
Console.ReadLine();

As you can see that the main object which is responsible for synchronizing between databases is SyncOrchestrator which needs two providers: LocalProvider and RemoteProvider. These providers can be FileSyncProvider, SqlSyncProvider or any custom provider depending on which object we would like to synchronize. In case of SqlSyncProvider, we can declare SyncProvider with a scope name and its connection. In case of FileSyncProvider, we need path to folders we would like to synchronize like this

FileSyncProvider providerLocal = new FileSyncProvider(@"C:\Temp\Source Sync");
FileSyncProvider providerRemote = new FileSyncProvider(@"C:\Temp\Dest Sync");

We can apply more settings to the SyncProvider through SqlSyncScopeProvisioning to customize the synchronization. In the example, I define which tables should be synchronized between databases. The result of synchronizing process will be stored in SyncOperationStatistics telling how many data record was sent to remote server or download from it and if any record can’t be synchronized. So now if I execute the code, I’ll receive the statistic at frontend and at backend the contents of both databases are same.

SyncOperationStatistics

Metatables

As you can see, MSF create some metadata tables for controlling the synchronization too. It makes the databases a little complicated and messy but I think for an administrator it’s no problem. Now I would like to make a small test to see what will happen when changes occur at both of sides: local and remote. Therefore I open the OrderDetails table of MainDb, insert one data record and open OrderDetails of ClientDb01, insert one other data record. The content of these records is different.

Changes in MainDb

Changes in ClientDb01

Then I let the synchronization sync again, that result really satisfies me. Both of tables have exactly same structure of data (please notify the Id of each data record of table OrderDetails).

Result for data integrity

Database with immediate changes

Ok, MSF passed the first test with data integrity. Now I would like to make a second test for duplication, what would happen if changes from local and remote are same? I open OrderDetails table of MainDb and ClientDb01 again and insert another data record to them, but these records are same. I let the synchronization again, the statistic shows that there are some data to be uploaded and downloaded. But when opening the table at the back end, we’ll see that there are no changes in the database. MSF recognizes that they are duplicated and don’t insert them again into the database.

Duplication test

SyncOperationStatistics duplication test

MSF is a powerful framework. We can use it to sync any object with our custom sync provider. It allows us to define how the synchronization should run and how it should behave in any conflict. I hope that you have a good start with MSF and maybe I’ll write some more articles about this wonderful framework. At usual is source code for anyone who is interested in “Sync Framework Basic Example

7 thoughts on “C# – Microsoft Sync Framework for file and database”

  1. hello, it is indeed good demo really helpful but i have one major issue as i am developing Big ERP System using Microsoft synchronization framework and i have successfully synchronize client dbs with main server DB but at same time during the process of synchronization the table which is being to use in synchronization they are no longer use with another process at different client locations or at server location means if user want to search any data from that table or want to insert any data into that table he has to wait for this and it will show time out exception so any solution for this then it will be helpful to me.

  2. @pravin prajapati : I think Microsoft Sync Framework is not suitable for real time system because it will lock table for a while when syncing. It’s suitable for replication than real-time sychronization. For example you can use a system for inputing data and the other system for evaluating the data. The second system can be synced as slaver with the first system.
    Moreover I think a NoSQL server is an approriate solution for you because he does natively support sharding database and heavy traffic.

  3. Super Nice Tutorial and all is works exactly follow your guide but i would like to know is it possible to like SQL Server ( Master ) and SQLCE as (Client)? if yes how do i do that?
    Thanks a ton.

Leave a Reply

Your email address will not be published. Required fields are marked *