Compartir a través de


Limpiar los metadatos de la sincronización de colaboración (no SQL Server)

Nota

La finalidad de los temas de esta sección de la documentación, Sincronizar otras bases de datos compatibles con ADO.NET, es mostrar cómo se pueden sincronizar otras bases de datos distintas de SQL Server mediante Sync Framework. En esta versión, se utiliza SQL Server en los ejemplos de código, pero el código puede utilizarse en otras bases de datos compatibles con ADO.NET, siempre y cuando se modifiquen los objetos específicos de SQL Server (como SqlConnection) y las consultas SQL que se muestran. Para obtener información acerca de la sincronización de SQL Server, vea Configurar y ejecutar la sincronización de colaboración (SQL Server).

En este tema se describe cómo se limpian los metadatos del servidor durante la sincronización de bases de datos del mismo nivel en Sync Framework. En el código de este tema se describen las clases de Sync Framework siguientes:

Para obtener información acerca de cómo se ejecuta el código de ejemplo, vea "Aplicaciones de ejemplo en los temas sobre procedimientos" en Sincronizar otras bases de datos compatibles con ADO.NET.

Descripción del proceso de limpieza de los metadatos

Limpiar los metadatos implica eliminar los correspondientes a las filas que se han eliminado de una tabla base. La sincronización punto a punto utiliza dos tipos de metadatos:

  • Los metadatos del nivel de tabla que realizan el seguimiento de las inserciones, actualizaciones y eliminaciones de cada tabla que se sincroniza.

    Hay una fila de metadatos para cada fila de la tabla base. Si una fila se elimina de la tabla base y todos nodos de todos los ámbitos la han recibido, la fila de los metadatos se puede eliminar sin ningún riesgo.

  • Los metadatos del nivel de base de datos que realizan el seguimiento de los cambios que cada nodo ha recibido de otros nodos.

    Estos metadatos suelen almacenarse en una tabla de ámbito en cada nodo. Las filas de la tabla de ámbito nunca deben eliminarse hasta que se quite el ámbito.

Para obtener más información sobre los metadatos, vea "Crear tablas de seguimiento para metadatos por tabla" en Aprovisionar una base de datos servidor para la sincronización de colaboración (no SQL Server).

La aplicación administra la limpieza de metadatos. Para bases de datos de SQL Server Compact, utilice el objeto SqlCeSyncStoreMetadataCleanup.

  • PerformCleanup es el método al que se llama desde la aplicación.

  • RetentionInDays es la propiedad que especifica la antigüedad en días que deben tener los metadatos de seguimiento de cambios que se van a eliminar al llamar a PerformCleanup.

En otras bases de datos, hay tres componentes de limpieza de metadatos:

  • El método CleanupMetadata al que se llama desde una aplicación.

  • El comando que se especifica para la propiedad SelectMetadataForCleanupCommand del objeto DbSyncAdapter de cada tabla. El método CleanupMetadata utiliza este comando para seleccionar las filas que se pueden eliminar.

  • Los comandos que se especifican para las propiedades UpdateScopeCleanupTimestampCommand y SelectOverlappingScopesCommand:

    • El método CleanupMetadata utiliza SelectOverlappingScopesCommand antes de la limpieza para devolver el nombre de ámbito y el nombre de tabla de todas las tablas del ámbito especificado que también están incluidas en otros ámbitos.

    • El método CleanupMetadata utiliza UpdateScopeCleanupTimestampCommand después de la limpieza para actualizar la columna scope_cleanup_timestamp en un ámbito determinado de la tabla scope_info. De este modo, se marca el punto hasta el que se ha realizado la limpieza en el ámbito.

A diferencia de otros comandos del objeto DbSyncAdapter, a los comandos de limpieza no se les llama automáticamente durante cada sesión de sincronización. Solamente se les llama cuando una aplicación llama al método CleanupMetadata. El comando que se especifica para la propiedad SelectMetadataForCleanupCommand puede utilizar cualquier lógica que sea adecuada para su aplicación, pero normalmente se basa en la retención: los metadatos anteriores a un determinado período de tiempo se eliminan. Si un nodo intenta sincronizar los cambios cuyos metadatos ya se han limpiado, se produce una excepción de tipo DbOutdatedSyncException. Se genera el evento SyncPeerOutdated, que proporciona acceso a un objeto DbOutdatedEventArgs. Hay dos opciones para administrar este evento:

  • Establecer la propiedad Action en PartialSync. De este modo, se sincronizan los datos para los que hay metadatos, aunque algunas eliminaciones se omiten.

  • Establecer la propiedad Action en AbortSync (opción predeterminada). Esto pone fin a la sesión de sincronización. El cliente debe reinicializarse en la siguiente sesión de sincronización para que tenga los datos correctos.

