How to: Configure Change Tracking and Synchronize Peers

This topic describes the key parts of an application that uses Sync Services for ADO.NET 2.0 to synchronize three instances of SQL Server. The code in this application focuses on the following Sync Services classes:

• DbSyncAdapter

• DbSyncProvider

• SyncOrchestrator

• SyncOperationStatistics

Each of these classes is described in Architecture and Classes for Peer-to-Peer Synchronization. The SyncOrchestrator and SyncOperationStatistics classes are documented in the Sync Framework core documentation at this Microsoft Web site.

Configuring synchronization involves the following steps:

1. Creating tracking tables to store metadata

2. Creating stored procedures to update data and metadata

3. Initializing each database with schema and change tracking infrastructure

Executing synchronization involves the following steps:

1. Creating peer synchronization providers and synchronization adapters

2. Creating a synchronization orchestrator, and synchronizing the peers

The code examples in this topic are from an application that includes three peer databases. The databases are always synchronized as pairs: SyncSamplesDb_Peer1 and SyncSamplesDb_Peer2; SyncSamplesDb_Peer2 and SyncSamplesDb_Peer3; and SyncSamplesDb_Peer1 and SyncSamplesDb_Peer3. For a production application, a copy of the application would typically be deployed to each peer so that synchronization could be initiated from any of the peers.

Creating Tracking Tables to Store Metadata

Peer-to-peer synchronization requires a way to identify which rows should be synchronized during a particular synchronization session. Each table must have a primary key. Consider the following code example. It shows the Sales.Customer table schema in the SyncSamplesDb_Peer1 database.

CREATE TABLE Sales.Customer(
    CustomerId uniqueidentifier NOT NULL PRIMARY KEY DEFAULT NEWID(), 
    CustomerName nvarchar(100) NOT NULL,
    SalesPerson nvarchar(100) NOT NULL,
    CustomerType nvarchar(100) NOT NULL)

Each table that you synchronize has an associated DbSyncAdapter object. This is described in the section "Creating Peer Synchronization Providers and Synchronization Adapters" later in this topic. Add the CustomerId primary key to the RowIdColumns collection of the DbSyncAdapter object as follows: adapterCustomer.RowIdColumns.Add("CustomerId"). To view this code in context, see the complete code example at the end of this topic.

In addition to a primary key, Sync Services synchronization requires a way to track which rows have changed since the previous synchronization session between two peers. There are two main activities involved in tracking changes:

• Tracking inserts, updates, and deletes for each table that is synchronized.

  This can be handled by using coupled or decoupled change tracking. Coupled change tracking means that change-tracking metadata for inserts and updates is stored in the base table, with a tombstone table to track deletes. Decoupled change tracking means that metadata for inserts, updates, and deletes is stored in a separate table (typically one table for each base table). Set the kind of change tracking by specifying a value for the ChangeTracking property. With either kind of change tracking, the commands that you specify for the DbSyncAdapter object use change tracking metadata to determine the incremental changes that have been made at each peer.

  The examples in this documentation use decoupled change tracking. For information about a sample that uses coupled tracking, see Sample Applications for Peer-to-Peer Synchronization.

  Note

  You can use different kinds of change tracking for different peer providers. However, you must use the same kind of change tracking for all tables that are synchronized by a particular peer provider. In addition to the tracking approaches described in this topic, you can use SQL Server change tracking with peer-to-peer synchronization. However, Sync Services requires the ability to update change tracking metadata for peer-to-peer synchronization, and SQL Server change tracking tables are read-only. To use SQL Server change tracking requires that you maintain a separate metadata table that Sync Services can update. For information about how to use SQL Server change tracking with client and server synchronization, see How to: Use SQL Server Change Tracking.

• Tracking which changes each peer has received from other peers.

  This is typically handled by a single table in each peer database. This table stores synchronization knowledge in a binary format for each scope. Sync Services uses knowledge to determine which changes to send to each peer during synchronization. Applications do not have to work with knowledge directly. A scope can be thought of as a logical grouping of tables. Consider a synchronization topology with three peers:

  1. Peer1 and Peer2 synchronize all changes.

  2. Peer1 synchronizes with Peer3.

  3. A user performs an update at Peer2.

  4. Peer3 synchronizes with Peer2.

  When Peer3 synchronizes with Peer2, Peer3 already has most of the changes from Peer2, because Peer3 synchronized with Peer1 first. Knowledge enables Sync Services to recognize this, and to synchronize only the update that occurred at Peer2. For more information about knowledge, see the Sync Framework core documentation.

The following code example creates a table that tracks changes for the Sales.Customer table.

CREATE TABLE Sales.Customer_Tracking(
    CustomerId uniqueidentifier NOT NULL PRIMARY KEY,          
    sync_row_is_tombstone int DEFAULT 0,
    sync_row_timestamp timestamp, 
    sync_update_peer_key int DEFAULT 0,
    sync_update_peer_timestamp bigint,        
    sync_create_peer_key int DEFAULT 0,
    sync_create_peer_timestamp bigint,
    last_change_datetime datetime DEFAULT GETDATE())

The base table and tracking tables must be present at each peer. The primary key of the tracking table is the same as in the base table, and additional columns are required. These additional columns are described in the following table. The names of the additional columns do not have to be the same as those listed; they must match the queries or procedures that access the tracking tables.

The following code example creates a trigger that updates change tracking metadata in the Sales.Customer_Tracking table when an update is made to the Sales.Customer table. For examples of insert and delete triggers, see Setup Scripts for Sync Services How-to Topics.

CREATE TRIGGER Customer_UpdateTrigger ON Sales.Customer FOR UPDATE
AS    
    UPDATE t    
    SET sync_update_peer_key = 0, 
        sync_update_peer_timestamp = @@DBTS + 1,        
        last_change_datetime = GETDATE()
    FROM Sales.Customer_Tracking t JOIN inserted i ON t.CustomerId = i.CustomerId       

The following code example creates a Sales.ScopeInfo table and inserts a scope called Sales. The Sales.ScopeInfo table stores synchronization knowledge for each scope that is defined. The scope does not necessarily correspond to the database schema Sales.

