A day with .Net

My day to day experince in .net

Synchronizing SQL Server Databases using Microsoft Sync Framework

Posted by vivekcek on July 8, 2012

Source code Download http://sdrv.ms/PKZNd9

Problem Statement
———————————

I am designing an inventory application. Which have server side application and client side application. Through server application we can add products, can create orders etc. The server side application use a sql server database. The client side applications are used by marketing executives in their laptops or tablets. These marketing executives will be always out of office. They may not have access to internet to connect to server. So a local sql database of the same schema of server database will be installed with the client application. Then we need to do a synchronization with server and client databases.After the synchronization client database will contain all the product data from server. Then the marketing executives meet their clients and create orders for product in their local database. Now when marketing executives reach the main office they need to push all the order information from their local database to server database. At the mean time new products may be added to server database that is to be pushed to client database. This process will continue.

The problem come under the category of occasionally connected systems(OCS).

I love Microsoft because they have solution for everything. i Choose the Microsoft Sync Framework.

Development Environment Setup
———————————————-

1. Visual Studio 2010

2. SQL Server 2008 or SQL Express

3. Download and install Microsoft Sync SDK 2.1 (http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=23217)

4. Add reference to the below library’s.

Microsoft.Synchronization.dll
Microsoft.Synchronization.Data.dll
Microsoft.Synchronization.Data.SqlServer.dll

5. Create a database named ‘SyncDB’ using the below script.

USE [master]
GO

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'SyncDB')
DROP DATABASE SyncDB

CREATE DATABASE [SyncDB] 
GO

USE [SyncDB]
GO

CREATE TABLE [dbo].[Products](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[ListPrice] [money] NOT NULL
      
      CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ([ID] ASC)
)

GO

CREATE TABLE [dbo].[Orders](
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Quantity] [int] NOT NULL,
[OriginState] [nvarchar](2) NOT NULL,
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ([OrderID] ASC,[ProductID] ASC)
)
GO

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ID])
GO

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Products]
GO

INSERT INTO Products VALUES (1, 'PC', 400)
INSERT INTO Products VALUES (2, 'Laptop', 600)
INSERT INTO Products VALUES (3, 'NetBook', 300)
INSERT INTO Orders VALUES (1, 1, 2, 'NC')
INSERT INTO Orders VALUES (2, 2, 1, 'NC')
INSERT INTO Orders VALUES (3, 1, 5, 'WA')
INSERT INTO Orders VALUES (3, 3, 10, 'WA')
INSERT INTO Orders VALUES (4, 2, 4, 'WA')

6. Create SyncClientDB.With out any tables in it.

HOW SYNC WORKS
——————————-

To synchronize SQL Server database with a SQL Client database we will complete the tasks below.

1. Define the scope based on the tables from the SQL Server database, then provision the SQL Server and SQL client databases.This will prepare the SQL Server and SQL client databases for Synchronization.
2. Synchronize the SQL Server and SQL client databases after they have been configured for synchronization as per step 1.
3. Optionally if you wish you can use the SqlSyncDeprovisioning class to deprovision the specified scope and remove all associated synchronization elements from the database.

1: Scope the SQL Server database and then provision SQL client databases.

 public static void SetUp()
        {
            // Connection to on  SQL Server database
            SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncDB; Trusted_Connection=Yes");

            // Connection to SQL client database
            SqlConnection clientConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncClientDB; Trusted_Connection=Yes");

            // Create a scope named "product" and add tables to it.
            DbSyncScopeDescription productScope = new DbSyncScopeDescription("product");

            // Select the colums to be included in the Collection Object
            Collection<string> includeColumns = new Collection<string>();
            includeColumns.Add("ID");
            includeColumns.Add("Name");
            includeColumns.Add("ListPrice");

            // Define the Products table.
            DbSyncTableDescription productDescription =
                                                    SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.Products",
                                                                                                        includeColumns, serverConn);

            // Add the Table to the scope object.    
            productScope.Tables.Add(productDescription);

            // Create a provisioning object for "product" and apply it to the on-premise database if one does not exist.
            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, productScope);


            // Filter Rows for the ListPrice column
            serverProvision.Tables["dbo.Products"].AddFilterColumn("ListPrice");
            serverProvision.Tables["dbo.Products"].FilterClause = "[side].[ListPrice] < '600'";

            if (!serverProvision.ScopeExists("product"))
                serverProvision.Apply();

            // Provision the SQL client database from the on-premise SQL Server database if one does not exist.
            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, productScope);


            if (!clientProvision.ScopeExists("product"))
                clientProvision.Apply();

            // Shut down database connections.

            serverConn.Close();

            serverConn.Dispose();

            clientConn.Close();

            clientConn.Dispose();
        }

2: Synchronize the SQL Server and SQL Client databases

public static void Synchronize()
        {
            // Connection to  SQL Server
            SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncDB; Trusted_Connection=Yes");

            // Connection to SQL client
            SqlConnection clientConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncClientDB; Trusted_Connection=Yes");

            // Perform Synchronization between SQL Server and the SQL client.
            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

            // Create provider for SQL Server
            SqlSyncProvider serverProvider = new SqlSyncProvider("product", serverConn);

            // Set the command timeout and maximum transaction size for the SQL Azure provider.
            SqlSyncProvider clientProvider = new SqlSyncProvider("product", clientConn);

            // Set Local provider of SyncOrchestrator to the server provider
            syncOrchestrator.LocalProvider = serverProvider;

            // Set Remote provider of SyncOrchestrator to the client provider
            syncOrchestrator.RemoteProvider = clientProvider;

            // Set the direction of SyncOrchestrator session to Upload and Download
            syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;

            // Create SyncOperations Statistics Object
            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

            // Display the 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);

            // Shut down database connections.
            serverConn.Close();
            serverConn.Dispose();
            clientConn.Close();
            clientConn.Dispose();

        }

