On September 19, 2009, I delivered a session with topic the Microsoft Sync Framework, during the Mysore SQL Server Community Series. The exact topic was: “Flexible data synchronization with Microsoft Sync Framework”.
Further below I provide a short review for my session and I also provide the PowerPoint presentation along with the source code (C#) of my demos.
The problem domain
In the modern age of Information Technology many businesses have operations demanding a large number of their employees to work outside the office in remote locations but still being able to access corporate data. To this end, the ability to support mobile and remote workers is becoming more and more important for organizations every day.
It is very critical that organizations ensure users have access to the same information they have when they are in the office. Usually these workers have laptops, smartphones or PDAs. From these devices, users need to have access to their organization’s data which is usually stored in a central Database Server.
The old approach
The traditional way based on which mobile and remote workers access information is usually by using a VPN connection or a similar technique. Of course, in the case the connection is broken or it is not available, then there is also a disruption to the operation the worker performs. Someone may argue with that and say that there is always a way of establishing a network connection to the company. That would be great but common practice proves the opposite. It is not rare at all for communications to face problems thus leaving remote workers without access to their organization’s data. Even a short disruption to an established connection between a remote/mobile worker and the organization’s central Database can cause data integrity problems.
Introducing Microsoft Sync Framework
Microsoft Sync Framework offers a solution to the problem of supporting mobile and remote workers to continue to access their data, even in the absence of a direct connection to the corporate database. To this end, Occasionally Connected Applications (OCAs) are introduced. An OCA stores the data is on the user’s device. Data is usually stored in a SQL Server Compact Edition database. This technology, allows remote workers to continue accessing their data regardless the case where a network connection is present or not.
In order to populate the user’s local database, an OCA will need to include some Data Synchronization capabilities. Data Synchronization consists of the ability to periodically take information that is stored in the client database (such as SQL Server Compact) and synchronize changes with a server database (such as SQL Server).
The main advantage of a synchronization-based solution is that users are not longer required to have a constant network connection to corporate Database. In addition to this, the speed on which users can access the data is not any more relied on the network speed but only on the local device’s processing speed which of course, is much higher than any network speed.
Why use the Microsoft Sync Framework?
Someone might ask; “Why use a framework for this purpose?”
The answer to this question is relatively easy. First of all, synchronization is a difficult task. It introduces the not so trivial tasks of data conflicts detection and resolution when data is communicated from the corporate Database to the local data store on the user’s device and vice versa. Also when data synchronization is used, interruptions and restarts have to be handled in an efficient way. Furthermore, deleted items and synchronization loops are need to be managed as well.
Microsoft Sync Framework takes care of all the data synchronization issues and among many other enhancements, it allows the developer to easily parameterize an OCA implementation in order to control how data conflicts are detected and resolved.
Sync Framework Runtime and Main Components
Sync Framework introduces two basic components:
- Synchronization providers (source & destination)
- Synchronization session
The source and destination synchronization providers determine the source replica and the destination replica.
The synchronization session is the component which actually controls and coordinates the entire data synchronization process between the two providers.
It is important to note that Sync Framework not only allows performing data synchronization between databases, but also allows synchronizing files/folders as well as RSS and ATOM feeds.
The supplied synchronization providers are:
- Sync Services for ADO.NET
- Sync Services for File Systems
- Sync Services for FeedSync (RSS & ATOM feeds)
You can also author your own provider, but it is recommended to use the supplied providers whenever possible thus reusing tested code and easier implementing your data synchronization solution.
The following figure illustrates the Runtime of Sync Framework:
Explanation of the Runtime components:
- Data Source: The location that stores all the information that needs to be synchronized. It can be a relational database, a file system, a Web Service, or even a custom data source.
- Metadata: Information about the data store and the objects within that data store with respect to state and change information. It can be stored anywhere. For making it easy for the developer, Microsoft Sync Framework offers a complete implementation of a metadata store.
- Sync Service Provider: A synchronization provider for ADO.NET, File Systems, FeedSync (RSS & ATOM feeds) or even a custom provider.
- Sync Session: Creates a link from the source to the provider and controls the entire synchronization flow.
The synchronization flow is the following:
- Sync Session Initiated with Destination
- Destination Prepares and Sends Knowledge
- Destination Knowledge used to Determine Changes to be sent
- Change Versions and Source Knowledge sent to Destination
- Local Version Retrieved for Change Items and Compared against Source Version and Knowledge
- Conflicts are Detected and Resolved or Deferred
- Destination Requests Item Data from Source
- Source Prepares and Sends Item Data
- Items are applied at Destination
For more information regarding the Sync Framework Runtime please visit the following MSDN Link.
There are many synchronization scenarios which can be easily implemented using the MS Sync Framework. To this end you can do the following:
- Synchronize Databases
- Synchronize two file system locations (i.e. Multi-master file sync between multiple PCs, Synchronization between PCs using a USB Drive, Taking a network share offline, Maintaining a backup copy of files)
- Synchronize Web Feeds (RSS and ATOM)
You can also implement many other scenarios as the Sync Framework is very extensible and allows you to develop and use your own components such as: Synchronization Providers, metadata stores, custom data stores, etc.
Source Code for Demos
Please find below the source code for my demos.
- Visual Studio 2008 SP1
- .NET Framework 3.5 SP1
- SQL Server 2008 SP1 (used for hosting the Central Database)
- SQL Server Compact 3.5 SP1 (used for hosting the client’s local data store)
- Sync Framework 2.0 CTP2
Demo 1: Creating a Sync Ecosystem from Scratch (Databases)
Summary: In this demo, my source replica is a SQL Server 2008 database called “SyncServerDB” which represents the Central Database. The destination replica is a Windows Forms Application (role: client) developed in Visual Studio 2008 SP1 which used SQL Server 3.5 Compact Edition SP1 for hosting the local data store. The Windows Forms Application allows the user to synchronize the client with the central database via a GUI form.
* Before using the code, you have to reinitialize it. You can follow the instructions in this document for doing this.
Demo 2: Synchronizing Files
Summary: In this demo I used the Sync Framework for synchronizing the contents of two folders on my local hard drive in a bidirectional manner.
Details regarding the folders: Make sure you create two folders called “source” and “dest” in the “Debug” folder for this Visual Studio project. Also before running the executable project, make sure you place a sample file in one of the folders in order to see after the program’s execution that the two folders were successfully synchronized.
Download link for demo 2 source code.
* Disclaimer: The source code for the demos is intended to be used only for demo purposes. Do not use it for Production systems as it is simplified for demo purposes.
This session gave me the chance to use again the Microsoft Sync Framework. All I can say is that it was a great experience. I am actually thinking of implementing some tools using the Framework and place it to CodePlex. I will be posting updates to my blog regarding this as well.
Summing up this review, I would like to say that by using the MS Sync Framework it is very easy to implement Data Synchronization for a large variety of scenarios. With all the built-in functionality you are enabled to do more with less! Additionally, you can create your own custom components and cover even more synchronization scenarios.
Sync Framework enables the developer to easily built Occasionally Connected Systems and Sync Ecosystems by providing among other efficient conflict handling and a flexible Synchronization Algorithm. Furthermore, Sync Framework Supplied Providers can be widely used for different scenarios.
I would like to thank the Mysore SQL Server PASS Chapter committee for inviting me to present to this great SQL Server community series! It was a pleasure to present and discuss about SQL Server and its related .NET technologies.
Drop me a line if you have any comments!
See you at another event soon!
Learn Useful SQL Server Development Tips – Enroll to the Course!
Check our online course on Udemy titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).
Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!
Upgrade your Tech Skills – Learn all about Azure SQL Database
Enroll to our online course on Udemy titled “Introduction to Azure SQL Database for Beginners” and get lifetime access to high-quality lessons and hands-on guides about all aspects of Azure SQL Database.Enroll Now with Discount!
Featured Online Courses:
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Administration Tips
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Development Tips for SQL Developers
- Working with Python on Windows and SQL Server Databases
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to SQL Server Machine Learning Services
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
- How to Patch a SQL Server Failover Cluster
- How to Patch a Standalone SQL Server Instance
- How to Add a Database to a SQL Server Availability Group Using T-SQL
- Top 10 SQL Server DBA Daily Tasks List
- The “Public” Database Role in SQL Server
- Encrypting a SQL Server Database Backup
- Learn Azure Data Lake Analytics by Example
- Azure Cosmos DB: Learn by Example
- How to Create an Azure SQL Server Virtual Machine
- How to Backup a SQL Server Database from On-Premises to Azure Storage
- … all our SQL Server Administration Articles
Check our Database Security and Administration Tool: DBA Security Advisor
DBA Security Advisor: Secure your SQL Server instances by scanning multiple instances against a rich set of security checks, and by getting recommendations and remediation steps.
Subscribe to our newsletter and stay up to date!
Subscribe to our YouTube channel (SQLNetHubTV)!
Check our SQL Server Administration articles.
Check out our latest software releases!
Check our eBooks!
Benchmark SQL Server memory-optimized tables with In-Memory OLTP Simulator.
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.