Microsoft Sync Framework is a data synchronization platform, allowing data synchronization between any applications, services or devices regardless of the network used and data source type. To allow data source agnostic synchronization, Sync Framework uses synchronization providers for connecting to each of the data sources. Sync Framework database synchronization providers, similar to ADO.NET providers, allow other Sync Framework components to work without the need to concern on the underlying database implementation.

Note that Sync Framework is supported only in the Professional Edition of dotConnect for PostgreSQL.

• Sync Framework Support Overview
• Sample Application
• Database Provisioning
  • Creating Synchronization Scope without Filter
  • Creating Synchronization Scope with Filter
  • Creating Synchronization Scope with Filter Based on Template
• Synchronization
  • Provisioning Production Database
  • Synchronization
• Database Deprovisioning
  • Deprovisioning Synchronization Scope
  • Deprovisioning Template
  • Deprovisioning Storage

Sync Framework Support Overview

Sync Framework architecture allows clients both to synchronize data via the central server or to exchange data directly. The following main classes are used for data synchronization:

• SyncOrchestrator - creates and manages synchronization sessions. This class is provided by Sync Framework.
• DbSyncProvider - implements the synchronization service for interaction with a database and shields SyncOrchestrator from the specific implementation of the database. dotConnect for PostgreSQL provides its own PgSqlSyncProvider class implementation for PostgreSQL database.
• DbSyncAdapter - maintains the interaction between the DbSyncProvider and the database. This class is provided by Sync Framework.

To include database data to synchronization, first you need to provision the database. Provisioning means the creation of Sync Framework auxiliary tables, stored procedures, functions, and triggers for tracking data changes in the specified synchronization scope - a table or a group of tables to synchronize data from. Both databases being synchronized must be provisioned in order to synchronise data.

PgSqlSyncScopeProvisioning is the key class for performing database provisioning. It allows provisioning a synchronization scope. We will describe the provisioning and synchronization scopes in more details in our tutorial below.

After the provisioning, the databases can be synchronised. When synchronizing databases, you may retrieve synchronization operation statistics. It includes the time synchronization took, the number of processed changes and failed changes.

In case there is no need to synchronise a database or any specific scope any more, the database (or scope) can be deprovisioned. This means deleting of the unneeded Sync Framework database objects and/or data. User tables and data are retained. dotConnect for PostgreSQL offers the PgSqlSyncScopeDeprovisioning class for scope deprovisioning.

The parent class of the PgSqlSyncProvider class is the descendant of the standard Sync Framework DbSyncProvider class. Other synchronization classes of dotConnect for PostgreSQL are not inherited from the standard Sync Framework classes, but provide the same interface.

Sample Application

For this tutorial you need Visual Studio 2008 or higher, and Sync Framework 2.1.

In our tutorial we will synchronize two databases with identical structure. Let's call them the development database and the production database. We will use a simple database of one table "Product" for our tutorial. Here is its script:

CREATE table "Products"
  (
  "Id" INT NOT NULL PRIMARY KEY,
  "Name" VARCHAR(100),
  "CategoryId" INT,
  "IsAvailable" boolean
  );


INSERT INTO "Products"
  VALUES (1, 'HP 2000-2b19WM ', 1, true
  ), (2, 'Dell Adamo XPS', 1, false
  ), (3, 'DELL LAPTOP I14Z', 1, true
  ), (4, 'HP DC5750 Desktop Computer Dual Core/ ', 2, true
  ), (5, 'Apple iMac All In One', 2, false
  ), (6, 'Sharp - AQUOS - ', 3, true
  ), (7, 'Samsung ST76', 4, true
  ), (8, 'Canon PowerShot', 4, true
  ), (9, 'Olympus OM-D E-M5', 4, true
  );

If you want to configure data synchronization for a new database, and its tables are not created yet, they can be automatically generated during the provisioning. However, in our tutorial we will create them manually.

Create a new console application in Visual Studio. It could be any other project type as well, but for simplicity's sake we'll use console project throughout the tutorial.

Add the references to the following assemblies to your project:

• Devart.Data.Synchronization
• Devart.Data.PostgreSql.Synchronization
• Microsoft.Synchronization Version 2.1
• Microsoft.Synchronization.Data Version 3.1
• Devart.Data.PostgreSql