Partes principales de la API

En el ejemplo de código siguiente se especifica un comando para la propiedad SelectMetadataForCleanupCommand. El procedimiento almacenado al que se llama, sp_Customer_SelectMetadata, toma como parámetro un intervalo de tiempo en horas. Se trata del período de retención de los metadatos. Los metadatos que sean anteriores a este periodo se limpian. Si se pasa el valor -1 al procedimiento, se limpian todos los metadatos, sin tener en cuenta su antigüedad.

Nota

En este ejemplo se muestra un enfoque para la limpieza de los metadatos. No hay ningún requisito para que en la consulta o en el procedimiento se utilice un valor de retención como parámetro o se utilice -1 para indicar que se deben limpiar todos los metadatos.

SqlCommand selMetadataCustomerCmd = new SqlCommand();
selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
selMetadataCustomerCmd.CommandText = "Sync.sp_Customer_SelectMetadata";
selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_days", SqlDbType.Int).Value = metadataAgingInDays;
selMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);

adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd;
Dim selMetadataCustomerCmd As New SqlCommand()
selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure
selMetadataCustomerCmd.CommandText = "Sync.sp_Customer_SelectMetadata"
selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_days", SqlDbType.Int).Value = metadataAgingInDays
selMetadataCustomerCmd.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)

adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd

En el ejemplo de código siguiente se crea el procedimiento almacenado al que llama el comando de limpieza.

CREATE PROCEDURE Sync.sp_Customer_SelectMetadata     
    @metadata_aging_in_days int,
    @sync_scope_local_id int
AS
    IF @metadata_aging_in_days = -1
        BEGIN
            SELECT  CustomerId,
                    local_update_peer_timestamp as sync_row_timestamp,  
                    case when (update_scope_local_id is null or update_scope_local_id <> @sync_scope_local_id) 
                        then case when (restore_timestamp is null) then local_update_peer_timestamp else restore_timestamp end else scope_update_peer_timestamp end as sync_update_peer_timestamp,
                    case when (update_scope_local_id is null or update_scope_local_id <> @sync_scope_local_id) 
                        then local_update_peer_key else scope_update_peer_key end as sync_update_peer_key,
                    case when (create_scope_local_id is null or create_scope_local_id <> @sync_scope_local_id) 
                        then local_create_peer_timestamp else scope_create_peer_timestamp end as sync_create_peer_timestamp,
                    case when (create_scope_local_id is null or create_scope_local_id <> @sync_scope_local_id) 
                        then local_create_peer_key else scope_create_peer_key end as sync_create_peer_key
            FROM Sync.Customer_Tracking
            WHERE sync_row_is_tombstone = 1
        END
    
    ELSE
        BEGIN
            SELECT  CustomerId,
                    local_update_peer_timestamp as sync_row_timestamp,  
                    case when (update_scope_local_id is null or update_scope_local_id <> @sync_scope_local_id) 
                        then case when (restore_timestamp is null) then local_update_peer_timestamp else restore_timestamp end else scope_update_peer_timestamp end as sync_update_peer_timestamp,
                    case when (update_scope_local_id is null or update_scope_local_id <> @sync_scope_local_id) 
                        then local_update_peer_key else scope_update_peer_key end as sync_update_peer_key,
                    case when (create_scope_local_id is null or create_scope_local_id <> @sync_scope_local_id) 
                        then local_create_peer_timestamp else scope_create_peer_timestamp end as sync_create_peer_timestamp,
                    case when (create_scope_local_id is null or create_scope_local_id <> @sync_scope_local_id) 
                        then local_create_peer_key else scope_create_peer_key end as sync_create_peer_key
            FROM Sync.Customer_Tracking
            WHERE sync_row_is_tombstone = 1 AND
            DATEDIFF(day, last_change_datetime, GETDATE()) > @metadata_aging_in_days
        END

En el ejemplo de código siguiente se especifica un comando para la propiedad SelectOverlappingScopesCommand. Este comando y el siguiente (UpdateScopeCleanupTimestampCommand) permiten que Sync Framework administre correctamente la limpieza en los casos en los que una tabla está incluida en varios ámbitos.

SqlCommand overlappingScopesCmd = new SqlCommand();
overlappingScopesCmd.CommandType = CommandType.StoredProcedure;
overlappingScopesCmd.CommandText = "Sync.sp_SelectSharedScopes";
overlappingScopesCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
sampleDbProvider.SelectOverlappingScopesCommand = overlappingScopesCmd;
Dim overlappingScopesCmd As New SqlCommand()
With overlappingScopesCmd
    .CommandType = CommandType.StoredProcedure
    .CommandText = "Sync.sp_SelectSharedScopes"
    .Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
End With

