How to: Clean Up Metadata for Collaborative Synchronization (SQL Server)
This topic describes how to clean up metadata for SQL Server and SQL Server Compact databases that are synchronized by using Sync Framework. The code in this topic focuses on the following Sync Framework classes:
For more information about how to run sample code, see "Example Applications in the How to Topics" in Synchronizing SQL Server and SQL Server Compact.
Understanding Metadata Cleanup
Cleanup involves deleting metadata for rows that have been deleted from a base table. Sync Framework 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.
Cleanup is retention-based, which means that metadata that is older than the specified number of days is deleted. For SQL Server databases, use the SqlSyncStoreMetadataCleanup object; and for SQL Server Compact databases, use the SqlCeSyncStoreMetadataCleanup object. Both objects have the same properties and methods.
SQL Server |
SQL Server Compact |
Description |
|---|---|---|
The method that you call from your application to clean up metadata. |
||
The property that specifies how old change-tracking metadata must be, in days, for that metadata to be deleted when the cleanup method is called. |
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.
Complete Code Example
The complete code example performs the following steps:
Synchronizes SyncSamplesDb_SqlPeer1 (Node1) and SyncSamplesDb_SqlPeer1 (Node2). Nine rows are uploaded to Node2.
Synchronizes Node2 and SyncSampleClient1.sdf (Node3).
Performs an insert, update, and delete at Node1.
Calls PerformCleanup for metadata that is more than 7 days old at Node1. The PerformCleanup 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, and Node2 and Node3. Synchronization is successful because all relevant metadata is still available at both nodes.
Deletes a row from Node1.
Calls PerformCleanup for all metadata at Node1. The metadata for the delete in the previous step is cleaned up.
Attempts to synchronize Node1 and Node3, and Node2 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.
Important
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 first script in Setup Scripts for Database Provider How-to Topics.