Getting Started: Client and Server Synchronization

  • Article

This topic describes a console application that downloads an initial dataset and then a set of incremental changes from a single table. The application is straightforward, but it will introduce you to code that is built on, in many ways, throughout the Sync Services for ADO.NET 2.0 documentation. If you have read Architecture and Classes for Client and Server Synchronization, you should have an understanding of the main classes that are used in the application.

You can learn by just reading through the example code. However, it is more instructive to run the application and to see it in action. Before you run the code, make sure that you have the following installed:

  • Sync Services

    The application requires references to Microsoft.Synchronization.Data.dll, Microsoft.Synchronization.Data.Server.dll, and Microsoft.Synchronization.Data.SqlServerCe.dll.

  • SQL Server Compact 3.5 Service Pack 1

    The application requires a reference to System.Data.SqlServerCe.dll.

  • A version of SQL Server other than SQL Server Compact 3.5 to act as the server database.

    The example code uses localhost in connection strings. To use the instance of SQL Server Express that installs with Visual Studio, change localhost to .\sqlexpress. To use a remote server, change localhost to the appropriate server name.

  • The Sync Services sample databases. Execute both scripts that are available in Setup Scripts for Sync Services How-to Topics. We recommend to that you review these scripts to see how change tracking is handled in the server database.

The application is composed of six classes:

  • SampleSyncAgent. This class is derived from SyncAgent and contains a SyncTable.

  • SampleServerSyncProvider. This class is derived from DbServerSyncProvider and contains the SyncAdapter.

  • SampleClientSyncProvider. This class is derived from SqlCeClientSyncProvider. In this example, this class contains only a connection string to the client database.

  • SampleStats. This class uses the statistics that are returned by the SyncAgent.

  • Program. This class sets up synchronization and calls methods from the Utility class.

  • Utility. This class handles all functionality that is not directly related to synchronization, such as holding connection string information and making changes to the server database. A complete Utility class is used in other topics. The complete class is available from Utility Class for Sync Services How-to Topics.

Key Parts of the API

Before you look at the complete code example, we recommend that you review the following examples that illustrate several key sections of the API that are used in this application.

Creating a SyncTable

The following code example creates a SyncTable object for the Customer table, specifies the synchronization direction, and specifies how the table should be created on the client. In this case, if the table already exists in the client database, the table will be dropped during the first synchronization.

SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerSyncTable);

Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerSyncTable)

Using the SqlSyncAdapterBuilder

Each of the code examples in this section creates a SyncAdapter for the Customer table. The synchronization adapter provides to the server synchronization provider the specific commands that are required to interact with the server database. In this application, the synchronization adapter is created by using the SqlSyncAdapterBuilder. The first example shows how to use the SqlSyncAdapterBuilder with a custom change tracking system. The second example shows how to use the SqlSyncAdapterBuilder with SQL Server change tracking (available in SQL Server 2008). For more information about change tracking, see Tracking Changes in the Server Database.

For information about how to create commands manually instead of using the builder, see How to: Download Incremental Data Changes to a Client.

Using a Custom Change Tracking System

To use a custom change tracking system, specify the following information for the SqlSyncAdapterBuilder and SyncAdapter:

  • The name of the table to synchronize and the tombstone table. A tombstone table is used to track delete operations in the server database. For more information, see Tracking Changes in the Server Database. If the tables are in a schema other than dbo, the schema must be specified.

  • The direction of synchronization. This controls which commands the SqlSyncAdapterBuilder creates. For more information about commands, see How to: Specify Snapshot, Download, Upload, and Bidirectional Synchronization.

  • The tracking columns in the server database. The columns are used to track when changes are made, so that only new changes are downloaded. You can include additional columns to track where changes are made. For more information, see How to: Use a Custom Change Tracking System.

  • The name of the SyncAdapter. This must match the name of the SyncTable. Therefore, it should not include the schema name.

SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);

customerBuilder.TableName = "Sales.Customer";
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
customerBuilder.CreationTrackingColumn = "InsertTimestamp";
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";

SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);

Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)

customerBuilder.TableName = "Sales.Customer"
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone"
customerBuilder.SyncDirection = SyncDirection.DownloadOnly
customerBuilder.CreationTrackingColumn = "InsertTimestamp"
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp"
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp"

Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)