CREATE TABLE Sales.ScopeInfo(      
    scope_id uniqueidentifier DEFAULT NEWID(),  
    scope_name nvarchar(100) NULL,
    scope_sync_knowledge varbinary(max) NULL,
    scope_tombstone_cleanup_knowledge varbinary(max) NULL,
    scope_timestamp timestamp)

SET NOCOUNT ON
INSERT INTO Sales.ScopeInfo(scope_name) VALUES (''Sales'')
SET NOCOUNT OFF

Creating Stored Procedures to Update Data and Metadata

After you create metadata tables for each peer, create Transact-SQL queries or stored procedures (recommended) to apply changes to the base tables and metadata tables at each peer. These queries or procedures are specified for the following DbSyncAdapter properties. These are described in the section "Creating Peer Synchronization Providers and Synchronization Adapters."

• SelectIncrementalChangesCommand

• InsertCommand

• UpdateCommand

• DeleteCommand

• InsertMetadataCommand

• UpdateMetadataCommand

• DeleteMetadataCommand

• SelectRowCommand

• SelectMetadataForCleanupCommand

The following code examples create a set of stored procedures to handle data and metadata changes for the Sales.Customer table. For brevity, the procedures to select data and to handle updates are included, but those for inserts and deletes are not. For examples of insert and delete procedures, see Setup Scripts for Sync Services How-to Topics.

In the complete code example at the end of this topic, many of the values that are passed to these procedures come from session variables. These are built-in variables that enable Sync Services to pass values to commands during a synchronization session. For more information about session variables, see How to: Use Session Variables for Peer-to-Peer Synchronization.

Procedure for SelectIncrementalChangesCommand

CREATE PROCEDURE Sales.sp_Customer_SelectChanges (             
        @sync_min_timestamp bigint,     
        @sync_metadata_only int,
        @sync_initialize int)
AS    

    --IF @sync_initialize = 0
    --BEGIN
        -- Perform additional logic if required.
    --END
    
    SELECT  t.CustomerId, 
            c.CustomerName,
            c.SalesPerson,
            c.CustomerType,                 
            t.sync_row_is_tombstone, 
            t.sync_row_timestamp,                          
            t.sync_update_peer_key, 
            t.sync_update_peer_timestamp, 
            t.sync_create_peer_key, 
            t.sync_create_peer_timestamp 
    FROM Customer c RIGHT JOIN Customer_Tracking t ON c.CustomerId = t.CustomerId
    WHERE t.sync_row_timestamp > @sync_min_timestamp     
    ORDER BY t.CustomerId ASC

Procedure for UpdateCommand

CREATE PROCEDURE Sales.sp_Customer_ApplyUpdate (                                   
        @CustomerId uniqueidentifier,
        @CustomerName nvarchar(100),
        @SalesPerson nvarchar(100),
        @CustomerType nvarchar(100),
        @sync_min_timestamp bigint ,                                
        @sync_row_count int OUT,
        @sync_force_write int)        
AS      
    UPDATE c
    SET c.CustomerName = @CustomerName, c.SalesPerson = @SalesPerson, c.CustomerType = @CustomerType      
    FROM Customer c JOIN Customer_Tracking t ON c.CustomerId = t.CustomerId
    WHERE ((t.sync_row_timestamp <= @sync_min_timestamp) OR @sync_force_write = 1)
        AND t.CustomerId = @CustomerId  
    SET @sync_row_count = @@rowcount

Procedure for UpdateMetadataCommand

CREATE PROCEDURE Sales.sp_Customer_UpdateMetadata (
        @CustomerId uniqueidentifier,
        @sync_create_peer_key int,
        @sync_create_peer_timestamp bigint,                 
        @sync_update_peer_key int,
        @sync_update_peer_timestamp timestamp,                      
        @sync_row_timestamp timestamp,
        @sync_check_concurrency int,
        @sync_row_count int OUT)        
AS          
    UPDATE Customer_Tracking SET
        sync_create_peer_key = @sync_create_peer_key, 
        sync_create_peer_timestamp =  @sync_create_peer_timestamp,
        sync_update_peer_key = @sync_update_peer_key, 
        sync_update_peer_timestamp =  @sync_update_peer_timestamp 
    WHERE CustomerId = @CustomerId AND 
        (@sync_check_concurrency = 0 OR sync_row_timestamp = @sync_row_timestamp)
    SET @sync_row_count = @@rowcount

Procedure for SelectRowCommand

CREATE PROCEDURE Sales.sp_Customer_SelectRow
        @CustomerId uniqueidentifier
AS
   SELECT  t.CustomerId, 
           c.CustomerName,
           c.SalesPerson,
           c.CustomerType,                 
           t.sync_row_timestamp, 
           t.sync_row_is_tombstone,
           t.sync_update_peer_key, 
           t.sync_update_peer_timestamp, 
           t.sync_create_peer_key, 
           t.sync_create_peer_timestamp 
    FROM Customer c RIGHT JOIN Customer_Tracking t ON c.CustomerId = t.CustomerId 
    WHERE t.CustomerId = @CustomerId 

Procedure for SelectMetadataForCleanupCommand

CREATE PROCEDURE Sales.sp_Customer_SelectMetadata     
    @metadata_aging_in_hours int
AS
    IF @metadata_aging_in_hours = -1
        BEGIN
            SELECT CustomerId,
                   sync_row_timestamp,         
                   sync_update_peer_key, 
                   sync_update_peer_timestamp, 
                   sync_create_peer_key, 
                   sync_create_peer_timestamp 
            FROM Customer_Tracking
            WHERE sync_row_is_tombstone = 1
        END
    
    ELSE
        BEGIN
            SELECT CustomerId,
                   sync_row_timestamp,         
                   sync_update_peer_key, 
                   sync_update_peer_timestamp, 
                   sync_create_peer_key, 
                   sync_create_peer_timestamp 
            FROM Customer_Tracking
            WHERE sync_row_is_tombstone = 1 AND
            DATEDIFF(hh, last_change_datetime, GETDATE()) > @metadata_aging_in_hours
        END

Initializing Each Database with Schema and Change Tracking Infrastructure