Database Provisioning

The first step of the synchronization is database provisioning. We will start from the development database and demonstrate different provisioning scenarios. Provisioning includes defining the synchronization scope and creating the necessary database objects.

The synchronization scope is one or more tables (with the specified columns to synchronize) that will be synchronized as a whole. The synchronization scope can include all columns in a table or just a subset of them. It can include all rows or only the rows that match some filter condition. You also can create a template for filters (a condition including a variable), and then create multiple synchronization scopes using filters based on this template.

When creating a synchronization scope, you must create descriptions for the tables of this scope. The synchronization scope tables can either already exist in the database, or they be created during the database provisioning based on their descriptions.

Creating Synchronization Scope without Filter

You can create table descriptions in two ways. The first way is to create the description manually, and the second is to use the PgSqlSyncDescriptionBuilder class to generate the description based on an existing database table. Since the tables are already created, we will use the second option.

Add the following code to the Main method:

      // Specify the connection to development database. Replace the sample connection string with your one.
      PgSqlConnection devConnection = new PgSqlConnection(@"Database=development;host=devserver;user id=postgres;password=password");    

      // Define the scope, named ProductsScope
      DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("ProductsScope");

      // Retrive the description for the Products table from the database 
      DbSyncTableDescription tableDesc = PgSqlSyncDescriptionBuilder.GetDescriptionForTable("Products", devConnection);

      // Add the table description to the scope definition
      scopeDesc.Tables.Add(tableDesc);

      // Create the scope
      PgSqlSyncScopeProvisioning devProvision = new PgSqlSyncScopeProvisioning(devConnection, scopeDesc);

      // Start the provision
      devProvision.Apply();

      ' Specify the connection to development database. Replace the sample connection string with your one.
      Dim devConnection As New PgSqlConnection("Database=development;host=devserver;user id=postgres;password=password")

      ' Define the scope, named ProductsScope
      Dim scopeDesc As New DbSyncScopeDescription("ProductsScope")

      ' Retrive the description for the Products table from the database 
      Dim tableDesc As DbSyncTableDescription = PgSqlSyncDescriptionBuilder.GetDescriptionForTable("Products", devConnection)

      ' Add the table description to the scope definition
      scopeDesc.Tables.Add(tableDesc)

      ' Create the scope
      Dim devProvision As New PgSqlSyncScopeProvisioning(devConnection, scopeDesc)

      ' Start the provision
      devProvision.Apply()

After executing this code, Sync Framework creates the following database objects in the database:

• schema_version table - contains the information on the version of Sync Framework that provisioned the database.
• scope_info table - contains the list of all the provisioned scopes.
• scope_config table - contains scope configuration information.
• products_tracking the table with metadata of the provisioned table "products".
• Stored procedures and functions for selecting/inserting the updated strings and metadata updates.
• Insert/update/delete triggers for tracking changes in the provisioned table.

Creating Synchronization Scope with Filter

Now we will demonstrate creating synchronization scope with a filter. The filter is just a SQL WHERE condition that allows synchronizing not all the data of a table, but only the rows that meets certain condition. This filter condition is added to the selectchanges procedure that gets the changed data.