Using SQL Server Change Tracking

To use SQL Server change tracking, specify the following information for the SqlSyncAdapterBuilder and SyncAdapter:

  • The name of the table to synchronize.

  • The direction of synchronization. This controls which commands the SqlSyncAdapterBuilder creates. For more information about commands, see How to: Specify Snapshot, Download, Upload, and Bidirectional Synchronization.

  • The type of change tracking to use. By default, Sync Services expects you to specify custom change tracking columns. In this code example, SQL Server change tracking is specified.

  • The name of the SyncAdapter. This must match the name of the SyncTable. Therefore, it should not include the schema name.

SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);

customerBuilder.TableName = "Sales.Customer";
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;

SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);

Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)

customerBuilder.TableName = "Sales.Customer"
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking

Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)

Specifying the New Anchor Command

The following code example specifies a command to retrieve a new anchor value from the server. The value is stored in the client database and is used by the commands that synchronize changes. During each synchronization, the new anchor value and the last anchor value from the previous synchronization are used: the set of changes between these upper and lower bounds is synchronized.

In this case, MIN_ACTIVE_ROWVERSION returns a timestamp value from a SQL Server database. (MIN_ACTIVE_ROWVERSION was introduced in SQL Server 2005 Service Pack 2.) A timestamp value is used because the tracking columns that are specified for the SqlSyncAdapterBuilder contain timestamp values. If the tracking columns contained date values, you could use a function such as GETUTCDATE() instead of MIN_ACTIVE_ROWVERSION. For more information about anchors, see Tracking Changes in the Server Database.

The SyncSession class contains several string constants that can be used in synchronization commands. SyncNewReceivedAnchor is one of these constants. You could also use the literal @sync_new_received_anchor directly in your queries.

SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;

Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
selectNewAnchorCommand.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
selectNewAnchorCommand.Connection = serverConn
Me.SelectNewAnchorCommand = selectNewAnchorCommand

Calling the Synchronize Method

The following code example instantiates SampleSyncAgent and calls the Synchronize method. In the SampleSyncAgent class, the SampleClientSyncProvider is specified as the LocalProvider and the SampleServerSyncProvider is specified as the RemoteProvider, and also the synchronization table that has already been described.

SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();

Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()

In the SampleStats class, statistics that are returned by the SyncAgent are used to provide feedback to the user about the synchronization session. For more information, see How to: Work with Events and Program Business Logic.

Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);

Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)

Complete Code Examples

Now that you have seen the main sections of code that are involved in synchronization, these sections are combined in a complete application that is thoroughly commented. After you run this application, we recommend that you read the topics in the section Programming Common Client and Server Synchronization Tasks. You will see the same classes that are used in the code examples in this topic. However, they are applied across additional tables in more sophisticated ways.

