Sync Two Databases using Microsoft Sync Framework in C#

We can sync two SQL Server databases in several ways, today we discuss about Microsoft Sync Framework. Microsoft Sync Framework is used to Sync the two Databases which are there in two different SQL servers. In this article we discuss about how to sync the databases using this Framework.

First download and install Microsoft Sync Framework at http://www.microsoft.com/en-us/download/details.aspx?id=23217

Open Microsoft Visual Studio => Create New Console application and name it as MicrosoftSyncFramework

Add Reference to Microsoft.Synchronization, Microsoft.Synchronization.Data, and Microsoft.Synchronization.Data.SqlServer dlls, I included these dlls also in the code folder.

Let’s Create the Database Company and Employee table at Server by using below script.

USE [master]
GO 

CREATE DATABASE [Company] ON  PRIMARY
( NAME = N'Company', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\DATA\Company.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Company_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\DATA\Company_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO 

USE [Company]
GO

/****** Object:  Table [dbo].[Employee]    Script Date: 08/10/2014 09:57:00 ******/

SET ANSI_NULLS ON
GO 

SET QUOTED_IDENTIFIER ON
GO 

SET ANSI_PADDING ON
GO 

CREATE TABLE [dbo].[Employee](
                [Id] [int] IDENTITY(1,1) NOT NULL,
                [Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Employee] 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

SET ANSI_PADDING OFF
GO

Insert some records into Employee Table by using below Insert Scripts.

INSERT INTO Employee(name) VALUES('A')
INSERT INTO Employee(name) VALUES('B')
INSERT INTO Employee(name) VALUES('C')
INSERT INTO Employee(name) VALUES('D')
INSERT INTO Employee(name) VALUES('E')
INSERT INTO Employee(name) VALUES('F')

Now create the provision at server by using Sync dlls as shown below.

public static void ProvisionServer()
{
            SqlConnection serverConn = new SqlConnection(sServerConnection);           

            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(sScope); 

            DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Employee", serverConn);

            scopeDesc.Tables.Add(tableDesc); 

            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
            serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
            serverProvision.Apply();

}

Here we declared the server & Client connection strings and scope level globally in the application. We added Employee table to the scope as shown below.

Now create the Client side provision with the below code. Before that first create Company Database at client SQL Server by using below script.

USE [master]
GO

CREATE DATABASE [Company] ON  PRIMARY
( NAME = N'Company', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\DATA\Company.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Company_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\DATA\Company_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
public static void ProvisionClient()
{
            SqlConnection serverConn = new SqlConnection(sServerConnection);

            SqlConnection clientConn = new SqlConnection(sClientConnection); 

            DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(sScope, serverConn);

            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc); 

            clientProvision.Apply();
}

Now sync two database servers by using below code.

private static void Sync()
{
            SqlConnection serverConn = new SqlConnection(sServerConnection);

            SqlConnection clientConn = new SqlConnection(sClientConnection);           

            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();            

            syncOrchestrator.LocalProvider = new SqlSyncProvider(sScope, clientConn);

            syncOrchestrator.RemoteProvider = new SqlSyncProvider(sScope, serverConn);           

            syncOrchestrator.Direction = SyncDirectionOrder.Download;
            ((SqlSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(Program_ApplyChangeFailed);           

            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();           

            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);
} 

static void Program_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)
{
            Console.WriteLine(e.Conflict.Type);
            Console.WriteLine(e.Error);
}

As shown above we are just downloading the changes at client from server. If you want to move client changes also to server use SyncDirectionOrder.DownloadAndUpload or SyncDirectionOrder.UploadAndDownload options.

Complete code to Sync the two SQL Server database is provided below.

using System;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer; 

namespace MicrosoftSyncFramework
{
    class Program
    {
        static string sServerConnection = @"Data Source=Server\MSSQL2008;Initial Catalog=Company;Persist Security Info=False;User ID=sa;Password=password;Connect Timeout=60";
        static string sClientConnection = @"Data Source=Client\MSSQL2008;Initial Catalog=Company;Persist Security Info=False;User ID=sa;Password=password;Connect Timeout=60";
        static string sScope = "EmployeeScope"; 

        static void Main(string[] args)
        {
            ProvisionServer();
            ProvisionClient();
            Sync();
        } 

        public static void ProvisionServer()
        {
            SqlConnection serverConn = new SqlConnection(sServerConnection);           

            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(sScope); 

            DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Employee", serverConn);

            scopeDesc.Tables.Add(tableDesc); 

            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
            serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);

            serverProvision.Apply();
        } 

        public static void ProvisionClient()
        {
            SqlConnection serverConn = new SqlConnection(sServerConnection);

            SqlConnection clientConn = new SqlConnection(sClientConnection); 

            DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(sScope, serverConn);

            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc); 

            clientProvision.Apply();
        } 

        private static void Sync()
        {
            SqlConnection serverConn = new SqlConnection(sServerConnection);

            SqlConnection clientConn = new SqlConnection(sClientConnection);           

            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();           

            syncOrchestrator.LocalProvider = new SqlSyncProvider(sScope, clientConn);

            syncOrchestrator.RemoteProvider = new SqlSyncProvider(sScope, serverConn);           

            syncOrchestrator.Direction = SyncDirectionOrder.Download;
            ((SqlSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(Program_ApplyChangeFailed);           

            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();           

            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)
        {
            Console.WriteLine(e.Conflict.Type);
            Console.WriteLine(e.Error);
        }
    }
}

Change the connection string and database details according to your requirement. Run the application, the output displays as six records downloaded as shown below.

                                 

Check client Company database and you can find Employee table with six records which are downloaded from server.

                                                                                                                     MicrosoftSyncFramework.zip (350.4KB)

Comments (17) -

  • How can i sync every table.As you have Specified "Employee Table".
    DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Employee", serverConn);
  • Raj
    Hi Johnson,

    We have to add each and every table manually or get all tables by using Sys.Objects and loop to add each table.

    Thanks
  • Hi Admin,

    I have some questions for you as follow:
    + If I have to put addition some columns into table client after the table has been completed sync, so what is happen to next time I will to sync again?
    + If I have to put addition some columns into table server after the table has been completed sync, so what is happen to next time I will to sync again?

  • Raj
    Hi LANNH,

    Microsoft Sync Framework not only works for data, it also syncs table schema also. As you mentioned both cases whatever the new schema changes will sync irrespective of whether changes are in client or server.

    Thanks
  • HI Admin,

    Thanks, your reply but I have been seen a message of system as conflicts and errors are detected at the level of the row, so that data of new some columns has not been put to table destination. How to fix this problems? Thanks
  • Sir, I have a query. Suppose I have developed an application which is using Local Database. Now  my requirement is that every user is having a table let us say "Product". Now for every User I want to maintain different Table on the server like ProductUser1, ProductUser2 and so on. So that for every user they can sync his/her own data based on the Table we have created on the server.
    How to perform this action.
    Please let me inform, I have stuck in this situation for the case of different user. According to u , its working well for single user. but for different user how to maintain different data.
  • Thank you so much!
    I needed a very simple start and your app does the job. I have customized it.
  • Hi Admin,
    Is there a way I can avoid the error "Could not create a scope with name 'EmployeeScope' as a scope with that name already exists." without changing the scope name everytime I run the program

    Thanks
    Rattasit
  • Hi
    Thank you for your sample
    It works fine for the first time but I have an error when I want to synchronize a second time
    An unhandled exception of type 'Microsoft.Synchronization.Data.DbProvisioningException' occurred in Microsoft.Synchronization.Data.SqlServer.dll

    Additional information: Could not create a scope with name 'EmployeeScope' as a scope with that name already exists.
    What is wrong ?
    Thanks
    Romain
    • use below line before Server/Client Provision apply..
      To check Scope is exist or not.
      Smile

      if (!serverProvision.ScopeExists(sScope))
                  {
                      serverProvision.Apply();

                  }
  • i am getting an exception while trying to sync database again

    An unhandled exception of type 'Microsoft.Synchronization.Data.DbProvisioningException' occurred in Microsoft.Synchronization.Data.SqlServer.dll

    Additional information: Could not create a scope with name 'Guest' as a scope with that name already exists.
  • Hay,

    Thank you for your sample
    but
    one problem
    my 4 table at a time sync for server to client side.
    how declaration a scope & how to use in this example.
    Help me.

  • Failed to execute the command 'BulkInsertCommand' for table 'office_account'; the transaction was rolled back. Ensure that the command syntax is correct.

    Solution Please.........?
  • I did above sample. Yes it does get sync server database to client server. But if I edit server data already has been sync it does sync changes to client server aging. Is it possible sync update records too?
  • Very helpful code.
    i did it and working fine in consol application.
  • Could not create a scope with name 'EmployeeScope' as a scope with that name already exists.

    this error show me
  • Hi
    Thank you for your sample
    It works fine for the first time but I have an error when I want to synchronize a second time
    An unhandled exception of type 'Microsoft.Synchronization.Data.DbProvisioningException' occurred in Microsoft.Synchronization.Data.SqlServer.dll

    Additional information: Could not create a scope with name 'EmployeeScope' as a scope with that name already exists.
    What is wrong ?

Add comment