Initializing a peer database involves copying to each peer the table schema and change tracking infrastructure, and any initial data that is required. For peer-to-peer synchronization, Sync Services does not automatically create table schema or tracking infrastructure in the peer databases. An application must ensure that these objects exist before it tries to synchronize peers. You can use backup and restore or another technology to copy the objects to each peer, but you should do so only if changes are not occurring in the database from which the backup is taken. If the first peer database is in use and being updated, we strongly recommend that you copy only schema and change tracking infrastructure to each peer, and use Sync Services to copy the data. If you copy the data by using another method, a peer might miss changes that have been committed at the first peer. Sync Services can initialize the data at each peer as long as at least one peer has the data. The example code in this topic uses this approach: each peer database contains two tables, but only the tables in SyncSamplesDb_Peer1 contain data. The data is copied to the other peers during the first synchronization session.

Creating Peer Synchronization Providers and Synchronization Adapters

Now that the change tracking infrastructure is in place, you can focus on the code that configures synchronization. The bulk of the synchronization configuration code involves two kinds of objects: a DbSyncAdapter object for each table, and a DbSyncProvider object for each peer that will synchronize during a session.

Synchronization Adapter Commands

The following commands must be set on the DbSyncAdapter object for each table.

The following code examples create commands that call the stored procedures that are described earlier in this topic.

Application Code for SelectIncrementalChangesCommand

SqlCommand chgsCustomerCmd = new SqlCommand();
chgsCustomerCmd.CommandType = CommandType.StoredProcedure;
chgsCustomerCmd.CommandText = "Sales.sp_Customer_SelectChanges";
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int);            

adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd;

Dim chgsCustomerCmd As New SqlCommand()

With chgsCustomerCmd
    .CommandType = CommandType.StoredProcedure
    .CommandText = "Sales.sp_Customer_SelectChanges"
    .Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
    .Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
    .Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int)
End With

adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd

Application Code for UpdateCommand

SqlCommand updCustomerCmd = new SqlCommand();
updCustomerCmd.CommandType = CommandType.StoredProcedure;
updCustomerCmd.CommandText = "Sales.sp_Customer_ApplyUpdate";
updCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);

adapterCustomer.UpdateCommand = updCustomerCmd;

Dim updCustomerCmd As New SqlCommand()

With updCustomerCmd
    .CommandType = CommandType.StoredProcedure
    .CommandText = "Sales.sp_Customer_ApplyUpdate"
    .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
    .Parameters.Add("@CustomerName", SqlDbType.NVarChar)
    .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
    .Parameters.Add("@CustomerType", SqlDbType.NVarChar)
    .Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
    .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
    .Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With

adapterCustomer.UpdateCommand = updCustomerCmd

Application Code for UpdateMetadataCommand

SqlCommand updMetadataCustomerCmd = new SqlCommand();
updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
updMetadataCustomerCmd.CommandText = "Sales.sp_Customer_UpdateMetadata";
updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd;

Dim updMetadataCustomerCmd As New SqlCommand()

With updMetadataCustomerCmd
    .CommandType = CommandType.StoredProcedure
    .CommandText = "Sales.sp_Customer_UpdateMetadata"
    .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
    .Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
    .Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
    .Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
    .Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
    .Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
    .Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
    .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With

adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd

Application Code for SelectRowCommand

SqlCommand selRowCustomerCmd = new SqlCommand();
selRowCustomerCmd.CommandType = CommandType.StoredProcedure;
selRowCustomerCmd.CommandText = "Sales.sp_Customer_SelectRow";
selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);

adapterCustomer.SelectRowCommand = selRowCustomerCmd;

Dim selRowCustomerCmd As New SqlCommand()

With selRowCustomerCmd
    .CommandType = CommandType.StoredProcedure
    .CommandText = "Sales.sp_Customer_SelectRow"
    .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
End With

adapterCustomer.SelectRowCommand = selRowCustomerCmd

Application Code for SelectMetadataForCleanupCommand

SqlCommand selMetadataCustomerCmd = new SqlCommand();
selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
selMetadataCustomerCmd.CommandText = "Sales.sp_Customer_SelectMetadata";
selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_hours", SqlDbType.Int).Value = MetadataAgingInHours;

adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd;

Dim selMetadataCustomerCmd As New SqlCommand()

With selMetadataCustomerCmd
    .CommandType = CommandType.StoredProcedure
    .CommandText = "Sales.sp_Customer_SelectMetadata"
    .Parameters.Add("@metadata_aging_in_hours", SqlDbType.Int).Value = MetadataAgingInHours
End With

adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd

Synchronization Provider Commands

The following commands must be set on the DbSyncProvider object for each peer.

The following code example creates a command for the SelectNewTimestampCommand property. The commands for the SelectScopeInfoCommand and UpdateScopeInfoCommand properties are included in the complete code example at the end of this topic.

Application Code for SelectNewTimestampCommand

SqlCommand selectNewTimestampCommand = new SqlCommand();
string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1";
selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;

peerProvider.SelectNewTimestampCommand = selectNewTimestampCommand;

Dim newTimestampVariable As String = "@" + DbSyncSession.SyncNewTimestamp

Dim selectNewTimestampCommand As New SqlCommand()

With selectNewTimestampCommand
    .CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1"
    .Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
    .Parameters(newTimestampVariable).Direction = ParameterDirection.Output
End With

peerProvider.SelectNewTimestampCommand = selectNewTimestampCommand

Creating a Synchronization Orchestrator and Synchronizing the Peers

The code described in the previous section of this topic configured synchronization for each peer. Now it is time to synchronize peers. The following code example instantiates two DbSyncProvider objects and calls a SetupSyncProvider method that was created for the sample application. This method shows a way to configure a peer provider and synchronization adapter. The code then specifies which provider is the local provider and which is the remote provider. Finally, the code specifies that changes are first uploaded from the remote database to the local database and then downloaded in the other direction.

