How to: Clean Up Metadata for Collaborative Synchronization (Non-SQL Server)
Nota
The topics in this section of the documentation, Synchronizing Other ADO.NET Compatible Databases, are designed to demonstrate how databases other than SQL Server can be synchronized by using Sync Framework. In this release, SQL Server is used in code examples, but the code can be used for other ADO.NET compatible databases, with some modifications to the SQL Server-specific objects (such as SqlConnection) and the SQL queries that are shown. For information about SQL Server synchronization, see How to: Execute Database Synchronization (SQL Server).
This topic describes how to clean up server metadata for peer-to-peer database synchronization in Sync Framework. The code in this topic describes the following Sync Framework classes:
For information about how to run sample code, see "Example Applications in the How to Topics" in Synchronizing Other ADO.NET Compatible Databases.
Understanding Metadata Cleanup
Cleanup involves deleting metadata for rows that have been deleted from a base table. Peer-to-peer synchronization uses two kinds of metadata:
Table-level metadata that tracks inserts, updates, and deletes for each table that is synchronized.
There is one row of metadata for each row in the base table. If a row is deleted from the base table and all nodes in all scopes have received it, the metadata row can be safely deleted.
Database-level metadata that tracks which changes each node has received from other nodes.
This metadata is typically stored in one scope table for each node database. Rows in the scope table should never be deleted unless the scope is dropped.
For more information about metadata, see "Create Tracking Tables for Per-Table Metadata" in How to: Provision a Server Database for Collaborative Synchronization (Non-SQL Server).
Metadata cleanup is handled by the application. For SQL Server Compact databases, use the SqlCeSyncStoreMetadataCleanup object:
The PerformCleanup is the method that you call from your application.
The RetentionInDays is the property that specifies how old change-tracking metadata must be, in days, for that metadata to be deleted when PerformCleanup is called.
For other databases, there are three components to metadata cleanup:
The CleanupMetadata method that you call from an application.
The command that you specify for the SelectMetadataForCleanupCommand property on the DbSyncAdapter object for each table. The CleanupMetadata method uses this command to select the rows that can be deleted.
The commands that you specify for the SelectOverlappingScopesCommand and UpdateScopeCleanupTimestampCommand properties:
The CleanupMetadata method uses SelectOverlappingScopesCommand before cleanup, to return the scope name and table name for all tables in the specified scope that are also included in other scopes.
The CleanupMetadata method uses UpdateScopeCleanupTimestampCommand after cleanup, to update the scope_cleanup_timestamp column for a particular scope in the scope_info table. This marks the point up to which cleanup has been performed for the scope.
Unlike other commands on the DbSyncAdapter object, the cleanup commands are not called automatically as part of each synchronization session. They are called only when an application calls the CleanupMetadata method. The command that you specify for the SelectMetadataForCleanupCommand property can use any logic that is appropriate to your application, but it is typically retention-based: metadata that is older than a particular length of time is deleted. If a node tries to synchronize changes whose metadata has already been cleaned up, an exception of type DbOutdatedSyncException is thrown. The SyncPeerOutdated event is raised, which provides access to a DbOutdatedEventArgs object. There are two options for handling this event:
Set the Action property to PartialSync. This synchronizes the data for which metadata is present, but some deletes are missed.
Set the Action property to AbortSync (the default). This ends the synchronization session. The client should be re-initialized in the next synchronization session so that it will have the correct data.
Key Parts of the API
The following code example specifies a command for the SelectMetadataForCleanupCommand property. The stored procedure that is called, sp_Customer_SelectMetadata, takes as a parameter a length of time in hours. This is the metadata retention period. Metadata that is older than this time period is cleaned up. If you pass a value of -1 to the procedure, all metadata is cleaned up, regardless of age.
Nota
This example shows one approach to metadata cleanup. There is no requirement for the query or procedure to use a retention value as a parameter or to use -1 to indicate that all metadata should be cleaned up.
The following code example creates the stored procedure that is called by the cleanup command.
The following code example specifies a command for the SelectOverlappingScopesCommand property. This command and the next command (UpdateScopeCleanupTimestampCommand) enable Sync Framework to handle cleanup appropriately in cases where a table is included in more than one scope.
The following code example creates the stored procedure that is called by the overlapping scopes command.
The following code example specifies a command for the UpdateScopeCleanupTimestampCommand property.
The following code example calls the CleanupMetadata method. The code instantiates a provider and calls the ConfigureDbSyncProvider method in the SampleSyncProvider class. All the required DbSyncAdapter and DbSyncProvider properties are defined in this class. This includes the SelectMetadataForCleanupCommand property. The value of 7 that is passed to the ConfigureDbSyncProvider method is the metadata retention period in days.
Complete Code Example
The following complete code example includes the code examples that are described earlier and additional code to make changes and synchronize those changes. The code performs the following steps:
Synchronizes SyncSamplesDb_Peer1 (Node1) and SyncSamplesDb_Peer3 (Node3). Five rows are uploaded to Node3.
Synchronizes Node3 and SyncSampleCe1 (CeNode1).
Updates a row at Node1.
Calls CleanupMetadata for metadata that is more than 7 days old at Node1. The CleanupMetadata method returns successfully, but no metadata is cleaned up because no deletes have been made at Node1 that are older than 7 days.
Synchronizes Node1 and Node3. Synchronization is successful because all relevant metadata is still available at both nodes.
Deletes a row from Node3.
Calls CleanupMetadata for all metadata at Node3. The metadata for the delete in the previous step is cleaned up.
Synchronizes Node1 and Node3. Synchronization fails because the synchronization knowledge no longer matches the state of the node. An exception of type DbOutdatedSyncException is thrown.
It is important to clean up only the metadata that is no longer required by other nodes. If the second cleanup had occurred after Node1 had received the delete from Node3, synchronization would have succeeded.
Importante
Running the following example code intentionally leaves the sample databases in an inconsistent state. After you run this code, drop the databases and re-create them by executing the script "Custom Change Tracking for Collaborative Scenarios" in Setup Scripts for Database Provider How-to Topics.