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)