The following code creates the synchronization scope with the filter.

      //Specify connection to dev database
      PgSqlConnection devConnection = new PgSqlConnection(@"Database=development;host=devserver;user id=postgres;password=password");

      DbSyncScopeDescription devScope = new DbSyncScopeDescription("DevCategoryScope");
      DbSyncTableDescription tableDesc = PgSqlSyncDescriptionBuilder.GetDescriptionForTable("Products", devConnection);
      devScope.Tables.Add(tableDesc);

      SyncScopeProvisioning devProvision = new PgSqlSyncScopeProvisioning(devConnection, devScope);

      //Specify filter clause
      devProvision.Tables["Products"].AddFilterColumn("CategoryId");

      devProvision.Tables["Products"].FilterClause = "t.CategoryId = 1";

      //Skip create Sync Framework objects because we have already created them on the previous step
      devProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
      //Create new selectchanges procedure for our scope
      devProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
          
      devProvision.Apply();

      'Specify connection to dev database
      Dim devConnection As New PgSqlConnection("Database=development;host=devserver;user id=postgres;password=password")

      Dim devScope As New DbSyncScopeDescription("DevCategoryScope")
      Dim tableDesc As DbSyncTableDescription = PgSqlSyncDescriptionBuilder.GetDescriptionForTable("Products", devConnection)
      devScope.Tables.Add(tableDesc)

      Dim devProvision As SyncScopeProvisioning = New PgSqlSyncScopeProvisioning(devConnection, devScope)

      'Specify filter clause
      devProvision.Tables("Products").AddFilterColumn("CategoryId")

      devProvision.Tables("Products").FilterClause = "t.CategoryId = 1"

      'Skip create Sync Framework objects because we have already created them on the previous step
      devProvision.SetCreateTableDefault(DbSyncCreationOption.Skip)
      'Create new selectchanges procedure for our scope
      devProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create)

      devProvision.Apply()

The AddFilterColumn call specifies the column from the provisioned table, that will be used in the filter and must be added to the tracking table. FilterClause specifies the condition. Note that we use the alias t when specifying the condition. This alias refers to the tracking table.

After this code is executed, the specified filter column is added to the products_tracking tracking table. An updated products_insert trigger will set values to this column. Sync Framework also creates the stored procedure for selecting changes with the specified filter applied.

Creating Synchronization Scope with Filter Based on Template

Creating a filter template is similar to creating a synchronization scope with a filter. The difference is that the filter condition uses a parameter instead of value. You can then create multiple synchronization scopes based on this template by supplying a value that will substitute this parameter in the filter condition. The template itself cannot be used for synchronization.

For example, in our tutorial we will create an IsAvailableTemplate template with the condition "t.Available = f_Available" where f_Available is a parameter of a boolean type. We will create two synchronization scopes by providing values for this parameter. They will be the AvailableScope scope with f_Available = true and NotAvailableScope with f_Available = false.

Here is the code for creating the template:

      PgSqlConnection devConnection = new PgSqlConnection(@"Database=development;host=devserver;user id=postgres;password=password");

      // Create a template named "IsAvaibleTemplate"
      DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("IsAvaibleTemplate");

      // Definition for tables.
      DbSyncTableDescription productsDescription =
          PgSqlSyncDescriptionBuilder.GetDescriptionForTable("Products", devConnection);
      scopeDesc.Tables.Add(productsDescription);

      SyncScopeProvisioning templateProvisioning = new PgSqlSyncScopeProvisioning(devConnection, scopeDesc, SyncScopeProvisioningType.Template);

      templateProvisioning.Tables[0].AddFilterColumn("IsAvailable");
      templateProvisioning.Tables[0].FilterClause = "t.\"IsAvailable\" = f_available";
      templateProvisioning.Tables[0].FilterParameters.Add(new PgSqlParameter("f_available", PgSqlType.Boolean));

      templateProvisioning.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);      

      templateProvisioning.Apply();

      Dim devConnection As New PgSqlConnection("Database=development;host=devserver;user id=postgres;password=password")

      ' Create a template named "IsAvaibleTemplate"
      Dim scopeDesc As New DbSyncScopeDescription("IsAvaibleTemplate")

      ' Definition for tables.
      Dim productsDescription As DbSyncTableDescription = PgSqlSyncDescriptionBuilder.GetDescriptionForTable("Products", devConnection)
      scopeDesc.Tables.Add(productsDescription)

      Dim templateProvisioning As SyncScopeProvisioning = New PgSqlSyncScopeProvisioning(devConnection, scopeDesc, SyncScopeProvisioningType.Template)

      templateProvisioning.Tables(0).AddFilterColumn("IsAvailable")
      templateProvisioning.Tables(0).FilterClause = "t.""IsAvailable"" = f_available"
      templateProvisioning.Tables(0).FilterParameters.Add(New PgSqlParameter("f_available", PgSqlType.[Boolean]))

      templateProvisioning.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create)

      templateProvisioning.Apply()

After executing this code, the new selectchanges procedure will contain the parameter in the filtering condition.

