Como limpar metadados para sincronização de colaboração (SQL Server)

Este tópico descreve como limpar metadados de bancos de dados do SQL Server e do SQL Server Compact sincronizados usando o Sync Framework. O código neste tópico enfoca as seguintes classes do Sync Framework:

• SqlSyncStoreMetadataCleanup

• SqlCeSyncStoreMetadataCleanup

Para obter mais informações sobre como executar o código de exemplo, consulte "Exemplo de aplicativos nos tópicos de instruções" em Sincronizando o SQL Server e o SQL Server Compact.

Noções básicas sobre a limpeza de metadados

A limpeza envolve a exclusão de metadados das linhas que foram excluídas de uma tabela base. O Sync Framework usa dois tipos de metadados:

• Metadados no nível de tabela que controlam as operações de inserção, atualização e exclusão de cada tabela sincronizada.

  Há uma linha de metadados para cada linha na tabela base. Se uma linha for excluída da tabela base e todos os nós em todos os escopos a receberam, a linha de metadados poderá ser excluída de forma segura.

• Metadados no nível de banco de dados que controlam quais alterações cada nó recebeu de outros nós.

  Em geral, esses metadados são armazenados em uma única tabela de escopo para cada banco de dados de nó. As linhas da tabela de escopo nunca devem ser excluídas, a menos que o escopo seja descartado.

A limpeza é baseada em retenção, o que significa que os metadados mais antigos que o número de dias especificado são excluídos. Para bancos de dados do SQL Server, use o objeto SqlSyncStoreMetadataCleanup e para bancos de dados do SQL Server Compact, use o objeto SqlCeSyncStoreMetadataCleanup. Os dois objetos têm as mesmas propriedades e métodos.

Se um nó tentar sincronizar alterações cujos metadados já tenham sido limpos, uma exceção do tipo DbOutdatedSyncException será lançada. O evento SyncPeerOutdated é gerado, fornecendo acesso a um objeto DbOutdatedEventArgs. Há duas opções para tratar esse evento:

• Defina a propriedade Action como PartialSync. Isso sincroniza os dados nos quais os metadados estão presentes, mas algumas exclusões são perdidas.

• Defina a propriedade Action como AbortSync (o padrão). Isso encerra a sessão de sincronização. O cliente deve ser reinicializado na próxima sessão de sincronização para que tenha os dados corretos.

Exemplo de código completo

O exemplo de código completo executa as seguintes etapas:

1. Sincroniza SyncSamplesDb_SqlPeer1 (Node1) e SyncSamplesDb_SqlPeer1 (Node2). Nove linhas são carregadas no Node2.

2. Sincroniza Node2 e SyncSampleClient1.sdf (Node3).

3. Executa uma inserção, atualização e exclusão em Node1.

4. Chama o PerformCleanup para metadados que tenham mais de 7 dias no Node1. O método PerformCleanup retorna com êxito, mas nenhum metadado é limpo, pois nenhuma exclusão com mais de sete dias foi feita no Node1.

   SqlSyncStoreMetadataCleanup metadataCleanup = new SqlSyncStoreMetadataCleanup(serverConn);
   bool cleanupSuccessful;
   metadataCleanup.RetentionInDays = 7;
   cleanupSuccessful = metadataCleanup.PerformCleanup();
   

   Dim metadataCleanup As New SqlSyncStoreMetadataCleanup(serverConn)
   Dim cleanupSuccessful As Boolean
   metadataCleanup.RetentionInDays = 7
   cleanupSuccessful = metadataCleanup.PerformCleanup()
   

5. Sincroniza Node1 e Node3, e Node2 e Node3. A sincronização tem êxito porque todos os metadados relevantes ainda estão disponíveis nos dois nós.

6. Exclui uma linha de Node1.

7. Chama PerformCleanup para todos os metadados no Node1. Os metadados para a exclusão da etapa anterior são limpos.

   metadataCleanup.RetentionInDays = 0;
   cleanupSuccessful = metadataCleanup.PerformCleanup();
   

   metadataCleanup.RetentionInDays = 0
   cleanupSuccessful = metadataCleanup.PerformCleanup()
   