Complete Example Using Custom Change Tracking

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

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 connection 
            //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();

            //Delete and re-create the database. The client synchronization
            //provider also enables you to create the client database 
            //if it does not exist.
            util.SetClientPassword();
            util.RecreateClientDatabase();

            //Initial synchronization. Instantiate the SyncAgent
            //and call Synchronize.
            SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
            SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
            sampleStats.DisplayStats(syncStatistics, "initial");

            //Make changes on the server.
            util.MakeDataChangesOnServer();

            //Subsequent synchronization.
            syncStatistics = sampleSyncAgent.Synchronize();
            sampleStats.DisplayStats(syncStatistics, "subsequent");

            //Return server data back to its original state.
            util.CleanUpServer();

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

    //Create a class that is derived from 
    //Microsoft.Synchronization.SyncAgent.
    public class SampleSyncAgent : SyncAgent
    {
        public SampleSyncAgent()
        {
            //Instantiate a client synchronization provider and specify it
            //as the local provider for this synchronization agent.
            this.LocalProvider = new SampleClientSyncProvider();

            //Instantiate a server synchronization provider and specify it
            //as the remote provider for this synchronization agent.
            this.RemoteProvider = new SampleServerSyncProvider();

            //Add the Customer table: specify a synchronization direction of
            //DownloadOnly.
            SyncTable customerSyncTable = new SyncTable("Customer");
            customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
            this.Configuration.SyncTables.Add(customerSyncTable);
        }
    }

    //Create a class that is derived from 
    //Microsoft.Synchronization.Server.DbServerSyncProvider.
    public class SampleServerSyncProvider : DbServerSyncProvider
    {
        public SampleServerSyncProvider()
        {
            //Create a connection to the sample server database.
            Utility util = new Utility();
            SqlConnection serverConn = new SqlConnection(util.ServerConnString);
            this.Connection = serverConn;

            //Create a command to retrieve a new anchor value from
            //the server. In this case, we use a timestamp value
            //that is retrieved and stored in the client database.
            //During each synchronization, the new anchor value and
            //the last anchor value from the previous synchronization
            //are used: the set of changes between these upper and
            //lower bounds is synchronized.
            //
            //SyncSession.SyncNewReceivedAnchor is a string constant; 
            //you could also use @sync_new_received_anchor directly in 
            //your queries.
            SqlCommand selectNewAnchorCommand = new SqlCommand();
            string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
            selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
            selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
            selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
            selectNewAnchorCommand.Connection = serverConn;
            this.SelectNewAnchorCommand = selectNewAnchorCommand;


            //Create a SyncAdapter for the Customer table by using 
            //the SqlSyncAdapterBuilder:
            //  * Specify the base table and tombstone table names.
            //  * Specify the columns that are used to track when
            //    changes are made.
            //  * Specify download-only synchronization.
            //  * Call ToSyncAdapter to create the SyncAdapter.
            //  * Specify a name for the SyncAdapter that matches the
            //    the name specified for the corresponding SyncTable.
            //    Do not include the schema names (Sales in this case).

            SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);

            customerBuilder.TableName = "Sales.Customer";
            customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
            customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
            customerBuilder.CreationTrackingColumn = "InsertTimestamp";
            customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
            customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
            
            SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
            customerSyncAdapter.TableName = "Customer";
            this.SyncAdapters.Add(customerSyncAdapter);

        }
    }

    //Create a class that is derived from 
    //Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
    //You can just instantiate the provider directly and associate it
    //with the SyncAgent, but you could use this class to handle client 
    //provider events and other client-side processing.
    public class SampleClientSyncProvider : SqlCeClientSyncProvider
    {

        public SampleClientSyncProvider()
        {
            //Specify a connection string for the sample client database.
            Utility util = new Utility();
            this.ConnectionString = util.ClientConnString;
        }
    }

    //Handle the statistics that are returned by the SyncAgent.
    public class SampleStats
    {
        public void DisplayStats(SyncStatistics 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 Downloaded: " + syncStatistics.TotalChangesDownloaded);
            Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
            Console.WriteLine(String.Empty);

        }
    }

    public class Utility
    {

        private static string _clientPassword;

        //Get and set the client database password.
        public static string Password
        {
            get { return _clientPassword; }
            set { _clientPassword = value; }
        }

        //Have the user enter a password for the client database file.
        public void SetClientPassword()
        {
            Console.WriteLine("Type a strong password for the client");
            Console.WriteLine("database, and then press Enter.");
            Utility.Password = Console.ReadLine();
        }

        //Return the client connection string with the password.
        public string ClientConnString
        {
            get { return @"Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password; }
        }

        //Return the server connection string. 
        public string ServerConnString
        {

            get { return @"Data Source=localhost; Initial Catalog=SyncSamplesDb; Integrated Security=True"; }

        }

        //Make server changes that are synchronized on the second 
        //synchronization.
        public void MakeDataChangesOnServer()
        {
            int rowCount = 0;

            using (SqlConnection serverConn = new SqlConnection(this.ServerConnString))
            {
                SqlCommand sqlCommand = serverConn.CreateCommand();
                sqlCommand.CommandText =
                    "INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " +
                    "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " +
                    
                    "UPDATE Sales.Customer " +
                    "SET  SalesPerson = 'James Bailey' " +
                    "WHERE CustomerName = 'Tandem Bicycle Store' " +

                    "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'"; 
                
                serverConn.Open();
                rowCount = sqlCommand.ExecuteNonQuery();
                serverConn.Close();
            }

            Console.WriteLine("Rows inserted, updated, or deleted at the server: " + rowCount);
        }

        //Revert changes that were made during synchronization.
        public void CleanUpServer()
        {          
            using(SqlConnection serverConn = new SqlConnection(this.ServerConnString))
            {
                SqlCommand sqlCommand = serverConn.CreateCommand();
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "usp_InsertSampleData";
                
                serverConn.Open();               
                sqlCommand.ExecuteNonQuery();
                serverConn.Close();
            }
        }

        //Delete the client database.
        public void RecreateClientDatabase()
        {
            using (SqlCeConnection clientConn = new SqlCeConnection(this.ClientConnString))
            {
                if (File.Exists(clientConn.Database))
                {
                    File.Delete(clientConn.Database);
                }
            }
                
            SqlCeEngine sqlCeEngine = new SqlCeEngine(this.ClientConnString);
            sqlCeEngine.CreateDatabase();
        }
    }
}

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

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 connection 
        '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()

        'Delete and re-create the database. The client synchronization
        'provider also enables you to create the client database 
        'if it does not exist.
        util.SetClientPassword()
        util.RecreateClientDatabase()

        'Initial synchronization. Instantiate the SyncAgent
        'and call Synchronize.
        Dim sampleSyncAgent As New SampleSyncAgent()
        Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
        sampleStats.DisplayStats(syncStatistics, "initial")

        'Make changes on the server.
        util.MakeDataChangesOnServer()

        'Subsequent synchronization.
        syncStatistics = sampleSyncAgent.Synchronize()
        sampleStats.DisplayStats(syncStatistics, "subsequent")

        'Return server data back to its original state.
        util.CleanUpServer()

        '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.SyncAgent.