sampleDbProvider.SelectOverlappingScopesCommand = overlappingScopesCmd

En el ejemplo de código siguiente se crea el procedimiento almacenado al que llama el comando de ámbitos superpuestos.

CREATE PROCEDURE Sync.sp_SelectSharedScopes
      @sync_scope_name nvarchar(100)      
AS
   SELECT ScopeTableMap2.table_name AS sync_table_name, 
          ScopeTableMap2.scope_name AS sync_shared_scope_name
   FROM Sync.ScopeTableMap ScopeTableMap1 JOIN Sync.ScopeTableMap ScopeTableMap2
   ON ScopeTableMap1.table_name = ScopeTableMap2.table_name
   AND ScopeTableMap1.scope_name = @sync_scope_name
   WHERE ScopeTableMap2.scope_name <> @sync_scope_name

En el ejemplo de código siguiente se especifica un comando para la propiedad UpdateScopeCleanupTimestampCommand.

SqlCommand updScopeCleanupInfoCmd = new SqlCommand();
updScopeCleanupInfoCmd.CommandType = CommandType.Text;
updScopeCleanupInfoCmd.CommandText = "UPDATE  scope_info set " +
                                     " scope_cleanup_timestamp = @" + DbSyncSession.SyncScopeCleanupTimestamp + 
                                     " WHERE scope_name = @" + DbSyncSession.SyncScopeName + 
                                     " AND(scope_cleanup_timestamp is null or scope_cleanup_timestamp <  @" + DbSyncSession.SyncScopeCleanupTimestamp + ");" +
                                     " SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupTimestamp, SqlDbType.BigInt);
updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
sampleDbProvider.UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd;
Dim updScopeCleanupInfoCmd As New SqlCommand()
With updScopeCleanupInfoCmd
    .CommandType = CommandType.Text
    .CommandText = "UPDATE  scope_info set " _
                 & " scope_cleanup_timestamp = @" + DbSyncSession.SyncScopeCleanupTimestamp _
                 & " WHERE scope_name = @" + DbSyncSession.SyncScopeName _
                 & " AND(scope_cleanup_timestamp is null or scope_cleanup_timestamp <  @" + DbSyncSession.SyncScopeCleanupTimestamp + ");" _
                 & " SET @" + DbSyncSession.SyncRowCount + " = @@rowcount"
    .Parameters.Add("@" + DbSyncSession.SyncScopeCleanupTimestamp, SqlDbType.BigInt)
    .Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
    .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With

sampleDbProvider.UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd

En el ejemplo de código siguiente se llama al método CleanupMetadata. El código crea una instancia de un proveedor y llama al método ConfigureDbSyncProvider de la clase SampleSyncProvider. Todas las propiedades de DbSyncProvider y DbSyncAdapter necesarias se definen en esta clase. Se incluye la propiedad SelectMetadataForCleanupCommand. El valor de 7 que se pasa al método ConfigureDbSyncProvider es el período de retención de los metadatos en días.

sampleSyncProvider = new SampleSyncProvider();
DbSyncProvider provider1 = sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1, 7);

if (provider1.CleanupMetadata() == true)
{
    Console.WriteLine(String.Empty);
    Console.WriteLine("Metadata cleanup ran in the SyncSamplesDb_Peer1 database.");
    Console.WriteLine("Metadata more than 7 days old was deleted.");
}
else
{
    Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.");
}
sampleSyncProvider = New SampleSyncProvider()
Dim provider1 As DbSyncProvider = sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1, 7)

If provider1.CleanupMetadata() = True Then
    Console.WriteLine([String].Empty)
    Console.WriteLine("Metadata cleanup ran in the SyncSamplesDb_Peer1 database.")
    Console.WriteLine("Metadata more than 7 days old was deleted.")
Else
    Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.")
End If

Ejemplo de código completo

El ejemplo de código completo siguiente incluye los ejemplos de código descritos anteriormente y un código adicional para realizar los cambios y sincronizarlos. El código realiza los pasos siguientes:

  1. Sincroniza SyncSamplesDb_Peer1 (Node1) y SyncSamplesDb_Peer3 (Node3). Se cargan cinco filas en Node3.

  2. Sincroniza Node3 y SyncSampleCe1 (CeNode1).

  3. Actualiza una fila en Node1.

  4. Llama a CleanupMetadata para los metadatos que tienen una antigüedad superior a 7 días en Node1. El método CleanupMetadata devuelve resultados correctamente, pero no se limpia ningún metadato porque no se ha realizado ninguna eliminación en Node1 con una antigüedad superior a 7 días.

  5. Sincroniza Node1 y Node3. La sincronización se realiza correctamente porque todos los metadatos pertinentes siguen estando disponibles en ambos nodos.

  6. Elimina una fila de Node3.

  7. Llama a CleanupMetadata para todos los metadatos de Node3. Se limpian los metadatos de la eliminación del paso anterior.

  8. Sincroniza Node1 y Node3. Se produce un error en la sincronización porque el conocimiento de sincronización ya no coincide con el estado del nodo. Se produce una excepción de tipo DbOutdatedSyncException.

