Software Architecture – Sync Data between offline-Disconnected databases –Sync Framework

October 3, 2010

Problem definition:ERP Product is installed in head office and 5 branch offices such that each location installation has its on database. Locations are connected via Internet that is they are acting as diconnected databases.
We want to sync databases of different branches.
Solution: As discussed in previous post at here
Some possible solutions to problem are as:
• Replication
• ETL
• Sync Providors
• Sync Framework
• Change Data Capture
In this post we will follow Sync Framework to solve this problem.
According to MSDN:

Database synchronization providers are a part of the Microsoft® Sync Framework. Sync Framework is a comprehensive synchronization platform that enables developers to add synchronization capabilities to applications, services, and devices. Sync Framework solves the problem of how to synchronize any type of data in any store using any protocol over any topology. Fundamental to Sync Framework is the ability to support offline and collaboration of data between any types of endpoints (such as device to desktop, device to server, etc.).
Sync Framework database synchronization providers enable synchronization between ADO.NET-enabled databases. Since the database synchronization providers are part of the Sync Framework, any database that uses these providers can then also exchange information with other data sources that are supported by Sync Framework, such as web services, file systems, or custom data stores.
The primary focus of this document will be on synchronizing information between database systems and how Sync Framework helps developers avoid many of the common issues associated with OCAs.

Download Sync Framework 2.1 and install it. Add references to the following DLLs:
o Microsoft.Synchronization.dll
o Microsoft.Synchronization.Data.dll
o Microsoft.Synchronization.Data.SqlServer.dll

Lets consider Employeetable in 2 databases DB1 and DB2, which needs to be synchronized.
L ets declare few global variables as

  static string  SourceSqlConnection = @"Data Source=SOFTCOM-PC\XGURU;Initial Catalog=DB1;Integrated Security=True";
       static string TargetSqlConnection = @"Data Source=SOFTCOM-PC\XGURU;Initial Catalog=DB2;Integrated Security=True";
       static string SourceScopeName = "MySourceScop";
       static string TargetScopeName = "MyTargetScop";
       static string TableToSync = "Employee";

Variable SourceSqlConnection represents the connection string of first database that will take part in sync process. Variable TargetSqlConnection represents the connection string of second database that will take part in sync process. Variable SourceScopeName and TargetScopeName contains name of scope for the two databases. Scope defines what you want to synchronize. Normally tables are added to scope which needs to be sync. Variable TableToSync contain the name of table that will be synced.
Step 1:
Defining Scope and Provisioning for Source SQL Server Database can be done via following method.

 private static void ProvisionSource()
        {
            // create connection to the Source server database
            SqlConnection serverConn = new SqlConnection(SourceSqlConnection);
 
            // define a new scope named MySourceScope
            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(SourceScopeName);
 
            // get the description of the GeneralAreaMaster table from SyncDB dtabase
            DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(TableToSync, serverConn);
 
            // add the table description to the sync scope definition
            scopeDesc.Tables.Add(tableDesc);
 
            // create a server scope provisioning object based on the ProductScope
            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
            try
            {
                serverProvision.Apply();
            }
            catch { }
        }

Step 2:
Defining Scope and Provisioning for Target SQL Server Database can be done via following method.

private static void ProvisionTarget()
        {
            // create connection to the server database
            SqlConnection serverConn = new SqlConnection(TargetSqlConnection);
 
            // define a new scope named MyTargetScope
            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(TargetScopeName);
 
            // get the description of the GeneralAreaMaster table from SyncDB dtabase
            DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(TableToSync, serverConn);
            // add the table description to the sync scope definition
            scopeDesc.Tables.Add(tableDesc);
 
            // create a server scope provisioning object based on the ProductScope
            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
            try
            {
                serverProvision.Apply();
            }
            catch { }
        }

Step 3:
Synchronization between the 2 databases can be done via following code.

private static void SyncSourcewAndTargetDatabases()
        {
            // create a connection to the Source database
            SqlConnection clientConn = new SqlConnection(SourceSqlConnection);
 
            // create a connection to the Target server database
            SqlConnection serverConn = new SqlConnection(TargetSqlConnection);
 
            // create the sync orhcestrator
            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
 
            // set local provider of orchestrator to a CE sync provider associated with the 
            // ProductsScope in the SyncCompactDB compact client database
            syncOrchestrator.LocalProvider = new SqlSyncProvider(SourceScopeName, clientConn);
 
            // set the remote provider of orchestrator to a server sync provider associated with
            // the ProductsScope in the SyncDB server database
            syncOrchestrator.RemoteProvider = new SqlSyncProvider(TargetScopeName, serverConn);
 
            // set the direction of sync session to Upload and Download
            syncOrchestrator.Direction = SyncDirectionOrder.DownloadAndUpload;
 
            // 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);
        }

This code will sync the data between 2 databases.
For advance cases. See documentation.

Advertisements