Public Class SampleSyncAgent
    Inherits SyncAgent

    Public Sub New()
        'Instantiate a client synchronization provider and specify it
        'as the local provider for this synchronization agent.
        Me.LocalProvider = New SampleClientSyncProvider()

        'Instantiate a server synchronization provider and specify it
        'as the remote provider for this synchronization agent.
        Me.RemoteProvider = New SampleServerSyncProvider()

        'Add the Customer table: specify a synchronization direction of
        'DownloadOnly.
        Dim customerSyncTable As New SyncTable("Customer")
        customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
        customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
        Me.Configuration.SyncTables.Add(customerSyncTable)

    End Sub 'New 
End Class 'SampleSyncAgent

'Create a class that is derived from 
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
    Inherits DbServerSyncProvider

    Public Sub New()
        'Create a connection to the sample server database.
        Dim util As New Utility()
        Dim serverConn As New SqlConnection(util.ServerConnString)
        Me.Connection = serverConn

        'Create a command to retrieve a new anchor value from
        'the server. In this case, we use a timestamp value
        'that is retrieved and stored in the client database.
        'During each synchronization, the new anchor value and
        'the last anchor value from the previous synchronization
        'are used: the set of changes between these upper and
        'lower bounds is synchronized.
        '
        'SyncSession.SyncNewReceivedAnchor is a string constant; 
        'you could also use @sync_new_received_anchor directly in 
        'your queries.
        Dim selectNewAnchorCommand As New SqlCommand()
        Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
        selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
        selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
        selectNewAnchorCommand.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
        selectNewAnchorCommand.Connection = serverConn
        Me.SelectNewAnchorCommand = selectNewAnchorCommand

        'Create a SyncAdapter for the Customer table by using 
        'the SqlSyncAdapterBuilder:
        '  * Specify the base table and tombstone table names.
        '  * Specify the columns that are used to track when
        '    changes are made.
        '  * Specify download-only synchronization.
        '  * Call ToSyncAdapter to create the SyncAdapter.
        '  * Specify a name for the SyncAdapter that matches the
        '    the name specified for the corresponding SyncTable.
        '    Do not include the schema names (Sales in this case).
        Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)

        customerBuilder.TableName = "Sales.Customer"
        customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone"
        customerBuilder.SyncDirection = SyncDirection.DownloadOnly
        customerBuilder.CreationTrackingColumn = "InsertTimestamp"
        customerBuilder.UpdateTrackingColumn = "UpdateTimestamp"
        customerBuilder.DeletionTrackingColumn = "DeleteTimestamp"

        Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
        customerSyncAdapter.TableName = "Customer"
        Me.SyncAdapters.Add(customerSyncAdapter)

    End Sub 'New