Es importante limpiar únicamente los metadatos que otros nodos ya no necesitan. Si la segunda limpieza se ha producido después de que Node1 hubiera recibido la eliminación de Node3, la sincronización se realiza correctamente.

Importante

Al ejecutar el código de ejemplo siguiente, las bases de datos de ejemplo se dejan intencionadamente en un estado incoherente. Después de ejecutar este código, quite las bases de datos y vuelva a crearlas ejecutando el script de "Seguimiento de cambios personalizados en escenarios de colaboración" que encontrará en Scripts de configuración para los temas de procedimientos del proveedor de base de datos.

using System;
using System.IO;
using System.Collections.Generic;
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.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 peerConnection 
            //string information and making changes to the server database.
            Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, true);

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

            try
            {
                //Initial synchronization. Instantiate the SyncOrchestrator
                //and call Synchronize.    
                sampleSyncProvider = new SampleSyncProvider();
                SyncOrchestrator sampleSyncAgent;
                SyncOperationStatistics syncStatistics;

                //The integer passed to ConfigureDbSyncProvider is how old that metadata
                //can be (in days) before it is deleted when CleanupMetadata() is called.
                //The integer value is only relevant if CleanupMetadata() is called, as
                //demonstrated later in this application.
                sampleSyncAgent = new SampleSyncAgent(
                                        sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1, 7),
                                        sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync3, 7));
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "initial");

                sampleSyncAgent = new SampleSyncAgent(
                        sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync3, 7),
                        sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1));
                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);
            }


            //Update a row on peer 1.
            Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer");


            //Instantiate a provider, connect to peer 1, and delete tombstone metadata that
            //is older than 7 days.
            sampleSyncProvider = new SampleSyncProvider();
            DbSyncProvider provider1 = sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1, 7);

            if (provider1.CleanupMetadata() == true)
            {
                Console.WriteLine(String.Empty);
                Console.WriteLine("Metadata cleanup ran in the SyncSamplesDb_Peer1 database.");
                Console.WriteLine("Metadata more than 7 days old was deleted.");
            }
            else
            {
                Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.");
            }

            //Synchronize.
            try
            {
                SyncOrchestrator sampleSyncAgent;
                SyncOperationStatistics syncStatistics;

                sampleSyncAgent = new SampleSyncAgent(
                                        sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1, 7),
                                        sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync3, 7));
                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);
            }


            //Delete a row on peer 3.
            Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync3, "Customer");


            //Instantiate a provider, connect to peer 3, and delete all tombstone metadata.
            sampleSyncProvider = new SampleSyncProvider();
            DbSyncProvider provider3 = sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync3, -1);


            if (provider3.CleanupMetadata() == true)
            {
                Console.WriteLine(String.Empty);
                Console.WriteLine("Metadata cleanup ran in the SyncSamplesDb_Peer3 database.");
                Console.WriteLine("All metadata was deleted.");
            }
            else
            {
                Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.");
            }


            //Synchronize.
            try
            {
                SyncOrchestrator sampleSyncAgent;
                SyncOperationStatistics syncStatistics;

                sampleSyncAgent = new SampleSyncAgent(
                                        sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1, 7),
                                        sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync3, 7));
                syncStatistics = sampleSyncAgent.Synchronize();
                sampleStats.DisplayStats(syncStatistics, "subsequent");
            }


            catch (DbOutdatedSyncException ex)
            {

                Console.WriteLine(String.Empty);
                Console.WriteLine("Synchronization failed due to outdated synchronization knowledge,");
                Console.WriteLine("which is expected in this sample application.");
                Console.WriteLine("Drop and recreate the sample databases.");
                Console.WriteLine(String.Empty);
                Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
                                  " Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
                Console.WriteLine(String.Empty);

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            //Return peer data back to its original state.
            Utility.CleanUpNode(Utility.ConnStr_DbSync1);
            Utility.CleanUpNode(Utility.ConnStr_DbSync3);

            //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(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
        {

            this.LocalProvider = localProvider;
            this.RemoteProvider = remoteProvider;
            this.Direction = SyncDirectionOrder.UploadAndDownload;

            //Check to see if any provider is a SqlCe provider and if it needs to
            //be initialized.
            CheckIfProviderNeedsSchema(localProvider as SqlCeSyncProvider, remoteProvider as DbSyncProvider);
            CheckIfProviderNeedsSchema(remoteProvider as SqlCeSyncProvider, localProvider as DbSyncProvider);
        }

        //For Compact databases that are not initialized with a snapshot,
        //get the schema and initial data from a server database.
        private void CheckIfProviderNeedsSchema(SqlCeSyncProvider providerToCheck, DbSyncProvider providerWithSchema)
        {

            //If one of the providers is a SqlCeSyncProvider and it needs
            //to be initialized, retrieve the schema from the other provider
            //if that provider is a DbSyncProvider; otherwise configure a
            //DbSyncProvider, connect to the server, and retrieve the schema.
            if (providerToCheck != null)
            {
                SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning();
                SqlCeConnection ceConn = (SqlCeConnection)providerToCheck.Connection;
                string scopeName = providerToCheck.ScopeName;
                if (!ceConfig.ScopeExists(scopeName, ceConn))
                {
                    DbSyncScopeDescription scopeDesc = providerWithSchema.GetScopeDescription();
                    ceConfig.PopulateFromScopeDescription(scopeDesc);
                    ceConfig.Apply(ceConn);
                }
            }

        }

    }

    public class SampleSyncProvider
    {

        public SqlCeSyncProvider ConfigureCeSyncProvider(string sqlCeConnString)
        {

            SqlCeSyncProvider sampleCeProvider = new SqlCeSyncProvider();

            //Set the scope name
            sampleCeProvider.ScopeName = "Sales";

            //Set the connection
            sampleCeProvider.Connection = new SqlCeConnection(sqlCeConnString);
 
            return sampleCeProvider;
        }


        public DbSyncProvider ConfigureDbSyncProvider(string peerConnString, int metadataAgingInDays)
        {

            DbSyncProvider sampleDbProvider = new DbSyncProvider();

            SqlConnection peerConnection = new SqlConnection(peerConnString);
            sampleDbProvider.Connection = peerConnection;
            sampleDbProvider.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 Framework 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 Framework 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 = "Sync.sp_Customer_SelectChanges";
            chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
            chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
            chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
            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 = "Sync.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 = "Sync.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 = "Sync.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;
            delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);

            adapterCustomer.DeleteCommand = delCustomerCmd;

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

            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 = "Sync.sp_Customer_InsertMetadata";
            insMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
            insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
            insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
            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.SyncCheckConcurrency, 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 = "Sync.sp_Customer_UpdateMetadata";
            updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
            updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
            updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
            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.SyncRowIsTombstone, SqlDbType.Int);
            updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
            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 = "Sync.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 = "Sync.sp_Customer_SelectMetadata";
            selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_days", SqlDbType.Int).Value = metadataAgingInDays;
            selMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);

            adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd;

            //Add the adapter to the provider.
            sampleDbProvider.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.            
            // * SelectTableMaxTimestampsCommand (optional): Returns the maximum timestamp from each base table 
            //   or tracking table, to determine whether for each table the destination already 
            //   has all of the changes from the source. If a destination table has all the changes,
            //   SelectIncrementalChangesCommand is not called for that table.
            // * SelectOverlappingScopesCommand: returns the scope name and table name for all tables 
            //   in the specified scope that are also included in other scopes.
            // * UpdateScopeCleanupTimestampCommand: updates the scope_cleanup_timestamp column for a 
            //   particular scope in the scope_info table, to mark the point up to which cleanup 
            //   has been performed for the scope.


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

            sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand;

            //Specify the command to select local replica metadata.
            SqlCommand selReplicaInfoCmd = new SqlCommand();
            selReplicaInfoCmd.CommandType = CommandType.Text;
            selReplicaInfoCmd.CommandText = "SELECT " +
                                            "scope_id, " +
                                            "scope_local_id, " +
                                            "scope_sync_knowledge, " +
                                            "scope_tombstone_cleanup_knowledge, " +
                                            "scope_timestamp " +
                                            "FROM Sync.ScopeInfo " +
                                            "WHERE scope_name = @" + DbSyncSession.SyncScopeName;
            selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);

            sampleDbProvider.SelectScopeInfoCommand = selReplicaInfoCmd;


            //Specify the command to update local replica metadata. 
            SqlCommand updReplicaInfoCmd = new SqlCommand();
            updReplicaInfoCmd.CommandType = CommandType.Text;
            updReplicaInfoCmd.CommandText = "UPDATE  Sync.ScopeInfo SET " +
                                            "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
                                            "scope_id = @" + DbSyncSession.SyncScopeId + ", " +
                                            "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.SyncScopeId, SqlDbType.UniqueIdentifier);
            updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt);
            updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

            sampleDbProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;


            //Return the maximum timestamp from the Customer_Tracking table.
            //If more tables are synchronized, the query should UNION
            //all of the results. The table name is not schema-qualified
            //in this case because the name was not schema qualified in the
            //DbSyncAdapter constructor.
            SqlCommand selTableMaxTsCmd = new SqlCommand();
            selTableMaxTsCmd.CommandType = CommandType.Text;
            selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " +
                                           "MAX(local_update_peer_timestamp) AS max_timestamp " +
                                           "FROM Sync.Customer_Tracking";
            sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd;


            //Specify the command to select table and scope names for
            //any tables that are in more than one scope.
            SqlCommand overlappingScopesCmd = new SqlCommand();
            overlappingScopesCmd.CommandType = CommandType.StoredProcedure;
            overlappingScopesCmd.CommandText = "Sync.sp_SelectSharedScopes";
            overlappingScopesCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
            sampleDbProvider.SelectOverlappingScopesCommand = overlappingScopesCmd;
            
            //Specify the command that updates the scope information table
            //to indicate to which point metadata has been cleaned up for a scope.
            SqlCommand updScopeCleanupInfoCmd = new SqlCommand();
            updScopeCleanupInfoCmd.CommandType = CommandType.Text;
            updScopeCleanupInfoCmd.CommandText = "UPDATE  scope_info set " +
                                                 " scope_cleanup_timestamp = @" + DbSyncSession.SyncScopeCleanupTimestamp + 
                                                 " WHERE scope_name = @" + DbSyncSession.SyncScopeName + 
                                                 " AND(scope_cleanup_timestamp is null or scope_cleanup_timestamp <  @" + DbSyncSession.SyncScopeCleanupTimestamp + ");" +
                                                 " SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
            updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupTimestamp, SqlDbType.BigInt);
            updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
            updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
            sampleDbProvider.UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd;
            
            return sampleDbProvider;

        }
    }

    //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.Collections.Generic
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.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 peerConnection 
        'string information and making changes to the server database. 
        Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, True)

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

        Try
            'Initial synchronization. Instantiate the SyncOrchestrator 
            'and call Synchronize. 
            sampleSyncProvider = New SampleSyncProvider()
            Dim sampleSyncAgent As SyncOrchestrator
            Dim syncStatistics As SyncOperationStatistics

            'The integer passed to ConfigureDbSyncProvider is how old that metadata 
            'can be (in days) before it is deleted when CleanupMetadata() is called. 
            'The integer value is only relevant if CleanupMetadata() is called, as 
            'demonstrated later in this application. 
            sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider( _
                                                  Utility.ConnStr_DbSync1, 7), _
                                                  sampleSyncProvider.ConfigureDbSyncProvider( _
                                                  Utility.ConnStr_DbSync3, 7))
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "initial")

            sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider( _
                                                  Utility.ConnStr_DbSync3, 7), _
                                                  sampleSyncProvider.ConfigureCeSyncProvider( _
                                                  Utility.ConnStr_SqlCeSync1))
            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


        'Update a row on peer 1. 
        Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer")


        'Instantiate a provider, connect to peer 1, and delete tombstone metadata that 
        'is older than 7 days. 
        sampleSyncProvider = New SampleSyncProvider()
        Dim provider1 As DbSyncProvider = sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1, 7)

        If provider1.CleanupMetadata() = True Then
            Console.WriteLine([String].Empty)
            Console.WriteLine("Metadata cleanup ran in the SyncSamplesDb_Peer1 database.")
            Console.WriteLine("Metadata more than 7 days old was deleted.")
        Else
            Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.")
        End If

        'Synchronize. 
        Try
            Dim sampleSyncAgent As SyncOrchestrator
            Dim syncStatistics As SyncOperationStatistics

            sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider( _
                                                  Utility.ConnStr_DbSync1, 7), _
                                                  sampleSyncProvider.ConfigureDbSyncProvider( _
                                                  Utility.ConnStr_DbSync3, 7))
            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


        'Delete a row on peer 3. 
        Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync3, "Customer")


        'Instantiate a provider, connect to peer 3, and delete all tombstone metadata. 
        sampleSyncProvider = New SampleSyncProvider()
        Dim provider3 As DbSyncProvider = sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync3, -1)


        If provider3.CleanupMetadata() = True Then
            Console.WriteLine([String].Empty)
            Console.WriteLine("Metadata cleanup ran in the SyncSamplesDb_Peer3 database.")
            Console.WriteLine("All metadata was deleted.")
        Else
            Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.")
        End If


        'Synchronize. 
        Try
            Dim sampleSyncAgent As SyncOrchestrator
            Dim syncStatistics As SyncOperationStatistics

            sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider( _
                                                  Utility.ConnStr_DbSync1, 7), _
                                                  sampleSyncProvider.ConfigureDbSyncProvider( _
                                                  Utility.ConnStr_DbSync3, 7))
            syncStatistics = sampleSyncAgent.Synchronize()
            sampleStats.DisplayStats(syncStatistics, "subsequent")
        Catch ex As DbOutdatedSyncException



            Console.WriteLine([String].Empty)
            Console.WriteLine("Synchronization failed due to outdated synchronization knowledge,")
            Console.WriteLine("which is expected in this sample application.")
            Console.WriteLine("Drop and recreate the sample databases.")
            Console.WriteLine([String].Empty)
            Console.WriteLine(("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() _
                               & " Clean up knowledge: ") + ex.MissingCleanupKnowledge.ToString())
            Console.WriteLine([String].Empty)

        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        'Return peer data back to its original state. 
        Utility.CleanUpNode(Utility.ConnStr_DbSync1)
        Utility.CleanUpNode(Utility.ConnStr_DbSync3)

        'Exit. 
        Console.Write(vbLf & "Press Enter to close the window.")
        Console.ReadLine()
    End Sub
