In this article I will show how to use the 2.1 version of the Sync Framework to write a console application to synchronize Windows Azure SQL Database with on-premises SQL Servers. The Sync Framework takes care of all the messy details for you, leaving you just tying the pieces together in very few lines of code. This article is building on top of Liam’s blog post and video.
This example will show how to synchronize SQL Database with an on-premises SQL Server database. This means that the on-premise SQL Server will be the source database for setting up the synchronization, and the SQL Database will be the destination. When the databases are setup and sync’d for the first time, both databases will have the same data and the synchronization will be bi-directional. Writes can be done on either database, and synchronization will move the changes to make sure both databases are identical.
For this example, I am going to use the Adventure Works database (which can be downloaded here) and synchronize just a subset of the tables: the Customer and Product tables.
I also need a SQL Database account and an allocated server to synchronize too. On the SQL Database server I am going to start with an empty database, the console application will do the initial synchronization to setup the table schema on SQL Database. Once you have created the database on the SQL Database server you will have to create the SalesLT schema that the Product and Customer tables are in. You can do this by attaching to SQL Database with SQL Server Management Studio and executing this Transact-SQL statement:
CREATE SCHEMA SalesLT
First thing to do is download and install the Microsoft Sync Framework 2.1 Software Development Kit (SDK). This will install the assemblies on your local machine that you need to reference in order to make your console application run. This SDK doesn’t install any software on your computer, i.e. a running service or a windows application with a user interface. Which means, as long as you ship the required assemblies with the console application in the example, you can transport the console application to another computer.
Next step is to create the console application and references the Sync Framework assemblies. Using Visual Studio I create a simple new console application. Next I need to add the correct Sync Framework references. Which are:
Found in: C:\Program Files\Microsoft Sync Framework\2.1\Runtime\x86 and the Synchronization Framework providers:
Found in: C:\Program Files\Microsoft Sync Framework\2.1\Runtime\ADO.NET\V3.1\x86
Inside the console application I am going to call the classes exposed by adding the Sync Framework references to perform two actions: Setup and Synchronization. Setup will only be run once to create the table schema on SQL Database, and Synchronization will be run anytime you want to the two databases to be in sync.
The first part of the code to setup the databases creates two very ordinary ADO.NET SqlConnection objects. It then uses classes from the Sync Framework to name the tables and add them to the Sync scope:
The next section of code sets up the local on-premise SQL Server for provisioning. If the SQL Server already contains the table schemas and data then what does it have to do? The Synchronization Framework uses both databases as data storage to store configuration information, and state information about the current status of the synchronization. So the provisioning creates tables on your local SQL Server to store this information.
The next section of code does the same thing for the remote SQL Database server. However, it also creates the schemas data tables that it is going to synchronize too, based on the local SQL Server scope. Here is what the code looks like:
After the Apply() method has run on SQL Database the database looks like this:
To synchronize the databases just run the console application like this:
Database setup just needs to happen once, however you will might want to synchronize the database multiple, because of this the code is split into two different sections one for setup and one for synchronization.
The code synchronizing the data is just as simple. Here is what it looks like:
In the synchronization code we create two connection and instantiate a sync orchestrator, telling it that we want to upload and download the data. This is considered bi-directional synchronization, writes in either SQL Database or SQL Server to be moved to the other.
Once the synchronization has completed, we can query the SQL Database and see that the data in is there.
I borrowed heavily from Liam’s blog post and video, and in posting the code, simplified it by removing the output to the console. For the complete code sample, see Liam’s blog and use the code posted there.
hey wayne this artical is very nice ,
but is there is any way to sync only a specific columns of a table???
For the answer to JamesRob's question, see social.msdn.microsoft.com/.../03656f43-181b-41d6-b552-fe7210320f4b
can we schedule the sync time? maybe every 10 minutes the sync will run