End Class 'SampleServerSyncProvider

'Create a class that is derived from 
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
'You can just instantiate the provider directly and associate it
'with the SyncAgent, but you could use this class to handle client 
'provider events and other client-side processing.
Public Class SampleClientSyncProvider
    Inherits SqlCeClientSyncProvider


    Public Sub New()
        'Specify a connection string for the sample client database.
        Dim util As New Utility()
        Me.ConnectionString = util.ClientConnString

    End Sub 'New
End Class 'SampleClientSyncProvider

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

    Public Sub DisplayStats(ByVal syncStatistics As SyncStatistics, 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 Downloaded: " & syncStatistics.TotalChangesDownloaded)
        Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
        Console.WriteLine(String.Empty)

    End Sub 'DisplayStats 
End Class 'SampleStats


Public Class Utility

    Private Shared _clientPassword As String

    'Get and set the client database password.
    Public Shared Property Password() As String
        Get
            Return _clientPassword
        End Get
        Set(ByVal value As String)
            _clientPassword = value
        End Set
    End Property

    'Have the user enter a password for the client database file.
    Public Sub SetClientPassword()
        Console.WriteLine("Type a strong password for the client")
        Console.WriteLine("database, and then press Enter.")
        Utility.Password = Console.ReadLine()

    End Sub 'SetClientPassword

    'Return the client connection string with the password.
    Public ReadOnly Property ClientConnString() As String
        Get
            Return "Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password
        End Get
    End Property

    'Return the server connection string. 
    Public ReadOnly Property ServerConnString() As String

        Get
            Return "Data Source=localhost; Initial Catalog=SyncSamplesDb; Integrated Security=True"
        End Get
    End Property


    'Make server changes that are synchronized on the second 
    'synchronization.
    Public Sub MakeDataChangesOnServer()
        Dim rowCount As Integer = 0

        Dim serverConn As New SqlConnection(Me.ServerConnString)
        Try
            Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
            sqlCommand.CommandText = _
                "INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " _
              & "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " _
              & "UPDATE Sales.Customer " _
              & "SET  SalesPerson = 'James Bailey' " _
              & "WHERE CustomerName = 'Tandem Bicycle Store' " _
              & "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'"
            serverConn.Open()
            rowCount = sqlCommand.ExecuteNonQuery()
            serverConn.Close()
        Finally
            serverConn.Dispose()
        End Try

        Console.WriteLine("Rows inserted, updated, or deleted at the server: " & rowCount)

    End Sub 'MakeDataChangesOnServer


    'Revert changes that were made during synchronization.
    Public Sub CleanUpServer()
        Dim serverConn As New SqlConnection(Me.ServerConnString)
        Try
            Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.CommandText = "usp_InsertSampleData"

            serverConn.Open()
            sqlCommand.ExecuteNonQuery()
            serverConn.Close()
        Finally
            serverConn.Dispose()
        End Try

    End Sub 'CleanUpServer


    'Delete the client database.
    Public Sub RecreateClientDatabase()
        Dim clientConn As New SqlCeConnection(Me.ClientConnString)
        Try
            If File.Exists(clientConn.Database) Then
                File.Delete(clientConn.Database)
            End If
        Finally
            clientConn.Dispose()
        End Try

        Dim sqlCeEngine As New SqlCeEngine(Me.ClientConnString)
        sqlCeEngine.CreateDatabase()

    End Sub 'RecreateClientDatabase
End Class 'Utility

Complete Example Using SQL Server Change Tracking

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

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 connection 
            //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();

            //Delete and re-create the database. The client synchronization
            //provider also enables you to create the client database 
            //if it does not exist.
            util.SetClientPassword();
            util.RecreateClientDatabase();

            //Initial synchronization. Instantiate the SyncAgent
            //and call Synchronize.
            SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
            SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
            sampleStats.DisplayStats(syncStatistics, "initial");

            //Make changes on the server.
            util.MakeDataChangesOnServer();

            //Subsequent synchronization.
            syncStatistics = sampleSyncAgent.Synchronize();
            sampleStats.DisplayStats(syncStatistics, "subsequent");

            //Return server data back to its original state.
            util.CleanUpServer();

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

    //Create a class that is derived from 
    //Microsoft.Synchronization.SyncAgent.
    public class SampleSyncAgent : SyncAgent
    {
        public SampleSyncAgent()
        {
            //Instantiate a client synchronization provider and specify it
            //as the local provider for this synchronization agent.
            this.LocalProvider = new SampleClientSyncProvider();

            //Instantiate a server synchronization provider and specify it
            //as the remote provider for this synchronization agent.
            this.RemoteProvider = new SampleServerSyncProvider();

            //Add the Customer table: specify a synchronization direction of
            //DownloadOnly.
            SyncTable customerSyncTable = new SyncTable("Customer");
            customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
            this.Configuration.SyncTables.Add(customerSyncTable);
        }
    }

    //Create a class that is derived from 
    //Microsoft.Synchronization.Server.DbServerSyncProvider.
    public class SampleServerSyncProvider : DbServerSyncProvider
    {
        public SampleServerSyncProvider()
        {
            //Create a connection to the sample server database.
            Utility util = new Utility();
            SqlConnection serverConn = new SqlConnection(util.ServerConnString);
            this.Connection = serverConn;

            //Create a command to retrieve a new anchor value from
            //the server. In this case, we use a timestamp value
            //that is retrieved and stored in the client database.
            //During each synchronization, the new anchor value and
            //the last anchor value from the previous synchronization
            //are used: the set of changes between these upper and
            //lower bounds is synchronized.
            //
            //SyncSession.SyncNewReceivedAnchor is a string constant; 
            //you could also use @sync_new_received_anchor directly in 
            //your queries.
            SqlCommand selectNewAnchorCommand = new SqlCommand();
            string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
            selectNewAnchorCommand.CommandText =
                "SELECT " + newAnchorVariable + " = change_tracking_current_version()";
            selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt);
            selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
            selectNewAnchorCommand.Connection = serverConn;
            this.SelectNewAnchorCommand = selectNewAnchorCommand;


            //Create a SyncAdapter for the Customer table by using 
            //the SqlSyncAdapterBuilder:
            //  * Specify the base table name.
            //  * Specify that the server uses SQL Server change tracking.
            //  * Specify download-only synchronization.
            //  * Call ToSyncAdapter to create the SyncAdapter.
            //  * Specify a name for the SyncAdapter that matches the
            //    the name specified for the corresponding SyncTable.
            //    Do not include the schema names (Sales in this case).

            SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);

            customerBuilder.TableName = "Sales.Customer";
            customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;

            SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
            customerSyncAdapter.TableName = "Customer";
            this.SyncAdapters.Add(customerSyncAdapter);

        }
    }

    //Create a class that is derived from 
    //Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
    //You can just instantiate the provider directly and associate it
    //with the SyncAgent, but you could use this class to handle client 
    //provider events and other client-side processing.
    public class SampleClientSyncProvider : SqlCeClientSyncProvider
    {

        public SampleClientSyncProvider()
        {
            //Specify a connection string for the sample client database.
            Utility util = new Utility();
            this.ConnectionString = util.ClientConnString;
        }
    }

    //Handle the statistics that are returned by the SyncAgent.
    public class SampleStats
    {
        public void DisplayStats(SyncStatistics 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 Downloaded: " + syncStatistics.TotalChangesDownloaded);
            Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
            Console.WriteLine(String.Empty);

        }
    }

    public class Utility
    {

        private static string _clientPassword;

        //Get and set the client database password.
        public static string Password
        {
            get { return _clientPassword; }
            set { _clientPassword = value; }
        }

        //Have the user enter a password for the client database file.
        public void SetClientPassword()
        {
            Console.WriteLine("Type a strong password for the client");
            Console.WriteLine("database, and then press Enter.");
            Utility.Password = Console.ReadLine();
        }

        //Return the client connection string with the password.
        public string ClientConnString
        {
            get { return @"Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password; }
        }

        //Return the server connection string. 
        public string ServerConnString
        {

            get { return @"Data Source=localhost; Initial Catalog=SyncSamplesDb_ChangeTracking; Integrated Security=True"; }

        }

        //Make server changes that are synchronized on the second 
        //synchronization.
        public void MakeDataChangesOnServer()
        {
            int rowCount = 0;

            using (SqlConnection serverConn = new SqlConnection(this.ServerConnString))
            {
                SqlCommand sqlCommand = serverConn.CreateCommand();
                sqlCommand.CommandText =
                    "INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " +
                    "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " +
                    
                    "UPDATE Sales.Customer " +
                    "SET  SalesPerson = 'James Bailey' " +
                    "WHERE CustomerName = 'Tandem Bicycle Store' " +

                    "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'"; 
                
                serverConn.Open();
                rowCount = sqlCommand.ExecuteNonQuery();
                serverConn.Close();
            }

            Console.WriteLine("Rows inserted, updated, or deleted at the server: " + rowCount);
        }

        //Revert changes that were made during synchronization.
        public void CleanUpServer()
        {          
            using(SqlConnection serverConn = new SqlConnection(this.ServerConnString))
            {
                SqlCommand sqlCommand = serverConn.CreateCommand();
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "usp_InsertSampleData";
                
                serverConn.Open();               
                sqlCommand.ExecuteNonQuery();
                serverConn.Close();
            }
        }

        //Delete the client database.
        public void RecreateClientDatabase()
        {
            using (SqlCeConnection clientConn = new SqlCeConnection(this.ClientConnString))
            {
                if (File.Exists(clientConn.Database))
                {
                    File.Delete(clientConn.Database);
                }
            }
                
            SqlCeEngine sqlCeEngine = new SqlCeEngine(this.ClientConnString);
            sqlCeEngine.CreateDatabase();
        }
    }
}

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

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 connection 
        '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()

        'Delete and re-create the database. The client synchronization
        'provider also enables you to create the client database 
        'if it does not exist.
        util.SetClientPassword()
        util.RecreateClientDatabase()

        'Initial synchronization. Instantiate the SyncAgent
        'and call Synchronize.
        Dim sampleSyncAgent As New SampleSyncAgent()
        Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
        sampleStats.DisplayStats(syncStatistics, "initial")

        'Make changes on the server.
        util.MakeDataChangesOnServer()

        'Subsequent synchronization.
        syncStatistics = sampleSyncAgent.Synchronize()
        sampleStats.DisplayStats(syncStatistics, "subsequent")

        'Return server data back to its original state.
        util.CleanUpServer()

        '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.SyncAgent.