public class SampleSyncAgent : SyncOrchestrator
{
    public SampleSyncAgent(string localProviderConnString, string remoteProviderConnString)
    {

        //Instantiate the sample provider that allows us to create a provider
        //for both of the peers that are being synchronized.
        SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();

        //Instantiate a DbSyncProvider for the local peer and the remote peer.
        //For example, if this code is running at peer1 and is
        //synchronizing with peer2, peer1 would be the local provider
        //and peer2 the remote provider.
        DbSyncProvider localProvider = new DbSyncProvider();
        DbSyncProvider remoteProvider = new DbSyncProvider();

        //Create a provider by using the SetupSyncProvider on the sample class.             
        sampleSyncProvider.SetupSyncProvider(localProviderConnString, localProvider);
        localProvider.SyncProviderPosition = SyncProviderPosition.Local;
        
        sampleSyncProvider.SetupSyncProvider(remoteProviderConnString, remoteProvider);
        remoteProvider.SyncProviderPosition = SyncProviderPosition.Remote;

        //Specify the local and remote providers that should be synchronized,
        //and the direction and order of changes. In this case, changes are first
        //uploaded from remote to local and then downloaded in the other direction.
        this.LocalProvider = localProvider;
        this.RemoteProvider = remoteProvider;
        this.Direction = SyncDirectionOrder.UploadAndDownload;
    }

Public Class SampleSyncAgent
    Inherits SyncOrchestrator

    Public Sub New(ByVal localProviderConnString As String, ByVal remoteProviderConnString As String)

        'Instantiate the sample provider that allows us to create a provider
        'for both of the peers that are being synchronized.
        Dim sampleSyncProvider As New SampleSyncProvider()

        'Instantiate a DbSyncProvider for the local peer and the remote peer.
        'For example, if this code is running at peer1 and is
        'synchronizing with peer2, peer1 would be the local provider
        'and peer2 the remote provider.  
        Dim localProvider As New DbSyncProvider()
        Dim remoteProvider As New DbSyncProvider()

        'Create a provider by using the SetupSyncProvider on the sample class.
        sampleSyncProvider.SetupSyncProvider(localProviderConnString, localProvider)
        localProvider.SyncProviderPosition = SyncProviderPosition.Local

        sampleSyncProvider.SetupSyncProvider(remoteProviderConnString, remoteProvider)
        remoteProvider.SyncProviderPosition = SyncProviderPosition.Remote

        'Specify the local and remote providers that should be synchronized,
        'and the direction and order of changes. In this case, changes are first
        'uploaded from local to remote and then downloaded in the other direction.
        Me.LocalProvider = localProvider
        Me.RemoteProvider = remoteProvider
        Me.Direction = SyncDirectionOrder.UploadAndDownload

    End Sub 'New
End Class 'SampleSyncAgent

The following code sets up three synchronization sessions by instantiating the SampleSyncAgent class (which derives from SyncOrchestrator) and then calling the Synchronize method to synchronize the three pairs of peer databases.

//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();

try
{
    //Initial synchronization. Instantiate the SyncOrchestrator
    //and call Synchronize. Note that data is not synchronized during the
    //session between peer 1 and peer 3, because all rows have already
    //been delivered to peer 3 during its synchronization session with peer 2.     
    SyncOrchestrator sampleSyncAgent;
    SyncOperationStatistics syncStatistics;

    sampleSyncAgent = new SampleSyncAgent(util.Peer1ConnString, util.Peer2ConnString);
    syncStatistics = sampleSyncAgent.Synchronize();
    sampleStats.DisplayStats(syncStatistics, "initial");

    sampleSyncAgent = new SampleSyncAgent(util.Peer2ConnString, util.Peer3ConnString);
    syncStatistics = sampleSyncAgent.Synchronize();
    sampleStats.DisplayStats(syncStatistics, "initial");

    sampleSyncAgent = new SampleSyncAgent(util.Peer1ConnString, util.Peer3ConnString);
    syncStatistics = sampleSyncAgent.Synchronize();
    sampleStats.DisplayStats(syncStatistics, "initial");
}


catch (DbOutdatedSyncException ex)
{
    Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
                      " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()

Try
    'Initial synchronization. Instantiate the SyncOrchestrator
    'and call Synchronize. Note that data is not synchronized during the
    'session between peer 1 and peer 3, because all rows have already
    'been delivered to peer 3 during its synchronization session with peer 2.              
    Dim sampleSyncAgent As SyncOrchestrator
    Dim syncStatistics As SyncOperationStatistics

    sampleSyncAgent = New SampleSyncAgent(util.Peer1ConnString, util.Peer2ConnString)
    syncStatistics = sampleSyncAgent.Synchronize()
    sampleStats.DisplayStats(syncStatistics, "initial")

    sampleSyncAgent = New SampleSyncAgent(util.Peer2ConnString, util.Peer3ConnString)
    syncStatistics = sampleSyncAgent.Synchronize()
    sampleStats.DisplayStats(syncStatistics, "initial")

    sampleSyncAgent = New SampleSyncAgent(util.Peer1ConnString, util.Peer3ConnString)
    syncStatistics = sampleSyncAgent.Synchronize()
    sampleStats.DisplayStats(syncStatistics, "initial")


Catch ex As DbOutdatedSyncException
    Console.WriteLine("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() _
                    & " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString())
Catch ex As Exception
    Console.WriteLine(ex.Message)
End Try

Complete Code Example

The following complete code example includes the code examples that are described earlier, the code that is required for the DbSyncAdapter and DbSyncProvider objects, and additional code to display synchronization statistics.

using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;

namespace Microsoft.Samples.Synchronization
{
    class Program
    {
        static void Main(string[] args)
        {

            //The Utility class handles all functionality that is not
            //directly related to synchronization, such as holding peerConnection 
            //string information and making changes to the server database.
            Utility util = new Utility();

            //The SampleStats class handles information from the SyncStatistics
            //object that the Synchronize method returns.
            SampleStats sampleStats = new SampleStats();

            try
            {
                //Initial synchronization. Instantiate the SyncOrchestrator
                //and call Synchronize. Note that data is not synchronized during the
                //session between peer 1 and peer 3, because all rows have already
                //been delivered to peer 3 during its synchronization session with peer 2.     
                SyncOrchestrator sampleSyncAgent;
                SyncOperationStatistics syncStatistics;

                sampleSyncAgent = new SampleSyncAgent(util.Peer1ConnString, util.Peer2ConnString);
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "initial");

                sampleSyncAgent = new SampleSyncAgent(util.Peer2ConnString, util.Peer3ConnString);
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "initial");

                sampleSyncAgent = new SampleSyncAgent(util.Peer1ConnString, util.Peer3ConnString);
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "initial");
            }


            catch (DbOutdatedSyncException ex)
            {
                Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
                                  " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            //Make changes in each peer database.
            util.MakeDataChangesOnPeer(util.Peer1ConnString, "Customer");
            util.MakeDataChangesOnPeer(util.Peer2ConnString, "Customer");
            util.MakeDataChangesOnPeer(util.Peer3ConnString, "Customer");

            try
            {
                //Subsequent synchronization. Changes are now synchronized between all
                //peers. 
                //reports two changes at each peer because of the changes made to
                //the change tracking tables.
                SyncOrchestrator sampleSyncAgent;
                SyncOperationStatistics syncStatistics;

                sampleSyncAgent = new SampleSyncAgent(util.Peer1ConnString, util.Peer2ConnString);
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "subsequent");

                sampleSyncAgent = new SampleSyncAgent(util.Peer2ConnString, util.Peer3ConnString);
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "subsequent");

                sampleSyncAgent = new SampleSyncAgent(util.Peer1ConnString, util.Peer3ConnString);
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "subsequent");
            }