And now let's create synchronization scopes based on the template. To create such scope, you need to call the PopulateFromTemplate method and pass the new scope name, the name of the template, and the value of the filter parameter to it.

       PgSqlConnection devConnection = new PgSqlConnection(@"Database=development;host=devserver;user id=postgres;password=password");

      // Create a synchronization scope
       PgSqlSyncScopeProvisioning devProvAvailable = new  PgSqlSyncScopeProvisioning(devConnection);

      // Populate the scope description from template
      devProvAvailable.PopulateFromTemplate("AvaibleScope", "IsAvaibleTemplate");

      // Set value to the parameter
      devProvAvailable.Tables["Products"].FilterParameters["f_available"].Value = true;

      // Set description for template.
      devProvAvailable.UserComment = "Scope for available products";
     
      devProvAvailable.Apply();

      Dim devConnection As New  PgSqlConnection("Database=development;host=devserver;user id=postgres;password=password")

      ' Create a synchronization scope
      Dim devProvAvailable As New  PgSqlSyncScopeProvisioning(devConnection)

      ' Populate the scope description from template
      devProvAvailable.PopulateFromTemplate("AvaibleScope", "IsAvaibleTemplate")

      ' Set value to the parameter
      devProvAvailable.Tables("Products").FilterParameters("f_available").Value = True

      ' Set description for template.
      devProvAvailable.UserComment = "Scope for available products"

      devProvAvailable.Apply()

The code for creating the NotAvaibleScope is almost the same, just change the scope name and the filter parameter value.

Creating Synchronization Scope with Subset of Columns of Existing Table

Here is an example of creating synchronization scope for synchronizing data in only a subset of table columns.

      PgSqlConnection devConnection = new PgSqlConnection(@"Database=development;host=devserver;user id=postgres;password=password");
      
      // Define scope with name ProductsScope"
      DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("ProductsScopeWithColumnSpec");

      // Specify the list of columns of the Products Table to include
      Collection<string> columnsToInclude = new Collection<string>();
      columnsToInclude.Add("Id");
      columnsToInclude.Add("Name");
      columnsToInclude.Add("CategoryId");
      columnsToInclude.Add("Available");

      // Retrive description for Products with specified columns from database 
      DbSyncTableDescription tableDesc =
          PgSqlSyncDescriptionBuilder.GetDescriptionForTable("Products", columnsToInclude, devConnection);

      // add the table description to the sync scope definition
      scopeDesc.Tables.Add(tableDesc);

      // create a development scope provisioning object based on the OrdersScope
      PgSqlSyncScopeProvisioning devProvision = new PgSqlSyncScopeProvisioning(devConnection, scopeDesc);

      // start the provision
      devProvision.Apply();

      Dim devConnection As New PgSqlConnection("Database=development;host=devserver;user id=postgres;password=password")

      ' Define scope with name ProductsScope"
      Dim scopeDesc As New DbSyncScopeDescription("ProductsScopeWithColumnSpec")

      ' Specify the list of columns of the Products table to include
      Dim columnsToInclude As New Collection(Of String)()
      columnsToInclude.Add("Id")
      columnsToInclude.Add("Name")
      columnsToInclude.Add("CategoryId")
      columnsToInclude.Add("Available")

      ' Retrive description for Products with specified columns from database 
      Dim tableDesc As DbSyncTableDescription = PgSqlSyncDescriptionBuilder.GetDescriptionForTable("Products", columnsToInclude, devConnection)

      ' add the table description to the sync scope definition
      scopeDesc.Tables.Add(tableDesc)

      ' create a development scope provisioning object based on the OrdersScope
      Dim devProvision As New PgSqlSyncScopeProvisioning(devConnection, scopeDesc)

      ' start the provision
      devProvision.Apply()

Synchronization

To synchronize databases you need first to provision the Production database, and then to actually synchronize the databases.

Provisioning Production Database