Public Class SampleSyncAgent
    Inherits SyncAgent

    Public Sub New()
        'Instantiate a client synchronization provider and specify it
        'as the local provider for this synchronization agent.
        Me.LocalProvider = New SampleClientSyncProvider()

        'Instantiate a server synchronization provider and specify it
        'as the remote provider for this synchronization agent.
        Me.RemoteProvider = New SampleServerSyncProvider()

        'Add the Customer table: specify a synchronization direction of
        'DownloadOnly.
        Dim customerSyncTable As New SyncTable("Customer")
        customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
        customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
        Me.Configuration.SyncTables.Add(customerSyncTable)

    End Sub 'New 
End Class 'SampleSyncAgent

'Create a class that is derived from 
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
    Inherits DbServerSyncProvider

    Public Sub New()
        'Create a connection to the sample server database.
        Dim util As New Utility()
        Dim serverConn As New SqlConnection(util.ServerConnString)
        Me.Connection = serverConn

        'Create a command to retrieve a new anchor value from
        'the server. In this case, we use a timestamp value
        'that is retrieved and stored in the client database.
        'During each synchronization, the new anchor value and
        'the last anchor value from the previous synchronization
        'are used: the set of changes between these upper and
        'lower bounds is synchronized.
        '
        'SyncSession.SyncNewReceivedAnchor is a string constant; 
        'you could also use @sync_new_received_anchor directly in 
        'your queries.
        Dim selectNewAnchorCommand As New SqlCommand()
        Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
        With selectNewAnchorCommand
            .CommandText = _
                "SELECT " + newAnchorVariable + " = change_tracking_current_version()"
            .Parameters.Add(newAnchorVariable, SqlDbType.BigInt)
            .Parameters(newAnchorVariable).Direction = ParameterDirection.Output
            .Connection = serverConn
        End With
        Me.SelectNewAnchorCommand = selectNewAnchorCommand

        'Create a SyncAdapter for the Customer table by using 
        'the SqlSyncAdapterBuilder:
        '  * Specify the base table names.
        '  * Specify that the server uses SQL Server change tracking.
        '  * Specify download-only synchronization.
        '  * Call ToSyncAdapter to create the SyncAdapter.
        '  * Specify a name for the SyncAdapter that matches the
        '    the name specified for the corresponding SyncTable.
        '    Do not include the schema names (Sales in this case).
        Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)

        customerBuilder.TableName = "Sales.Customer"
        customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking

        Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
        customerSyncAdapter.TableName = "Customer"
        Me.SyncAdapters.Add(customerSyncAdapter)

    End Sub 'New