End Class

'Create a class that is derived from 
'Microsoft.Synchronization.SyncOrchestrator. 
Public Class SampleSyncAgent
    Inherits SyncOrchestrator
    Public Sub New(ByVal localProvider As RelationalSyncProvider, ByVal _
                   remoteProvider As RelationalSyncProvider)

        Me.LocalProvider = localProvider
        Me.RemoteProvider = remoteProvider
        Me.Direction = SyncDirectionOrder.UploadAndDownload

        'Check to see if any provider is a SqlCe provider and if it needs to 
        'be initialized. 
        CheckIfProviderNeedsSchema(TryCast(localProvider, SqlCeSyncProvider), _
                                   TryCast(remoteProvider, DbSyncProvider))
        CheckIfProviderNeedsSchema(TryCast(remoteProvider, SqlCeSyncProvider), _
                                   TryCast(localProvider, DbSyncProvider))
    End Sub

    'For Compact databases that are not initialized with a snapshot, 
    'get the schema and initial data from a server database. 
    Private Sub CheckIfProviderNeedsSchema(ByVal providerToCheck As SqlCeSyncProvider, _
                                           ByVal providerWithSchema As DbSyncProvider)

        'If one of the providers is a SqlCeSyncProvider and it needs 
        'to be initialized, retrieve the schema from the other provider 
        'if that provider is a DbSyncProvider; otherwise configure a 
        'DbSyncProvider, connect to the server, and retrieve the schema. 
        If providerToCheck IsNot Nothing Then
            Dim ceConfig As New SqlCeSyncScopeProvisioning()
            Dim ceConn As SqlCeConnection = DirectCast(providerToCheck.Connection, SqlCeConnection)
            Dim scopeName As String = providerToCheck.ScopeName
            If Not ceConfig.ScopeExists(scopeName, ceConn) Then
                Dim scopeDesc As DbSyncScopeDescription = providerWithSchema.GetScopeDescription()
                ceConfig.PopulateFromScopeDescription(scopeDesc)
                ceConfig.Apply(ceConn)
            End If

        End If

    End Sub
