Limpiar los metadatos de la sincronización de colaboración (SQL Server)
En este tema se describe cómo limpiar los metadatos en las bases de datos de SQL Server y SQL Server Compact que se sincronizan utilizando Sync Framework. El código de este tema se centra en las clases de Sync Framework siguientes:
Para obtener más información acerca de cómo ejecutar código de ejemplo, vea "Aplicaciones de ejemplo en los temas sobre procedimientos" en Sincronizar SQL Server y SQL Server Compact.
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. Sync Framework usa 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.
La limpieza es un proceso que se basa en la retención, lo que significa que se eliminan los metadatos con una antigüedad superior al número de días especificado. En las bases de datos de SQL Server, utilice el objeto SqlSyncStoreMetadataCleanup y en las bases de datos de SQL Server Compact, utilice el objeto SqlCeSyncStoreMetadataCleanup. Ambos objetos tienen los mismos métodos y propiedades.
| SQL Server | SQL Server Compact | Descripción |
|---|---|---|
Método al que se llama desde la aplicación para limpiar los metadatos. |
||
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 al método de limpieza. |
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.
Ejemplo de código completo
En el ejemplo de código completo se realizan estos pasos:
Sincroniza SyncSamplesDb_SqlPeer1 (Node1) y SyncSamplesDb_SqlPeer1 (Node2). Se cargan nueve filas en Node2.
Sincroniza Node2 y SyncSampleClient1.sdf (Node3).
Realiza una inserción, una actualización y una eliminación en Node1.
Llama a PerformCleanup para los metadatos que tienen una antigüedad superior a 7 días en Node1. El método PerformCleanup 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.
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()Sincroniza Node1 y Node3, y Node2 y Node3. La sincronización se realiza correctamente porque todos los metadatos pertinentes siguen estando disponibles en ambos nodos.
Elimina una fila de Node1.
Llama a PerformCleanup para todos los metadatos de Node1. Se limpian los metadatos de la eliminación del paso anterior.
metadataCleanup.RetentionInDays = 0; cleanupSuccessful = metadataCleanup.PerformCleanup();metadataCleanup.RetentionInDays = 0 cleanupSuccessful = metadataCleanup.PerformCleanup()Intenta sincronizar Node1 y Node3, y Node2 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 primer script de Scripts de configuración para los temas de procedimientos del proveedor de base de datos.
// 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(scopeDesc);
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
// Configure the scope and change-tracking infrastructure.
serverConfig.Apply(serverConn);
// 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(clientSqlDesc);
clientSqlConfig.Apply(clientSqlConn);
// This database does not yet exist.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, true);
DbSyncScopeDescription clientSqlCeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("customer", serverConn);
SqlCeSyncScopeProvisioning clientSqlCeConfig = new SqlCeSyncScopeProvisioning(clientSqlCeDesc);
clientSqlCeConfig.Apply(clientSqlCe1Conn);
// 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(scopeDesc)
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip)
' Configure the scope and change-tracking infrastructure.
serverConfig.Apply(serverConn)
' 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(clientSqlDesc)
clientSqlConfig.Apply(clientSqlConn)
' 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(clientSqlCeDesc)
clientSqlCeConfig.Apply(clientSqlCe1Conn)
' 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