Pages

Tuesday, February 19, 2013

Let’s imagine a scenario where you want to sync data based on a filter. For an example your server database has an “EMPLOYEE” master table and it has all the employee records of all your branches. Now you have two branch office databases for your two branches, and you want to download relevant employee data to each of these branch databases.

This is my “EMPLOYEE” table in the Server database.

Employee table in the Server

My requirement is download all employee information to relevant branch databases and that is all employees who has branch id as “B0001” to New York database and all employees who has branch id as “B0002” to Boston database.

Now let’s see how to implement such a synchronization using Microsoft Sync Framework. Here a filter is used to restrict the data that is being synchronized between each branch and the server. With the release of Sync Framework 2.1 Microsoft has introduced dynamic parameter based filtering support and I am going to use dynamic parameter based filtering for this.

Here first I am doing is creating a provisioning template. Creating a provisioning template is same as creating a provisioning scope, except the only thing is the template cannot be used for synchronization. In my provisioning template on the server I am mentioning my “EMPLOYEE” table has filter column which is “BRANCH_ID” and the filter clause is where “BRANCH_ID” is equal to parameterized “branchid”.

Once the template is created you can see two new records added to “scope_config” and “scope_templates” tables in Server database. In the row created in “scope_config” table, you can see config data of the template which is an XML. Here you can see things we mentioned in the code such as parameter name, filter clause, filter column etc. in the XML.

When the provisioning is completed, a scope created on the server. Now when this step is completed if you examine the tables in “scope_info” and “scope_parameters”, again you can see two new rows created. “scope_info” will contain a row including the details of the particular provision we have done now and “scope_parameters” will contain a row including the details of parameters for the above provision.

Now I am going to provision the branch database. It’s basic provision; I am getting the relevant scope from the server and applying it on the client.

Sunday, February 17, 2013

Let’s say you want to sync two tables which have two different names with Microsoft Sync Framework. In my Remote database I have a table named “CUSTOMER” and in my Local database I have two tables named “CUSTOMER” and “NEW_CUSTOMER.” I want table “CUSTOMER” in remote database to be downloaded in to table “NEW_CUSTOMER”.

There is something important to note when provisioning the client. Most of the times when provisioning the client what we are doing is getting the scope description which we have applied on the server and then applying it on the client. Please remember that, here we can’t do it. We should create the provision scope in the client in the following way.

Friday, February 1, 2013

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,

Web synchronization components: Synchronization for FeedSync feeds such as RSS and ATOM feeds.

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.

“SyncDBServer” and “SyncDBClient”

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)).

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.

“Server Successfully Provisioned.”

“SyncDBServer”

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

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.

“Client Successfully Provisioned.”

“SyncDBClient"

Now finally what’s left to do is the synchronization. For that I am creating a console application and I am naming it as “ExecuteSync”. I am adding following references.

Here I am mentioning the synchronization direction(Upload or Download or UploadAndDownload or DownloadAndUpload) and doing the synchronization according to my specified scope. When I run this, I am getting the following output.

Synchronization Completed.

“SyncDBClient" after Synchronization

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