FULL CODE
————————-

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;

namespace SyncDb
{
    class Program
    {
        static void Main(string[] args)
        {
            string answer = string.Empty;

            string selection = string.Empty;

            do
            {

                Console.WriteLine("\nEnter a choice below:");

                Console.WriteLine(@"Type 'setup' to scope the database");

                Console.WriteLine(@"Type 'sync' to sync the database");

                Console.WriteLine(@"Type 'delete' to remove scope of the database");

                Console.Write("Enter command: ");

                selection = Console.ReadLine();

                if (selection.Equals("setup"))
                    SetUp();
                else if (selection.Equals("sync"))
                    Synchronize();
                else if (selection.Equals("delete"))
                    Deprovision();

                Console.Write("Do you wish to continue? (y/n): ");
                answer = Console.ReadLine();

            }
            while (answer.Equals("y"));
        }
        public static void SetUp()
        {
            // Connection to on  SQL Server database
            SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncDB; Trusted_Connection=Yes");

            // Connection to SQL client database
            SqlConnection clientConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncClientDB; Trusted_Connection=Yes");

            // Create a scope named "product" and add tables to it.
            DbSyncScopeDescription productScope = new DbSyncScopeDescription("product");

            // Select the colums to be included in the Collection Object
            Collection<string> includeColumns = new Collection<string>();
            includeColumns.Add("ID");
            includeColumns.Add("Name");
            includeColumns.Add("ListPrice");

            // Define the Products table.
            DbSyncTableDescription productDescription =
                                                    SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.Products",
                                                                                                        includeColumns, serverConn);

            // Add the Table to the scope object.    
            productScope.Tables.Add(productDescription);

            // Create a provisioning object for "product" and apply it to the on-premise database if one does not exist.
            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, productScope);


            // Filter Rows for the ListPrice column
            serverProvision.Tables["dbo.Products"].AddFilterColumn("ListPrice");
            serverProvision.Tables["dbo.Products"].FilterClause = "[side].[ListPrice] < '600'";

            if (!serverProvision.ScopeExists("product"))
                serverProvision.Apply();

            // Provision the SQL client database from the on-premise SQL Server database if one does not exist.
            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, productScope);


            if (!clientProvision.ScopeExists("product"))
                clientProvision.Apply();

            // Shut down database connections.

            serverConn.Close();

            serverConn.Dispose();

            clientConn.Close();

            clientConn.Dispose();
        }
        public static void Synchronize()
        {
            // Connection to  SQL Server
            SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncDB; Trusted_Connection=Yes");

            // Connection to SQL client
            SqlConnection clientConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncClientDB; Trusted_Connection=Yes");

            // Perform Synchronization between SQL Server and the SQL client.
            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

            // Create provider for SQL Server
            SqlSyncProvider serverProvider = new SqlSyncProvider("product", serverConn);

            // Set the command timeout and maximum transaction size for the SQL Azure provider.
            SqlSyncProvider clientProvider = new SqlSyncProvider("product", clientConn);

            // Set Local provider of SyncOrchestrator to the server provider
            syncOrchestrator.LocalProvider = serverProvider;

            // Set Remote provider of SyncOrchestrator to the client provider
            syncOrchestrator.RemoteProvider = clientProvider;

            // Set the direction of SyncOrchestrator session to Upload and Download
            syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;

            // Create SyncOperations Statistics Object
            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

            // Display the 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);

            // Shut down database connections.
            serverConn.Close();
            serverConn.Dispose();
            clientConn.Close();
            clientConn.Dispose();

        }
        public static void Deprovision()
        {
            // Connection to  SQL Server database
            SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncDB; Trusted_Connection=Yes");

            // Connection to SQL client database
            SqlConnection clientConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncClientDB; Trusted_Connection=Yes");

            // Create Scope Deprovisioning for Sql Server and SQL client.
            SqlSyncScopeDeprovisioning serverSqlDepro = new SqlSyncScopeDeprovisioning(serverConn);
            SqlSyncScopeDeprovisioning clientSqlDepro = new SqlSyncScopeDeprovisioning(clientConn);

            // Remove the scope from SQL Server remove all synchronization objects.
            serverSqlDepro.DeprovisionScope("product");
            serverSqlDepro.DeprovisionStore();

            // Remove the scope from SQL client and remove all synchronization objects.
            clientSqlDepro.DeprovisionScope("product");
            clientSqlDepro.DeprovisionStore();

            // Shut down database connections.
            serverConn.Close();
            serverConn.Dispose();
            clientConn.Close();
            clientConn.Dispose();

        }
    }
}
Advertisements

10 Responses to “Synchronizing SQL Server Databases using Microsoft Sync Framework”

  1. Mithra Krishnan said

    I have tried you article but its throwing error and not working proeperly.
    I feels you are missing some code.
    Please check

  2. Telmo Sá said

    Hello.
    What if i want to use SQL change tracking?
    thank you.
    Telmo

  3. jaspal said

    “DbSyncScopeDescription” namespace not found

  4. Nagaraj said

    I have tried your article its working fine..

    I have few question regarding Sync.

    1. why sync provision creates the New table for Change Tracking instead of using built in code for Enable Change Tracking = True in each table using sq Server 2008 r2
    2. how to create a dynamic filter for Both Client and Server example if Sync it should sync only selected records from client and server side

    thanks & regards
    Nagaraj L
    Unisys

  5. Aashish said

    Thanks Vivek..its works

  6. Suren said

    Hi ,

    How to make this automated except batch file, windows scheduler…? please advice

  7. Neha Rana said

    Hi,

    Unable to Download….?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s