When provisioning the production database, the DbSyncScopeDescription class is used to specify the synchronization scope name and tables to synchronize. The latter can be either specified explicitly or retrieved from the already provisioned development database.

     PgSqlConnection productionConnection = new PgSqlConnection(@"Database=development;host=prodserver;user id=postgres;password=password");
     PgSqlConnection devConnection = new PgSqlConnection(@"Database=development;host=devserver;user id=postgres;password=password");

     //Get definition for ProductsScope from dev
     DbSyncScopeDescription scopeDescription = PgSqlSyncDescriptionBuilder.GetDescriptionForScope("DevCategoryScope", devConnection);
     
     //Provise production using this definition
     SyncScopeProvisioning productionProvisioning = new PgSqlSyncScopeProvisioning(productionConnection, scopeDescription);

      productionProvisioning.Apply();

     Dim productionConnection As New PgSqlConnection("Database=development;host=prodserver;user id=postgres;password=password")
     Dim devConnection As New PgSqlConnection("Database=development;host=devserver;user id=postgres;password=password")

     'Get definition for ProductsScope from dev
     Dim scopeDescription As DbSyncScopeDescription = PgSqlSyncDescriptionBuilder.GetDescriptionForScope("DevCategoryScope", devConnection)

     'Provise production using this definition
     Dim productionProvisioning As SyncScopeProvisioning = New PgSqlSyncScopeProvisioning(productionConnection, scopeDescription)

     productionProvisioning.Apply()

Synchronization

Synchronization is performed via the SyncOrchestrator class. You need to assign the synchronization provider instances to its RemoteProvider and LocalProvider properties and then run its Synchronize method.

      PgSqlConnection productionConnection = new  PgSqlConnection(@"Database=development;host=prodserver;user id=postgres;password=password");
      PgSqlConnection devConnection = new  PgSqlConnection(@"Database=development;host=devserver;user id=postgres;password=password");

      SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

      //Specify source database
      syncOrchestrator.RemoteProvider = new PgSqlSyncProvider("DevCategoryScope", devConnection, null, null);
      
      //Specify production database
      syncOrchestrator.LocalProvider = new PgSqlSyncProvider("DevCategoryScope", productionConnection, null, null);

      syncOrchestrator.Synchronize();

      Dim productionConnection As New PgSqlConnection("Database=development;host=prodserver;user id=postgres;password=password")
      Dim devConnection As New PgSqlConnection("Database=development;host=devserver;user id=postgres;password=password")

      Dim syncOrchestrator As New SyncOrchestrator()

      'Specify source database
      syncOrchestrator.RemoteProvider = New PgSqlSyncProvider("DevCategoryScope", devConnection, Nothing, Nothing)

      'Specify production database
      syncOrchestrator.LocalProvider = New PgSqlSyncProvider("DevCategoryScope", productionConnection, Nothing, Nothing)

      syncOrchestrator.Synchronize()

Database Deprovisioning

When deprovisioning the database, the objects, created by Sync Framework, that are not used any more are deleted. If there is at least one scope that still uses these objects, they will not be deleted. User database objects are not deleted at all. You can deprovision a provisioned scope, a template with all scopes built on it, or the whole database.

Deprovisioning Synchronization Scope

To deprovision a single synchronization scope you need to create a PgSqlSyncScopeDeprovisioning object and execute its DeprovisionScope method, which accepts the scope name as its parameter.

Deprovisioning Template

      PgSqlConnection devConnection = new PgSqlConnection(@"Database=development;host=devserver;user id=postgres;password=password");

      // Remove the "IsAvaibleTemplate" template from the dev database.       
      PgSqlSyncScopeDeprovisioning templateDeprovision = new PgSqlSyncScopeDeprovisioning(devConnection);

      // Remove the scope.
      templateDeprovision.DeprovisionTemplate("IsAvaibleTemplate");

      Dim devConnection As New PgSqlConnection("Database=development;host=devserver;user id=postgres;password=password")

      ' Remove the "IsAvaibleTemplate" template from the dev database.       
      Dim templateDeprovision As New PgSqlSyncScopeDeprovisioning(devConnection)

      ' Remove the scope.
      templateDeprovision.DeprovisionTemplate("IsAvaibleTemplate")

Deprovisioning Storage

Storage deprovisioning removes all the templates, synchronization scopes, and all the Sync Framework database objects in the connection schema. The DeprovisionStore method is used for storage deprovisioning.

See Also

Entity Framework section  | Entity Framework Support Overview