            catch (DbOutdatedSyncException ex)
            {
                Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
                                  " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            //Return peer data back to its original state.
            util.CleanUpPeer(util.Peer1ConnString);
            util.CleanUpPeer(util.Peer2ConnString);
            util.CleanUpPeer(util.Peer3ConnString);

            //Exit.
            Console.Write("\nPress Enter to close the window.");
            Console.ReadLine();
        }

        //Create a class that is derived from 
        //Microsoft.Synchronization.SyncOrchestrator.
        public class SampleSyncAgent : SyncOrchestrator
        {
            public SampleSyncAgent(string localProviderConnString, string remoteProviderConnString)
            {

                //Instantiate the sample provider that allows us to create a provider
                //for both of the peers that are being synchronized.
                SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();

                //Instantiate a DbSyncProvider for the local peer and the remote peer.
                //For example, if this code is running at peer1 and is
                //synchronizing with peer2, peer1 would be the local provider
                //and peer2 the remote provider.
                DbSyncProvider localProvider = new DbSyncProvider();
                DbSyncProvider remoteProvider = new DbSyncProvider();

                //Create a provider by using the SetupSyncProvider on the sample class.             
                sampleSyncProvider.SetupSyncProvider(localProviderConnString, localProvider);
                localProvider.SyncProviderPosition = SyncProviderPosition.Local;
                
                sampleSyncProvider.SetupSyncProvider(remoteProviderConnString, remoteProvider);
                remoteProvider.SyncProviderPosition = SyncProviderPosition.Remote;

                //Specify the local and remote providers that should be synchronized,
                //and the direction and order of changes. In this case, changes are first
                //uploaded from remote to local and then downloaded in the other direction.
                this.LocalProvider = localProvider;
                this.RemoteProvider = remoteProvider;
                this.Direction = SyncDirectionOrder.UploadAndDownload;
            }

            //
            public class SampleSyncProvider
            {
                public DbSyncProvider SetupSyncProvider(string peerConnString, DbSyncProvider peerProvider)
                {

                    //Set the amount of time to retain metadata.
                    const int MetadataAgingInHours = 100;

                    SqlConnection peerConnection = new SqlConnection(peerConnString);
                    peerProvider.Connection = peerConnection;
                    peerProvider.ScopeName = "Sales";

                    //Create a DbSyncAdapter object for the Customer table and associate it 
                    //with the DbSyncProvider. Following the DataAdapter style in ADO.NET, 
                    //DbSyncAdapter is the equivalent for synchronization. The commands that 
                    //are specified for the DbSyncAdapter object call stored procedures
                    //that are created in each peer database.
                    DbSyncAdapter adapterCustomer = new DbSyncAdapter("Customer");


                    //Specify the primary key, which Sync Services uses
                    //to identify each row during synchronization.
                    adapterCustomer.RowIdColumns.Add("CustomerId");


                    //Specify the command to select incremental changes.
                    //In this command and other commands, session variables are
                    //used to pass information at runtime. DbSyncSession.SyncMetadataOnly 
                    //and SyncMinTimestamp are two of the string constants that
                    //the DbSyncSession class exposes. You could also include 
                    //@sync_metadata_only and @sync_min_timestamp directly in your 
                    //queries:
                    //*  sync_metadata_only is used by Sync Services as an optimization
                    //   in some queries.
                    //* The value of the sync_min_timestamp session variable is compared to
                    //   values in the sync_row_timestamp column in the tracking table to 
                    //   determine which rows to select.
                    SqlCommand chgsCustomerCmd = new SqlCommand();
                    chgsCustomerCmd.CommandType = CommandType.StoredProcedure;
                    chgsCustomerCmd.CommandText = "Sales.sp_Customer_SelectChanges";
                    chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
                    chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
                    chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int);            

                    adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd;

                    //Specify the command to insert rows.
                    //The sync_row_count session variable is used in this command 
                    //and other commands to return a count of the rows affected by an operation. 
                    //A count of 0 indicates that an operation failed.
                    SqlCommand insCustomerCmd = new SqlCommand();
                    insCustomerCmd.CommandType = CommandType.StoredProcedure;
                    insCustomerCmd.CommandText = "Sales.sp_Customer_ApplyInsert";
                    insCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
                    insCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
                    insCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
                    insCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
                    insCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

                    adapterCustomer.InsertCommand = insCustomerCmd;


                    //Specify the command to update rows.
                    //The value of the sync_min_timestamp session variable is compared to
                    //values in the sync_row_timestamp column in the tracking table to 
                    //determine which rows to update.
                    SqlCommand updCustomerCmd = new SqlCommand();
                    updCustomerCmd.CommandType = CommandType.StoredProcedure;
                    updCustomerCmd.CommandText = "Sales.sp_Customer_ApplyUpdate";
                    updCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
                    updCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
                    updCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
                    updCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
                    updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
                    updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                    updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
                    
