How to: Clean Up Metadata for Peer-to-Peer Synchronization
This topic shows how to clean up metadata for peer-to-peer synchronization in Sync Services for ADO.NET 2.0. The code in this application focuses on the following Sync Services classes:
For information about how to run sample code, see "Example Applications in the How to Topics" in Programming Common Peer-to-Peer Synchronization Tasks.
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. This metadata can be stored in a separate tracking table (decoupled tracking), or in each base table (coupled tracking) with a tombstone table to track deletes. If a row is deleted from the base table and all peers have received it, the metadata row can be safely deleted.
Database-level metadata that tracks which changes each peer has received from other peers.
This metadata is typically stored in one scope table for each peer database. Rows in the scope table should never be deleted.
For more information about change tracking metadata, see "Creating Tracking Tables to Store Metadata" in How to: Configure Change Tracking and Synchronize Peers.
There are two components to metadata cleanup:
The command that you specify for the SelectMetadataForCleanupCommand property for each table's DbSyncAdapter object.
The CleanupMetadata method that you call from an application.
Unlike other commands on the DbSyncAdapter object, the cleanup command is not called automatically as part of each synchronization session. The cleanup command 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 peer tries to synchronize changes that occurred before this cleanup, an exception of type DbOutdatedSyncException is thrown. This is demonstrated in "Complete Code Example". This exception is thrown when the knowledge metadata on the destination does not contain the forgotten knowledge from the source. The destination is outdated because the source no longer has all the metadata for the changes that the destination requires. For information about forgotten knowledge, see the Sync Framework core documentation. To download this documentation, go to this Microsoft Web site.
Key Parts of the API
The following code example shows the command that is specified 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.
Note
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.
SqlCommand selMetadataCustomerCmd = new SqlCommand();
selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
selMetadataCustomerCmd.CommandText = "Sales.sp_Customer_SelectMetadata";
selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_hours", SqlDbType.Int).Value = MetadataAgingInHours;
adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd;
Dim selMetadataCustomerCmd As New SqlCommand()
selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure
selMetadataCustomerCmd.CommandText = "Sales.sp_Customer_SelectMetadata"
selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_hours", SqlDbType.Int).Value = MetadataAgingInHours
adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd
The following code example shows the stored procedure that is called by the cleanup command.
CREATE PROCEDURE Sales.sp_Customer_SelectMetadata
@metadata_aging_in_hours int
AS
IF @metadata_aging_in_hours = -1
BEGIN
SELECT CustomerId,
sync_row_timestamp,
sync_update_peer_key,
sync_update_peer_timestamp,
sync_create_peer_key,
sync_create_peer_timestamp
FROM Customer_Tracking
WHERE sync_row_is_tombstone = 1
END
ELSE
BEGIN
SELECT CustomerId,
sync_row_timestamp,
sync_update_peer_key,
sync_update_peer_timestamp,
sync_create_peer_key,
sync_create_peer_timestamp
FROM Customer_Tracking
WHERE sync_row_is_tombstone = 1 AND
DATEDIFF(hh, last_change_datetime, GETDATE()) > @metadata_aging_in_hours
END
The following code example shows a call to the CleanupMetadata method. The code instantiates a provider and calls the SetupSyncProvider 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 100 that is passed to the SetupSyncProvider method is the metadata retention period.
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
DbSyncProvider provider1 = new DbSyncProvider();
provider1 = sampleSyncProvider.SetupSyncProvider(util.Peer1ConnString, provider1, 100);
if (provider1.CleanupMetadata() == true)
{
Console.WriteLine(String.Empty);
Console.WriteLine("Metadata cleanup ran in the peer database.");
Console.WriteLine("Metadata more than 100 hours old was deleted.");
}
else
{
Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.");
}
Dim sampleSyncProvider As New SampleSyncProvider()
Dim provider1 As New DbSyncProvider()
provider1 = sampleSyncProvider.SetupSyncProvider(util.Peer1ConnString, provider1, 100)
If provider1.CleanupMetadata() = True Then
Console.WriteLine(String.Empty)
Console.WriteLine("Metadata cleanup ran in the peer database.")
Console.WriteLine("Metadata more than 100 hours old was deleted.")
Else
Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.")
End If
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 (Peer1) and SyncSamplesDb_Peer3 (Peer3). Five rows are uploaded to Peer3.
Updates a row at Peer1.
Calls CleanupMetadata for metadata that is more than 100 hours old at Peer1. The CleanupMetadata method returns successfully, but no metadata is cleaned up because no deletes have been made at Peer1 in the last 100 hours.
Synchronizes Peer1 and Peer3. Synchronization is successful because all relevant metadata is still available at both peers.
Deletes a row from Peer3.
Calls CleanupMetadata for all metadata at Peer3. The metadata for the delete from the previous step is cleaned up.
Synchronizes Peer1 and Peer3. Synchronization fails because the synchronization knowledge no longer matches the state of the peer. An exception of type DbOutdatedSyncException is thrown.
As you can see from this example, it is important to clean up only the metadata that is no longer required by other peers. If the second cleanup had occurred after Peer1 had received the delete from Peer3, 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 script in Setup Scripts for Sync Services How-to Topics.
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
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 util = new Utility();
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
try
{
//Initial synchronization. Instantiate the SyncOrchestrator
//and call Synchronize.
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
sampleSyncAgent = new SampleSyncAgent(util.Peer1ConnString, util.Peer3ConnString);
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.
util.MakeDataChangesOnPeer(util.Peer1ConnString, "Customer");
//Instantiate a provider, connect to peer 1, and delete tombstone metadata that
//is older than 100 hours.
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
DbSyncProvider provider1 = new DbSyncProvider();
provider1 = sampleSyncProvider.SetupSyncProvider(util.Peer1ConnString, provider1, 100);
if (provider1.CleanupMetadata() == true)
{
Console.WriteLine(String.Empty);
Console.WriteLine("Metadata cleanup ran in the peer database.");
Console.WriteLine("Metadata more than 100 hours old was deleted.");
}
else
{
Console.WriteLine("Metadata cleanup failed, most likely due to concurrency issues.");
}
//Synchronize.
try
{
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
sampleSyncAgent = new SampleSyncAgent(util.Peer1ConnString, util.Peer3ConnString);
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.
util.MakeDataChangesOnPeer(util.Peer3ConnString, "Customer");
//Instantiate a provider, connect to peer 3, and delete all tombstone metadata.
DbSyncProvider provider3 = new DbSyncProvider();
provider3 = sampleSyncProvider.SetupSyncProvider(util.Peer3ConnString, provider3, -1);
if (provider3.CleanupMetadata() == true)
{
Console.WriteLine(String.Empty);
Console.WriteLine("Metadata cleanup ran in the peer 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(util.Peer1ConnString, util.Peer3ConnString);
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("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.
util.CleanUpPeer(util.Peer1ConnString);
util.CleanUpPeer(util.Peer3ConnString);
//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(string localProviderConnString, string remoteProviderConnString)
{
//Instantiate the sample provider that allows us to create a provider
//for both of the peers that are being synchronized.
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
//Instantiate a DbSyncProvider for the local peer and the remote peer.
//For example, if this code is running at peer1 and is
//synchronizing with peer2, peer1 would be the local provider
//and peer2 the remote provider.
DbSyncProvider localProvider = new DbSyncProvider();
DbSyncProvider remoteProvider = new DbSyncProvider();
//Create a provider by using the SetupSyncProvider method on the sample class.
sampleSyncProvider.SetupSyncProvider(localProviderConnString, localProvider, 100);
localProvider.SyncProviderPosition = SyncProviderPosition.Local;
sampleSyncProvider.SetupSyncProvider(remoteProviderConnString, remoteProvider, 100);
remoteProvider.SyncProviderPosition = SyncProviderPosition.Remote;
//Specify the local and remote providers that should be synchronized,
//and the direction and order of changes. In this case, changes are first
//uploaded from remote to local and then downloaded in the other direction.
this.LocalProvider = localProvider;
this.RemoteProvider = remoteProvider;
this.Direction = SyncDirectionOrder.UploadAndDownload;
}
}
public class SampleSyncProvider
{
public DbSyncProvider SetupSyncProvider(string peerConnString, DbSyncProvider peerProvider, int MetadataAgingInHours)
{
SqlConnection peerConnection = new SqlConnection(peerConnString);
peerProvider.Connection = peerConnection;
peerProvider.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 Services 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 Services 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 = "Sales.sp_Customer_SelectChanges";
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
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 = "Sales.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 = "Sales.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 = "Sales.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;
adapterCustomer.DeleteCommand = delCustomerCmd;
//Specify the command to select any conflicting rows.
SqlCommand selRowCustomerCmd = new SqlCommand();
selRowCustomerCmd.CommandType = CommandType.StoredProcedure;
selRowCustomerCmd.CommandText = "Sales.sp_Customer_SelectRow";
selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
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 = "Sales.sp_Customer_InsertMetadata";
insMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
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.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 = "Sales.sp_Customer_UpdateMetadata";
updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
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.SyncCheckConcurrency, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
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 = "Sales.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 = "Sales.sp_Customer_SelectMetadata";
selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_hours", SqlDbType.Int).Value = MetadataAgingInHours;
adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd;
peerProvider.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.
//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;
peerProvider.SelectNewTimestampCommand = selectNewTimestampCommand;
//Specify the command to select local replica metadata.
//Set session variables with values from the Sales.ScopeInfo
//metadata table.
SqlCommand selReplicaInfoCmd = new SqlCommand();
selReplicaInfoCmd.CommandType = CommandType.Text;
selReplicaInfoCmd.CommandText = "SELECT " +
"@" + DbSyncSession.SyncScopeId + " = scope_id, " +
"@" + DbSyncSession.SyncScopeKnowledge + " = scope_sync_knowledge, " +
"@" + DbSyncSession.SyncScopeCleanupKnowledge + " = scope_tombstone_cleanup_knowledge, " +
"@" + DbSyncSession.SyncScopeTimestamp + " = scope_timestamp " +
"FROM Sales.ScopeInfo " +
"WHERE scope_name = @" + DbSyncSession.SyncScopeName;
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output;
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output;
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt).Direction = ParameterDirection.Output;
peerProvider.SelectScopeInfoCommand = selReplicaInfoCmd;
//Specify the command to select local replica metadata.
//Update the Sales.ScopeInfo metadata table with values
//from session variables.
SqlCommand updReplicaInfoCmd = new SqlCommand();
updReplicaInfoCmd.CommandType = CommandType.Text;
updReplicaInfoCmd.CommandText = "UPDATE Sales.ScopeInfo SET " +
"scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
"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.SyncScopeTimestamp, SqlDbType.BigInt);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
peerProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;
return peerProvider;
}
}
//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.Text
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
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.
Dim util As New Utility()
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
Try
'Initial synchronization. Instantiate the SyncOrchestrator
'and call Synchronize.
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
sampleSyncAgent = New SampleSyncAgent(util.Peer1ConnString, util.Peer3ConnString)
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.
util.MakeDataChangesOnPeer(util.Peer1ConnString, "Customer")
'Instantiate a provider, connect to peer 1, and delete tombstone metadata that
'is older than 100 hours.
Dim sampleSyncProvider As New SampleSyncProvider()
Dim provider1 As New DbSyncProvider()
provider1 = sampleSyncProvider.SetupSyncProvider(util.Peer1ConnString, provider1, 100)
If provider1.CleanupMetadata() = True Then
Console.WriteLine(String.Empty)
Console.WriteLine("Metadata cleanup ran in the peer database.")
Console.WriteLine("Metadata more than 100 hours 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(util.Peer1ConnString, util.Peer3ConnString)
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.
util.MakeDataChangesOnPeer(util.Peer3ConnString, "Customer")
'Instantiate a provider, connect to peer 3, and delete all tombstone metadata.
Dim provider3 As New DbSyncProvider()
provider3 = sampleSyncProvider.SetupSyncProvider(util.Peer3ConnString, provider3, -1)
If provider3.CleanupMetadata() = True Then
Console.WriteLine(String.Empty)
Console.WriteLine("Metadata cleanup ran in the peer 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(util.Peer1ConnString, util.Peer3ConnString)
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("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.
util.CleanUpPeer(util.Peer1ConnString)
util.CleanUpPeer(util.Peer3ConnString)
'Exit.
Console.Write(vbLf + "Press Enter to close the window.")
Console.ReadLine()
End Sub 'Main
End Class 'Program
'Create a class that is derived from
'Microsoft.Synchronization.SyncOrchestrator.
Public Class SampleSyncAgent
Inherits SyncOrchestrator
Public Sub New(ByVal localProviderConnString As String, ByVal remoteProviderConnString As String)
'Instantiate the sample provider that allows us to create a provider
'for both of the peers that are being synchronized.
Dim sampleSyncProvider As New SampleSyncProvider()
'Instantiate a DbSyncProvider for the local peer and the remote peer.
'For example, if this code is running at peer1 and is
'synchronizing with peer2, peer1 would be the local provider
'and peer2 the remote provider.
Dim localProvider As New DbSyncProvider()
Dim remoteProvider As New DbSyncProvider()
'Create a provider by using the SetupSyncProvider method on the sample class.
sampleSyncProvider.SetupSyncProvider(localProviderConnString, localProvider, 100)
localProvider.SyncProviderPosition = SyncProviderPosition.Local
sampleSyncProvider.SetupSyncProvider(remoteProviderConnString, remoteProvider, 100)
remoteProvider.SyncProviderPosition = SyncProviderPosition.Remote
'Specify the local and remote providers that should be synchronized,
'and the direction and order of changes. In this case, changes are first
'uploaded from remote to local and then downloaded in the other direction.
Me.LocalProvider = localProvider
Me.RemoteProvider = remoteProvider
Me.Direction = SyncDirectionOrder.UploadAndDownload
End Sub 'New
End Class 'SampleSyncAgent
Public Class SampleSyncProvider
Public Function SetupSyncProvider(ByVal peerConnString As String, ByVal peerProvider As DbSyncProvider, ByVal MetadataAgingInHours As Integer) As DbSyncProvider
Dim peerConnection As New SqlConnection(peerConnString)
peerProvider.Connection = peerConnection
peerProvider.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 Services 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 Services 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 = "Sales.sp_Customer_SelectChanges"
.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.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 = "Sales.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 = "Sales.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 = "Sales.sp_Customer_ApplyDelete"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.DeleteCommand = delCustomerCmd
'Specify the command to select any conflicting rows.
Dim selRowCustomerCmd As New SqlCommand()
With selRowCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sales.sp_Customer_SelectRow"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
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. These are the same columns
'that were specified as DbSyncAdapter properties at the beginning
'of this code example.
Dim insMetadataCustomerCmd As New SqlCommand()
With insMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sales.sp_Customer_InsertMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.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.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 = "Sales.sp_Customer_UpdateMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.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.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.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 = "Sales.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 = "Sales.sp_Customer_SelectMetadata"
selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_hours", SqlDbType.Int).Value = MetadataAgingInHours
adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd
peerProvider.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.
'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 newTimestampVariable As String = "@" + DbSyncSession.SyncNewTimestamp
Dim selectNewTimestampCommand As New SqlCommand()
With selectNewTimestampCommand
.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1"
.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
.Parameters(newTimestampVariable).Direction = ParameterDirection.Output
End With
peerProvider.SelectNewTimestampCommand = selectNewTimestampCommand
'Specify the command to select local replica metadata.
'Set session variables with values from the Sales.ScopeInfo
'metadata table.
Dim selReplicaInfoCmd As New SqlCommand()
With selReplicaInfoCmd
.CommandType = CommandType.Text
.CommandText = "SELECT " _
& "@" + DbSyncSession.SyncScopeId + " = scope_id, " _
& "@" + DbSyncSession.SyncScopeKnowledge + " = scope_sync_knowledge, " _
& "@" + DbSyncSession.SyncScopeCleanupKnowledge + " = scope_tombstone_cleanup_knowledge, " _
& "@" + DbSyncSession.SyncScopeTimestamp + " = scope_timestamp " _
& "FROM Sales.ScopeInfo " _
& "WHERE scope_name = @" + DbSyncSession.SyncScopeName
.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output
.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output
.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output
.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt).Direction = ParameterDirection.Output
End With
peerProvider.SelectScopeInfoCommand = selReplicaInfoCmd
'Specify the command to select local replica metadata.
'Update the Sales.ScopeInfo metadata table with values
'from session variables.
Dim updReplicaInfoCmd As New SqlCommand()
With updReplicaInfoCmd
.CommandType = CommandType.Text
.CommandText = "UPDATE Sales.ScopeInfo SET " _
& "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " _
& "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.SyncScopeTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
peerProvider.UpdateScopeInfoCommand = updReplicaInfoCmd
Return peerProvider
End Function 'SetupSyncProvider
End Class 'SampleSyncProvider
'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 'DisplayStats
End Class 'SampleStats