8. Tenta sincronizar Node1 e Node3, e Node2 e Node3. A sincronização falha, pois o conhecimento da sincronização já não corresponde ao estado do nó. Uma exceção do tipo DbOutdatedSyncException é lançada.

É importante remover apenas os metadados que já não são mais necessários aos outros nós. Se a segunda limpeza tivesse ocorrido depois que o Node1 recebeu a exclusão do Node3, a sincronização teria sido bem-sucedida.

// NOTE: Before running this application, run the database sample script that is
// available in the documentation. The script drops and re-creates the tables that 
// are used in the code, and ensures that synchronization objects are dropped so that 
// Sync Framework can re-create them.

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.SqlServer;
using Microsoft.Synchronization.Data.SqlServerCe;

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

            // Create the connections over which provisioning and synchronization
            // are performed. 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.
            SqlConnection serverConn = new SqlConnection(Utility.ConnStr_SqlSync_Server);
            SqlConnection clientSqlConn = new SqlConnection(Utility.ConnStr_SqlSync_Client);
            SqlCeConnection clientSqlCe1Conn = new SqlCeConnection(Utility.ConnStr_SqlCeSync1);

            // Create a scope named "customer", and add the Customer and CustomerContact 
            // tables to the scope.
            // GetDescriptionForTable gets the schema of the table, so that tracking 
            // tables and triggers can be created for that table.
            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("customer");

            scopeDesc.Tables.Add(
            SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn));

            scopeDesc.Tables.Add(
            SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", serverConn));

            // Create a provisioning object for "customer" and specify that
            // base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1).
            SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
            serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);

            // Configure the scope and change-tracking infrastructure.
            serverConfig.Apply();

            // Retrieve scope information from the server and use the schema that is retrieved
            // to provision the SQL Server and SQL Server Compact client databases.           

            // This database already exists on the server.
            DbSyncScopeDescription clientSqlDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("customer", serverConn);
            SqlSyncScopeProvisioning clientSqlConfig = new SqlSyncScopeProvisioning(clientSqlConn, clientSqlDesc);
            clientSqlConfig.Apply();

            // This database does not yet exist.
            Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, true);
            DbSyncScopeDescription clientSqlCeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("customer", serverConn);
            SqlCeSyncScopeProvisioning clientSqlCeConfig = new SqlCeSyncScopeProvisioning(clientSqlCe1Conn, clientSqlCeDesc);
            clientSqlCeConfig.Apply();


            // Initial synchronization sessions.
            SampleSyncOrchestrator syncOrchestrator;
            SyncOperationStatistics syncStats;

            // Data is downloaded from the server to the SQL Server client.
            syncOrchestrator = new SampleSyncOrchestrator(
                new SqlSyncProvider("customer", clientSqlConn),
                new SqlSyncProvider("customer", serverConn)
                );
            syncStats = syncOrchestrator.Synchronize();
            syncOrchestrator.DisplayStats(syncStats, "initial");

            // Data is downloaded from the SQL Server client to the 
            // SQL Server Compact client.
            syncOrchestrator = new SampleSyncOrchestrator(
                new SqlCeSyncProvider("customer", clientSqlCe1Conn),
                new SqlSyncProvider("customer", clientSqlConn)
                );
            syncStats = syncOrchestrator.Synchronize();
            syncOrchestrator.DisplayStats(syncStats, "initial");


            // Make changes on the server: 1 insert, 1 update, and 1 delete.
            Utility.MakeDataChangesOnNode(Utility.ConnStr_SqlSync_Server, "Customer");

            SqlSyncStoreMetadataCleanup metadataCleanup = new SqlSyncStoreMetadataCleanup(serverConn);
            bool cleanupSuccessful;
            metadataCleanup.RetentionInDays = 7;
            cleanupSuccessful = metadataCleanup.PerformCleanup();

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


            // Synchronize the three changes.
            syncOrchestrator = new SampleSyncOrchestrator(
                new SqlSyncProvider("customer", clientSqlConn),
                new SqlSyncProvider("customer", serverConn)
                );
            syncStats = syncOrchestrator.Synchronize();
            syncOrchestrator.DisplayStats(syncStats, "subsequent");

            syncOrchestrator = new SampleSyncOrchestrator(
                new SqlSyncProvider("customer", clientSqlConn),
                new SqlCeSyncProvider("customer", clientSqlCe1Conn)
                );
            syncStats = syncOrchestrator.Synchronize();
            syncOrchestrator.DisplayStats(syncStats, "subsequent");


            Utility.MakeDataChangesOnNode(Utility.ConnStr_SqlSync_Server, "CustomerContact");

            metadataCleanup.RetentionInDays = 0;
            cleanupSuccessful = metadataCleanup.PerformCleanup();

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

            try
            {

                // Synchronize a final time.
                syncOrchestrator = new SampleSyncOrchestrator(
                    new SqlCeSyncProvider("customer", clientSqlCe1Conn),
                    new SqlSyncProvider("customer", serverConn)
                    );
                syncStats = syncOrchestrator.Synchronize();
                syncOrchestrator.DisplayStats(syncStats, "subsequent");

                syncOrchestrator = new SampleSyncOrchestrator(
                    new SqlSyncProvider("customer", clientSqlConn),
                    new SqlCeSyncProvider("customer", clientSqlCe1Conn)
                    );
                syncStats = syncOrchestrator.Synchronize();
                syncOrchestrator.DisplayStats(syncStats, "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);
            }

            serverConn.Close();
            serverConn.Dispose();
            clientSqlConn.Close();
            clientSqlConn.Dispose();
            clientSqlCe1Conn.Close();
            clientSqlCe1Conn.Dispose();

            Console.Write("\nPress any key to exit.");
            Console.Read();

        }

    }

    public class SampleSyncOrchestrator : SyncOrchestrator
    {
        public SampleSyncOrchestrator(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
        {

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

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

' NOTE: Before running this application, run the database sample script that is
' available in the documentation. The script drops and re-creates the tables that 
' are used in the code, and ensures that synchronization objects are dropped so that 
' Sync Framework can re-create them.

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.SqlServer
Imports Microsoft.Synchronization.Data.SqlServerCe

Namespace Microsoft.Samples.Synchronization

    Class Program

        Public Shared Sub Main(ByVal args As String())

            ' Create the connections over which provisioning and synchronization 
            ' are performed. 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 serverConn As New SqlConnection(Utility.ConnStr_SqlSync_Server)
            Dim clientSqlConn As New SqlConnection(Utility.ConnStr_SqlSync_Client)
            Dim clientSqlCe1Conn As New SqlCeConnection(Utility.ConnStr_SqlCeSync1)

            ' Create a scope named "customer", and add the Customer and CustomerContact 
            ' tables to the scope. 
            ' GetDescriptionForTable gets the schema of the table, so that tracking 
            ' tables and triggers can be created for that table. 
            Dim scopeDesc As New DbSyncScopeDescription("customer")

            scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn))

            scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", serverConn))

            ' Create a provisioning object for "customer" and specify that 
            ' base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1). 
            Dim serverConfig As New SqlSyncScopeProvisioning(serverConn, scopeDesc)
            serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip)

            ' Configure the scope and change-tracking infrastructure. 
            serverConfig.Apply()

            ' Retrieve scope information from the server and use the schema that is retrieved 
            ' to provision the SQL Server and SQL Server Compact client databases. 

            ' This database already exists on the server. 
            Dim clientSqlDesc As DbSyncScopeDescription = SqlSyncDescriptionBuilder.GetDescriptionForScope("customer", serverConn)
            Dim clientSqlConfig As New SqlSyncScopeProvisioning(clientSqlConn, clientSqlDesc)
            clientSqlConfig.Apply()

            ' This database does not yet exist. 
            Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, True)
            Dim clientSqlCeDesc As DbSyncScopeDescription = SqlSyncDescriptionBuilder.GetDescriptionForScope("customer", serverConn)
            Dim clientSqlCeConfig As New SqlCeSyncScopeProvisioning(clientSqlCe1Conn, clientSqlCeDesc)
            clientSqlCeConfig.Apply()


            ' Initial synchronization sessions. 
            Dim syncOrchestrator As SampleSyncOrchestrator
            Dim syncStats As SyncOperationStatistics

            ' Data is downloaded from the server to the SQL Server client. 
            syncOrchestrator = New SampleSyncOrchestrator( _
                New SqlSyncProvider("customer", clientSqlConn), _
                New SqlSyncProvider("customer", serverConn))
            syncStats = syncOrchestrator.Synchronize()
            syncOrchestrator.DisplayStats(syncStats, "initial")

            ' Data is downloaded from the SQL Server client to the 
            ' SQL Server Compact client. 
            syncOrchestrator = New SampleSyncOrchestrator( _
                New SqlCeSyncProvider("customer", clientSqlCe1Conn), _
                New SqlSyncProvider("customer", clientSqlConn))
            syncStats = syncOrchestrator.Synchronize()
            syncOrchestrator.DisplayStats(syncStats, "initial")


            ' Make changes on the server: 1 insert, 1 update, and 1 delete. 
            Utility.MakeDataChangesOnNode(Utility.ConnStr_SqlSync_Server, "Customer")

            Dim metadataCleanup As New SqlSyncStoreMetadataCleanup(serverConn)
            Dim cleanupSuccessful As Boolean
            metadataCleanup.RetentionInDays = 7
            cleanupSuccessful = metadataCleanup.PerformCleanup()

            If cleanupSuccessful = True Then
                Console.WriteLine([String].Empty)
                Console.WriteLine("Metadata cleanup ran in the 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 the three changes. 
            syncOrchestrator = New SampleSyncOrchestrator( _
                New SqlSyncProvider("customer", clientSqlConn), _
                New SqlSyncProvider("customer", serverConn))
            syncStats = syncOrchestrator.Synchronize()
            syncOrchestrator.DisplayStats(syncStats, "subsequent")

            syncOrchestrator = New SampleSyncOrchestrator( _
                New SqlSyncProvider("customer", clientSqlConn), _
                New SqlCeSyncProvider("customer", clientSqlCe1Conn))
            syncStats = syncOrchestrator.Synchronize()
            syncOrchestrator.DisplayStats(syncStats, "subsequent")


            Utility.MakeDataChangesOnNode(Utility.ConnStr_SqlSync_Server, "CustomerContact")

            metadataCleanup.RetentionInDays = 0
            cleanupSuccessful = metadataCleanup.PerformCleanup()

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

            Try

                ' Synchronize a final time. 
                syncOrchestrator = New SampleSyncOrchestrator( _
                    New SqlCeSyncProvider("customer", clientSqlCe1Conn), _
                    New SqlSyncProvider("customer", serverConn))
                syncStats = syncOrchestrator.Synchronize()
                syncOrchestrator.DisplayStats(syncStats, "subsequent")

                syncOrchestrator = New SampleSyncOrchestrator( _
                    New SqlSyncProvider("customer", clientSqlConn), _
                    New SqlCeSyncProvider("customer", clientSqlCe1Conn))
                syncStats = syncOrchestrator.Synchronize()
                syncOrchestrator.DisplayStats(syncStats, "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

            serverConn.Close()
            serverConn.Dispose()
            clientSqlConn.Close()
            clientSqlConn.Dispose()
            clientSqlCe1Conn.Close()
            clientSqlCe1Conn.Dispose()

            Console.Write(vbLf & "Press any key to exit.")

            Console.Read()
        End Sub

    End Class

    Public Class SampleSyncOrchestrator
        Inherits SyncOrchestrator
        Public Sub New(ByVal localProvider As RelationalSyncProvider, ByVal remoteProvider As RelationalSyncProvider)

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

        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
End Namespace

Consulte também

Outros recursos

Sincronizando o SQL Server e o SQL Server Compact