                    adapterCustomer.UpdateCommand = updCustomerCmd;


                    //Specify the command to delete rows.
                    //The value of the sync_min_timestamp session variable is compared to
                    //values in the sync_row_timestamp column in the tracking table to 
                    //determine which rows to delete.
                    SqlCommand delCustomerCmd = new SqlCommand();
                    delCustomerCmd.CommandType = CommandType.StoredProcedure;
                    delCustomerCmd.CommandText = "Sales.sp_Customer_ApplyDelete";
                    delCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
                    delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
                    delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

                    adapterCustomer.DeleteCommand = delCustomerCmd;

                    //Specify the command to select any conflicting rows.
                    SqlCommand selRowCustomerCmd = new SqlCommand();
                    selRowCustomerCmd.CommandType = CommandType.StoredProcedure;
                    selRowCustomerCmd.CommandText = "Sales.sp_Customer_SelectRow";
                    selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);

                    adapterCustomer.SelectRowCommand = selRowCustomerCmd;


                    //Specify the command to insert metadata rows.
                    //The session variables in this command relate to columns in
                    //the tracking table.
                    SqlCommand insMetadataCustomerCmd = new SqlCommand();
                    insMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
                    insMetadataCustomerCmd.CommandText = "Sales.sp_Customer_InsertMetadata";
                    insMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
                    insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
                    insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
                    insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
                    insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
                    insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
                    insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

                    adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd;


                    //Specify the command to update metadata rows.
                    SqlCommand updMetadataCustomerCmd = new SqlCommand();
                    updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
                    updMetadataCustomerCmd.CommandText = "Sales.sp_Customer_UpdateMetadata";
                    updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
                    updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
                    updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
                    updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
                    updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
                    updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
                    updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
                    updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

                    adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd;

                    //Specify the command to delete metadata rows.
                    SqlCommand delMetadataCustomerCmd = new SqlCommand();
                    delMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
                    delMetadataCustomerCmd.CommandText = "Sales.sp_Customer_DeleteMetadata";
                    delMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
                    delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
                    delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
                    delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

                    adapterCustomer.DeleteMetadataCommand = delMetadataCustomerCmd;


                    //Specify the command to select metadata rows for cleanup.
                    SqlCommand selMetadataCustomerCmd = new SqlCommand();
                    selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
                    selMetadataCustomerCmd.CommandText = "Sales.sp_Customer_SelectMetadata";
                    selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_hours", SqlDbType.Int).Value = MetadataAgingInHours;

                    adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd;

                    peerProvider.SyncAdapters.Add(adapterCustomer);

                    // Configure commands that relate to the provider itself rather 
                    // than the DbSyncAdapter object for each table:
                    // * SelectNewTimestampCommand: Returns the new high watermark for 
                    //   the current synchronization session.
                    // * SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge, 
                    //   and a scope version (timestamp).
                    // * UpdateScopeInfoCommand: Sets new values for sync knowledge and cleanup knowledge.            

                    //Select a new timestamp.
                    //During each synchronization, the new value and
                    //the last value from the previous synchronization
                    //are used: the set of changes between these upper and
                    //lower bounds is synchronized.
                    SqlCommand selectNewTimestampCommand = new SqlCommand();
                    string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
                    selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1";
                    selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
                    selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;
                    
                    peerProvider.SelectNewTimestampCommand = selectNewTimestampCommand;

                    //Specify the command to select local replica metadata. 
                    //Set session variables with values from the Sales.ScopeInfo
                    //metadata table.
                    SqlCommand selReplicaInfoCmd = new SqlCommand();
                    selReplicaInfoCmd.CommandType = CommandType.Text;
                    selReplicaInfoCmd.CommandText = "SELECT " +
                                                    "@" + DbSyncSession.SyncScopeId + " = scope_id, " +
                                                    "@" + DbSyncSession.SyncScopeKnowledge + " = scope_sync_knowledge, " +
                                                    "@" + DbSyncSession.SyncScopeCleanupKnowledge + " = scope_tombstone_cleanup_knowledge, " +
                                                    "@" + DbSyncSession.SyncScopeTimestamp + " = scope_timestamp " +
                                                    "FROM Sales.ScopeInfo " +
                                                    "WHERE scope_name = @" + DbSyncSession.SyncScopeName;
                    selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
                    selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;
                    selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output;
                    selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output;
                    selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt).Direction = ParameterDirection.Output;
                    
                    peerProvider.SelectScopeInfoCommand = selReplicaInfoCmd;


                    //Specify the command to select local replica metadata. 
                    //Update the Sales.ScopeInfo metadata table with values
                    //from session variables.
                    SqlCommand updReplicaInfoCmd = new SqlCommand();
                    updReplicaInfoCmd.CommandType = CommandType.Text;
                    updReplicaInfoCmd.CommandText = "UPDATE  Sales.ScopeInfo SET " +
                                                    "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
                                                    "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " +
                                                    "WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " +
                                                    " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 or scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " +
                                                    "SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
                    updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000);
                    updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000);
                    updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
                    updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
                    updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt);
                    updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                    
                    peerProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;

                    return peerProvider;
                }
            }
        }

        //Handle the statistics that are returned by the SyncAgent.
        public class SampleStats
        {
            public void DisplayStats(SyncOperationStatistics syncStatistics, string syncType)
            {
                Console.WriteLine(String.Empty);
                if (syncType == "initial")
                {
                    Console.WriteLine("****** Initial Synchronization ******");
                }
                else if (syncType == "subsequent")
                {
                    Console.WriteLine("***** Subsequent Synchronization ****");
                }

                Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
                Console.WriteLine("Total Changes Uploaded: " + syncStatistics.UploadChangesTotal);
                Console.WriteLine("Total Changes Downloaded: " + syncStatistics.DownloadChangesTotal);
                Console.WriteLine("Complete Time: " + syncStatistics.SyncEndTime);
                Console.WriteLine(String.Empty);
            }
        }
    }
}

Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data