End Class


Public Class SampleSyncProvider

    Public Function ConfigureCeSyncProvider(ByVal sqlCeConnString As String) As SqlCeSyncProvider

        Dim sampleCeProvider As New SqlCeSyncProvider()

        'Set the scope name 
        sampleCeProvider.ScopeName = "Sales"

        'Set the connection 
        sampleCeProvider.Connection = New SqlCeConnection(sqlCeConnString)

        Return sampleCeProvider
    End Function


    Public Function ConfigureDbSyncProvider(ByVal peerConnString As String, ByVal metadataAgingInDays As Integer) As DbSyncProvider

        Dim sampleDbProvider As New DbSyncProvider()

        Dim peerConnection As New SqlConnection(peerConnString)
        sampleDbProvider.Connection = peerConnection
        sampleDbProvider.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 Framework 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 Framework 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 = "Sync.sp_Customer_SelectChanges"
            .Parameters.Add("@" & DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
            .Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
            .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 = "Sync.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 = "Sync.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 = "Sync.sp_Customer_ApplyDelete"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
            .Parameters.Add("@" & DbSyncSession.SyncForceWrite, SqlDbType.Int)
        End With

        adapterCustomer.DeleteCommand = delCustomerCmd

        'Specify the command to select any conflicting rows. 
        Dim selRowCustomerCmd As New SqlCommand()
        With selRowCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sync.sp_Customer_SelectRow"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
        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. 
        Dim insMetadataCustomerCmd As New SqlCommand()
        With insMetadataCustomerCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sync.sp_Customer_InsertMetadata"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
            .Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
            .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.SyncCheckConcurrency, 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 = "Sync.sp_Customer_UpdateMetadata"
            .Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
            .Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
            .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.SyncCheckConcurrency, SqlDbType.Int)
            .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 = "Sync.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()
        selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure
        selMetadataCustomerCmd.CommandText = "Sync.sp_Customer_SelectMetadata"
        selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_days", SqlDbType.Int).Value = metadataAgingInDays
        selMetadataCustomerCmd.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)

        adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd


        'Add the adapter to the provider. 
        sampleDbProvider.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. 
        ' * SelectTableMaxTimestampsCommand (optional): Returns the maximum timestamp from each base table 
        ' or tracking table, to determine whether for each table the destination already 
        ' has all of the changes from the source. If a destination table has all the changes, 
        ' SelectIncrementalChangesCommand is not called for that table. 
        ' * SelectOverlappingScopesCommand: returns the scope name and table name for all tables 
        '   in the specified scope that are also included in other scopes.
        ' * UpdateScopeCleanupTimestampCommand: updates the scope_cleanup_timestamp column for a 
        '   particular scope in the scope_info table, to mark the point up to which cleanup 
        '   has been performed for the scope.


        '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 selectNewTimestampCommand As New SqlCommand()
        Dim newTimestampVariable As String = "@" & DbSyncSession.SyncNewTimestamp
        With selectNewTimestampCommand
            .CommandText = "SELECT " & newTimestampVariable & " = min_active_rowversion() - 1"
            .Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
            .Parameters(newTimestampVariable).Direction = ParameterDirection.Output
        End With

        sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand

        'Specify the command to select local replica metadata. 
        Dim selReplicaInfoCmd As New SqlCommand()
        With selReplicaInfoCmd
            .CommandType = CommandType.Text
            .CommandText = "SELECT " _
                         & "scope_id, " _
                         & "scope_local_id, " _
                         & "scope_sync_knowledge, " _
                         & "scope_tombstone_cleanup_knowledge, " _
                         & "scope_timestamp " _
                         & "FROM Sync.ScopeInfo " _
                         & "WHERE scope_name = @" + DbSyncSession.SyncScopeName
            .Parameters.Add("@" & DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
        End With

        sampleDbProvider.SelectScopeInfoCommand = selReplicaInfoCmd


        'Specify the command to update local replica metadata. 
        Dim updReplicaInfoCmd As New SqlCommand()
        With updReplicaInfoCmd
            .CommandType = CommandType.Text
            .CommandText = "UPDATE  Sync.ScopeInfo SET " _
                         & "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " _
                         & "scope_id = @" + DbSyncSession.SyncScopeId + ", " _
                         & "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.SyncScopeId, SqlDbType.UniqueIdentifier)
            .Parameters.Add("@" & DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
        End With

        sampleDbProvider.UpdateScopeInfoCommand = updReplicaInfoCmd


        'Return the maximum timestamp from the Customer_Tracking table. 
        'If more tables are synchronized, the query should UNION 
        'all of the results. The table name is not schema-qualified 
        'in this case because the name was not schema qualified in the 
        'DbSyncAdapter constructor. 
        Dim selTableMaxTsCmd As New SqlCommand()
        selTableMaxTsCmd.CommandType = CommandType.Text
        selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " _
                                     & "MAX(local_update_peer_timestamp) AS max_timestamp " _
                                     & "FROM Sync.Customer_Tracking"
        sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd


        'Specify the command to select table and scope names for
        'any tables that are in more than one scope.
        Dim overlappingScopesCmd As New SqlCommand()
        With overlappingScopesCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "Sync.sp_SelectSharedScopes"
            .Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
        End With

        sampleDbProvider.SelectOverlappingScopesCommand = overlappingScopesCmd

        'Specify the command that updates the scope information table
        'to indicate to which point metadata has been cleaned up for a scope.
        Dim updScopeCleanupInfoCmd As New SqlCommand()
        With updScopeCleanupInfoCmd
            .CommandType = CommandType.Text
            .CommandText = "UPDATE  scope_info set " _
                         & " scope_cleanup_timestamp = @" + DbSyncSession.SyncScopeCleanupTimestamp _
                         & " WHERE scope_name = @" + DbSyncSession.SyncScopeName _
                         & " AND(scope_cleanup_timestamp is null or scope_cleanup_timestamp <  @" + DbSyncSession.SyncScopeCleanupTimestamp + ");" _
                         & " SET @" + DbSyncSession.SyncRowCount + " = @@rowcount"
            .Parameters.Add("@" + DbSyncSession.SyncScopeCleanupTimestamp, SqlDbType.BigInt)
            .Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
            .Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
        End With

        sampleDbProvider.UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd

        Return sampleDbProvider

    End Function
End Class

'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
End Class

Vea también

Conceptos

Sincronizar otras bases de datos compatibles con ADO.NET