Share via


How to: Use Session Variables for Peer-to-Peer Synchronization

This topic shows how to use session variables in Sync Services for ADO.NET 2.0. The examples in this topic focus on the following Sync Services types:

For information about how to run sample code, see "Example Applications in the How to Topics" in Programming Common Peer-to-Peer Synchronization Tasks.

Understanding Session Variables

Sync Services provides a set of session variables that enable you to pass values to DbSyncAdapter and DbSyncProvider commands during synchronization. These variables are specified like other parameters to queries or stored procedures in ADO.NET commands. For example, the following UPDATE statement applies changes to the Customer table by using the @sync_min_timestamp, @sync_force_write, and @sync_row_count session variables.

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

The values for @sync_min_timestamp and @sync_force_write are set by Sync Services during synchronization, and the value for @sync_row_count is set by the SQL Server @@rowcount function. You can use the variable names directly as in the previous query, or you can use the string constants that are available from the SyncSession object. The following table lists all the available session variables and how they are used.

Session variable Usage

sync_check_concurrency

Used by Sync Services for the commands that update and delete metadata. Sync Services uses this value to determine whether a metadata change can be made if the row to which the metadata refers has changed. If a metadata change cannot be made, the ApplyMetadataFailed event is raised.

sync_create_peer_key, sync_create_peer_timestamp, sync_row_is_tombstone, sync_row_timestamp, sync_update_peer_key, and sync_update_peer_timestamp

Used by the commands that select and apply changes to change tracking columns. For more information about these columns, see "Creating Tracking Tables to Store Metadata" in How to: Configure Change Tracking and Synchronize Peers.

sync_force_write

Used with an ApplyAction of RetryWithForceWrite to force applying a change that failed because of a conflict or an error. For more information, see How to: Handle Data Conflicts and Errors for Peer-to-Peer Synchronization.

sync_initialize

Returns whether the current synchronization session is the initial session (a value of 1), or a subsequent session (a value of 0). This enables you to include logic to initialize the data in each peer database.

sync_metadata_only

Used by Sync Services for the commands that are specified for the SelectIncrementalChangesCommand and SelectRowCommand properties. When Sync Services specifies a value of 1 for this variable, it selects only metadata but not the associated data changes. This is an optimization for cases in which Sync Services does not require the data changes, such as selecting conflicting rows with the SelectRowCommand.

sync_min_timestamp and sync_new_timestamp

Used to define the set of changes to be synchronized during a session. During the current synchronization session, the command specified for the SelectNewTimestampCommand property provides a new timestamp value. Changes that are made after the minimum value and before the new value are synchronized. The new value is then stored and used as the minimum value for the next synchronization session.

sync_row_count

Returns the number of rows that were affected by the last operation at the server. In SQL Server databases, @@ROWCOUNT provides the value for this variable. As shown in the code examples in this topic, stored procedures should include an output parameter that sets the value for sync_row_count.

A row count of 0 indicates that an operation failed, typically because of a conflict or an error. For more information, see How to: Handle Data Conflicts and Errors for Peer-to-Peer Synchronization.

sync_scope_cleanup_knowledge, sync_scope_id, sync_scope_knowledge, sync_scope_name, and sync_scope_timestamp

Used by the commands that select and apply changes to the table that stores synchronization knowledge. For an example of this table, see "Creating Tracking Tables to Store Metadata" in How to: Configure Change Tracking and Synchronize Peers.

sync_session_id

Returns a GUID value that identifies the current synchronization session.

sync_stage_name

Returns a value that corresponds to one of the values in the DbSyncStage enumeration.

Example

The following code examples show how to use session variables when you are synchronizing the Customer table in the Sync Services peer sample database.

Key Parts of the API

This section provides code examples that point out some key parts of the API that involve session variables. Several of the commands that are shown call stored procedures that are included in the sample peer database. For more information, see "Creating Stored Procedures to Update Data and Metadata" in How to: Configure Change Tracking and Synchronize Peers.

The following code example specifies a query for the SelectNewTimestampCommand property. This property sets the value for the sync_new_timestamp variable. This value is used by the synchronization commands that select changes from the server database.

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

The MIN_ACTIVE_ROWVERSION function returns the lowest active timestamp (also known as rowversion) value in the current database. A timestamp value is active when it is used in a transaction that has not yet been committed. If there are no active values in the database, MIN_ACTIVE_ROWVERSION returns the same value as @@DBTS + 1. MIN_ACTIVE_ROWVERSION is useful for scenarios such as data synchronization that use timestamp values to group sets of changes together. If an application uses @@DBTS in its anchor commands instead of MIN_ACTIVE_ROWVERSION, it can miss changes that are active when synchronization occurs.

The following code example specifies a stored procedure for the SelectIncrementalChangesCommand property. This procedure selects inserts, updates, and deletes from a peer to apply to a second peer during a synchronization session. The sync_min_timestamp variable specifies the minimum timestamp that is included in the set of changes to be synchronized. The value of this variable is compared to values in the sync_row_timestamp column in the tracking table to determine which rows to select. The sync_initialize variable is used to specify whether a synchronization session is the first session between two peers. If it is the first session between two peers, the sp_Customer_SelectChanges stored procedure can include logic that only executes during this first session.

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

The following code example specifies a command to apply updates to one peer that are selected from the other peer. The sync_row_count variable enables Sync Services to determine whether an update succeeded or failed. If an update fails, the sync_force_write variable can be set to 1. This enables different logic in the sp_Customer_ApplyUpdate stored procedure. For more information, see How to: Handle Data Conflicts and Errors for Peer-to-Peer Synchronization. Before an update is applied at a peer, the sync_min_timestamp variable is used to check whether a row has been updated at the peer since the previous synchronization session.

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

The following code examples specify the commands that apply updates to the Customer_Tracking table and the ScopeInfo table. Most of the session variables correspond to columns in the tracking tables. The sync_check_concurrency variable is used in the commands to determine whether a metadata changes can be made when the rows to which the metadata refers have changed.

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

Complete Code Example

The following complete code example includes the code examples that are described earlier in this topic, and additional code to perform synchronization. The example requires the Utility class that is available in Utility Class for Sync Services How-to Topics.

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