Class Program

    Shared Sub Main(ByVal args() As String)

        'The Utility class handles all functionality that is not
        'directly related to synchronization, such as holding peerConnection 
        'string information and making changes to the server database.
        Dim util As New Utility()

        'The SampleStats class handles information from the SyncStatistics
        'object that the Synchronize method returns.
        Dim sampleStats As New SampleStats()

        Try
            'Initial synchronization. Instantiate the SyncOrchestrator
            'and call Synchronize. Note that data is not synchronized during the
            'session between peer 1 and peer 3, because all rows have already
            'been delivered to peer 3 during its synchronization session with peer 2.              
            Dim sampleSyncAgent As SyncOrchestrator
            Dim syncStatistics As SyncOperationStatistics

            sampleSyncAgent = New SampleSyncAgent(util.Peer1ConnString, util.Peer2ConnString)
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "initial")

            sampleSyncAgent = New SampleSyncAgent(util.Peer2ConnString, util.Peer3ConnString)
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "initial")

            sampleSyncAgent = New SampleSyncAgent(util.Peer1ConnString, util.Peer3ConnString)
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "initial")


        Catch ex As DbOutdatedSyncException
            Console.WriteLine("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() _
                            & " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString())
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
        'Make changes in each peer database.
        util.MakeDataChangesOnPeer(util.Peer1ConnString, "Customer")
        util.MakeDataChangesOnPeer(util.Peer2ConnString, "Customer")
        util.MakeDataChangesOnPeer(util.Peer3ConnString, "Customer")

        Try
            'Subsequent synchronization. Changes are now synchronized between all
            'peers. 
            'reports two changes at each peer because of the changes made to
            'the change tracking tables.
            Dim sampleSyncAgent As SyncOrchestrator
            Dim syncStatistics As SyncOperationStatistics

            sampleSyncAgent = New SampleSyncAgent(util.Peer1ConnString, util.Peer2ConnString)
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "subsequent")

            sampleSyncAgent = New SampleSyncAgent(util.Peer2ConnString, util.Peer3ConnString)
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "subsequent")

            sampleSyncAgent = New SampleSyncAgent(util.Peer1ConnString, util.Peer3ConnString)
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "subsequent")


        Catch ex As DbOutdatedSyncException
            Console.WriteLine("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() _
                            & " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString())
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        'Return peer data back to its original state.
        util.CleanUpPeer(util.Peer1ConnString)
        util.CleanUpPeer(util.Peer2ConnString)
        util.CleanUpPeer(util.Peer3ConnString)

        'Exit.
        Console.Write(vbLf + "Press Enter to close the window.")
        Console.ReadLine()

    End Sub 'Main

End Class 'Program

'Create a class that is derived from 
'Microsoft.Synchronization.SyncOrchestrator.
Public Class SampleSyncAgent
    Inherits SyncOrchestrator

    Public Sub New(ByVal localProviderConnString As String, ByVal remoteProviderConnString As String)

        'Instantiate the sample provider that allows us to create a provider
        'for both of the peers that are being synchronized.
        Dim sampleSyncProvider As New SampleSyncProvider()

        'Instantiate a DbSyncProvider for the local peer and the remote peer.
        'For example, if this code is running at peer1 and is
        'synchronizing with peer2, peer1 would be the local provider
        'and peer2 the remote provider.  
        Dim localProvider As New DbSyncProvider()
        Dim remoteProvider As New DbSyncProvider()

        'Create a provider by using the SetupSyncProvider on the sample class.
        sampleSyncProvider.SetupSyncProvider(localProviderConnString, localProvider)
        localProvider.SyncProviderPosition = SyncProviderPosition.Local

        sampleSyncProvider.SetupSyncProvider(remoteProviderConnString, remoteProvider)
        remoteProvider.SyncProviderPosition = SyncProviderPosition.Remote

        'Specify the local and remote providers that should be synchronized,
        'and the direction and order of changes. In this case, changes are first
        'uploaded from local to remote and then downloaded in the other direction.
        Me.LocalProvider = localProvider
        Me.RemoteProvider = remoteProvider
        Me.Direction = SyncDirectionOrder.UploadAndDownload

    End Sub 'New
End Class 'SampleSyncAgent

Public Class SampleSyncProvider

    Public Function SetupSyncProvider(ByVal peerConnString As String, ByVal peerProvider As DbSyncProvider) As DbSyncProvider

        'Set the amount of time to retain metadata.
        Const MetadataAgingInHours As Integer = 100

        Dim peerConnection As New SqlConnection(peerConnString)
        peerProvider.Connection = peerConnection
        peerProvider.ScopeName = "Sales"

        'Create a DbSyncAdapter object for the Customer table and associate it 
        'with the DbSyncProvider. Following the DataAdapter style in ADO.NET, 
        'DbSyncAdapter is the equivalent for synchronization. The commands that 
        'are specified for the DbSyncAdapter object call stored procedures
        ' that are created in each peer database.
        Dim adapterCustomer As New DbSyncAdapter("Customer")

        'Specify the primary key, which Sync Services uses
        'to identify each row during synchronization.
        adapterCustomer.RowIdColumns.Add("CustomerId")

        'Specify the command to select incremental changes.
        'In this command and other commands, session variables are
        'used to pass information at runtime. DbSyncSession.SyncMetadataOnly 
        'and SyncMinTimestamp are two of the string constants that
        'the DbSyncSession class exposes. You could also include 
        '@sync_metadata_only and @sync_min_timestamp directly in your 
        'queries:
        '*  sync_metadata_only is used by Sync Services as an optimization
        '   in some queries.
        '* The value of the sync_min_timestamp session variable is compared to
        '   values in the sync_row_timestamp column in the tracking table to 
        '   determine which rows to select.
        Dim chgsCustomerCmd As New SqlCommand()

        With chgsCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sales.sp_Customer_SelectChanges"
            .Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int)
        End With

        adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd

        'Specify the command to insert rows.
        'The sync_row_count session variable is used in this command 
        'and other commands to return a count of the rows affected by an operation. 
        'A count of 0 indicates that an operation failed.
        Dim insCustomerCmd As New SqlCommand()

        With insCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sales.sp_Customer_ApplyInsert"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@CustomerName", SqlDbType.NVarChar)
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@CustomerType", SqlDbType.NVarChar)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
        End With

        adapterCustomer.InsertCommand = insCustomerCmd


        'Specify the command to update rows.
        'The value of the sync_min_timestamp session variable is compared to
        'values in the sync_row_timestamp column in the tracking table to 
        'determine which rows to update.
        Dim updCustomerCmd As New SqlCommand()

        With updCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sales.sp_Customer_ApplyUpdate"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@CustomerName", SqlDbType.NVarChar)
            .Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
            .Parameters.Add("@CustomerType", SqlDbType.NVarChar)
            .Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
            .Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int)
        End With

        adapterCustomer.UpdateCommand = updCustomerCmd


        'Specify the command to delete rows.
        'The value of the sync_min_timestamp session variable is compared to
        'values in the sync_row_timestamp column in the tracking table to 
        'determine which rows to delete.
        Dim delCustomerCmd As New SqlCommand()

        With delCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sales.sp_Customer_ApplyDelete"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
        End With

        adapterCustomer.DeleteCommand = delCustomerCmd

        'Specify the command to select any conflicting rows.
        Dim selRowCustomerCmd As New SqlCommand()

        With selRowCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sales.sp_Customer_SelectRow"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
        End With

        adapterCustomer.SelectRowCommand = selRowCustomerCmd


        'Specify the command to insert metadata rows.
        'The session variables in this command relate to columns in
        'the tracking table. These are the same columns
        'that were specified as DbSyncAdapter properties at the beginning 
        'of this code example.
        Dim insMetadataCustomerCmd As New SqlCommand()

        With insMetadataCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sales.sp_Customer_InsertMetadata"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
        End With

        adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd


        'Specify the command to update metadata rows.
        Dim updMetadataCustomerCmd As New SqlCommand()

        With updMetadataCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sales.sp_Customer_UpdateMetadata"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
        End With

        adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd

        'Specify the command to delete metadata rows.
        Dim delMetadataCustomerCmd As New SqlCommand()

        With delMetadataCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sales.sp_Customer_DeleteMetadata"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
        End With

        adapterCustomer.DeleteMetadataCommand = delMetadataCustomerCmd


        'Specify the command to select metadata rows for cleanup.
        Dim selMetadataCustomerCmd As New SqlCommand()

        With selMetadataCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sales.sp_Customer_SelectMetadata"
            .Parameters.Add("@metadata_aging_in_hours", SqlDbType.Int).Value = MetadataAgingInHours
        End With

        adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd

        peerProvider.SyncAdapters.Add(adapterCustomer)

        ' Configure commands that relate to the provider itself rather 
        ' than the DbSyncAdapter object for each table:
        ' * SelectNewTimestampCommand: Returns the new high watermark for 
        '   the current synchronization session.
        ' * SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge, 
        '   and a scope version (timestamp).
        ' * UpdateScopeInfoCommand: Sets new values for sync knowledge and cleanup knowledge.            
        'Select a new timestamp.
        'During each synchronization, the new value and
        'the last value from the previous synchronization
        'are used: the set of changes between these upper and
        'lower bounds is synchronized.
        Dim newTimestampVariable As String = "@" + DbSyncSession.SyncNewTimestamp

        Dim selectNewTimestampCommand As New SqlCommand()

        With selectNewTimestampCommand
            .CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1"
            .Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
            .Parameters(newTimestampVariable).Direction = ParameterDirection.Output
        End With

        peerProvider.SelectNewTimestampCommand = selectNewTimestampCommand

        'Specify the command to select local replica metadata. 
        'Set session variables with values from the Sales.ScopeInfo
        'metadata table.
        Dim selReplicaInfoCmd As New SqlCommand()

        With selReplicaInfoCmd
            .CommandType = CommandType.Text
            .CommandText = "SELECT " _
                         & "@" + DbSyncSession.SyncScopeId + " = scope_id, " _
                         & "@" + DbSyncSession.SyncScopeKnowledge + " = scope_sync_knowledge, " _
                         & "@" + DbSyncSession.SyncScopeCleanupKnowledge + " = scope_tombstone_cleanup_knowledge, " _
                         & "@" + DbSyncSession.SyncScopeTimestamp + " = scope_timestamp " _
                         & "FROM Sales.ScopeInfo " _
                         & "WHERE scope_name = @" + DbSyncSession.SyncScopeName
            .Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
            .Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output
            .Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output
            .Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output
            .Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt).Direction = ParameterDirection.Output
        End With

        peerProvider.SelectScopeInfoCommand = selReplicaInfoCmd


        'Specify the command to select local replica metadata. 
        'Update the Sales.ScopeInfo metadata table with values
        'from session variables.
        Dim updReplicaInfoCmd As New SqlCommand()

        With updReplicaInfoCmd
            .CommandType = CommandType.Text
            .CommandText = "UPDATE  Sales.ScopeInfo SET " _
                         & "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " _
                         & "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " _
                         & "WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " _
                         & " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 or scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " _
                         & "SET @" + DbSyncSession.SyncRowCount + " = @@rowcount"
            .Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000)
            .Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000)
            .Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
            .Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
            .Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
        End With

        peerProvider.UpdateScopeInfoCommand = updReplicaInfoCmd

        Return peerProvider

    End Function 'SetupSyncProvider
End Class 'SampleSyncProvider


'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats

    Public Sub DisplayStats(ByVal syncStatistics As SyncOperationStatistics, ByVal syncType As String)
        Console.WriteLine(String.Empty)
        If syncType = "initial" Then
            Console.WriteLine("****** Initial Synchronization ******")
        ElseIf syncType = "subsequent" Then
            Console.WriteLine("***** Subsequent Synchronization ****")
        End If

        Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
        Console.WriteLine("Total Changes Uploaded: " & syncStatistics.UploadChangesTotal)
        Console.WriteLine("Total Changes Downloaded: " & syncStatistics.DownloadChangesTotal)
        Console.WriteLine("Complete Time: " & syncStatistics.SyncEndTime)
        Console.WriteLine(String.Empty)

    End Sub 'DisplayStats
End Class 'SampleStats

See Also

Concepts

Programming Common Peer-to-Peer Synchronization Tasks