End Class 'SampleServerSyncProvider

'Create a class that is derived from 
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
'You can just instantiate the provider directly and associate it
'with the SyncAgent, but you could use this class to handle client 
'provider events and other client-side processing.
Public Class SampleClientSyncProvider
    Inherits SqlCeClientSyncProvider


    Public Sub New()
        'Specify a connection string for the sample client database.
        Dim util As New Utility()
        Me.ConnectionString = util.ClientConnString

    End Sub 'New
End Class 'SampleClientSyncProvider

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

    Public Sub DisplayStats(ByVal syncStatistics As SyncStatistics, 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 Downloaded: " & syncStatistics.TotalChangesDownloaded)
        Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
        Console.WriteLine(String.Empty)

    End Sub 'DisplayStats 
End Class 'SampleStats


Public Class Utility

    Private Shared _clientPassword As String

    'Get and set the client database password.
    Public Shared Property Password() As String
        Get
            Return _clientPassword
        End Get
        Set(ByVal value As String)
            _clientPassword = value
        End Set
    End Property

    'Have the user enter a password for the client database file.
    Public Sub SetClientPassword()
        Console.WriteLine("Type a strong password for the client")
        Console.WriteLine("database, and then press Enter.")
        Utility.Password = Console.ReadLine()

    End Sub 'SetClientPassword

    'Return the client connection string with the password.
    Public ReadOnly Property ClientConnString() As String
        Get
            Return "Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password
        End Get
    End Property

    'Return the server connection string. 
    Public ReadOnly Property ServerConnString() As String

        Get
            Return "Data Source=localhost; Initial Catalog=SyncSamplesDb_ChangeTracking; Integrated Security=True"
        End Get
    End Property


    'Make server changes that are synchronized on the second 
    'synchronization.
    Public Sub MakeDataChangesOnServer()
        Dim rowCount As Integer = 0

        Dim serverConn As New SqlConnection(Me.ServerConnString)
        Try
            Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
            sqlCommand.CommandText = _
                "INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " _
              & "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " _
              & "UPDATE Sales.Customer " _
              & "SET  SalesPerson = 'James Bailey' " _
              & "WHERE CustomerName = 'Tandem Bicycle Store' " _
              & "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'"
            serverConn.Open()
            rowCount = sqlCommand.ExecuteNonQuery()
            serverConn.Close()
        Finally
            serverConn.Dispose()
        End Try

        Console.WriteLine("Rows inserted, updated, or deleted at the server: " & rowCount)

    End Sub 'MakeDataChangesOnServer


    'Revert changes that were made during synchronization.
    Public Sub CleanUpServer()
        Dim serverConn As New SqlConnection(Me.ServerConnString)
        Try
            Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.CommandText = "usp_InsertSampleData"

            serverConn.Open()
            sqlCommand.ExecuteNonQuery()
            serverConn.Close()
        Finally
            serverConn.Dispose()
        End Try

    End Sub 'CleanUpServer


    'Delete the client database.
    Public Sub RecreateClientDatabase()
        Dim clientConn As New SqlCeConnection(Me.ClientConnString)
        Try
            If File.Exists(clientConn.Database) Then
                File.Delete(clientConn.Database)
            End If
        Finally
            clientConn.Dispose()
        End Try

        Dim sqlCeEngine As New SqlCeEngine(Me.ClientConnString)
        sqlCeEngine.CreateDatabase()

    End Sub 'RecreateClientDatabase
End Class 'Utility

See Also

Concepts

Architecture and Classes for Client and Server Synchronization
Offline Scenarios (Sync Services)