如何清除对等同步的元数据
本主题说明如何在 Sync Services for ADO.NET 中清除对等同步的元数据。此应用程序中的代码主要涉及以下 Sync Services 类：
有关如何运行示例代码的信息，请参见对常见对等同步任务进行编程中的“帮助主题中的示例应用程序”。
了解元数据清除
清除涉及删除已经从基表中删除的行的元数据。对等同步使用两种元数据：
用于跟踪同步的每个表的插入、更新和删除操作的表级元数据。
基表中的每一行都有一行元数据。此元数据可以存储在单独的跟踪表中（解除耦合的跟踪），也可以与用于跟踪删除操作的逻辑删除表一起存储在每个基表中（耦合跟踪）。如果从基表中删除某行并且所有对等方都已接收到该行，则可以安全地删除元数据行。
用于跟踪每个对等方从其他对等方接收到的变更的数据库级元数据。
对于每个对等数据库，此元数据通常存储在一个作用域表中。始终不应删除该作用域表中的行。
有关变更跟踪元数据的更多信息，请参见如何配置变更跟踪和同步对等方中的“创建用于存储元数据的跟踪表”。
元数据清除涉及两个组成部分：
您为每个表的 DbSyncAdapter 对象的 SelectMetadataForCleanupCommand 属性指定的命令。
从某个应用程序调用的 CleanupMetadata 方法。
不同于 DbSyncAdapter 对象的其他命令，在每个同步会话中不会自动调用清除命令。清除命令可以使用适用于您的应用程序的任意逻辑，但它通常基于保持期，即：删除超过特定时长的元数据。如果某个对等方试图同步在此清除之前发生的变更，将引发 DbOutdatedSyncException 类型的异常。这种情况在“完整的代码示例”中进行了演示。当目标上的知识元数据不包含源的“遗忘知识”**时，引发此异常。目标已过时，因为源不再具有目标所需的变更的所有元数据。有关遗忘知识的信息，请参见 Sync Framework 核心文档。若要下载此文档，请转到此 Microsoft 网站。
API 的要点
下面的代码示例显示为 SelectMetadataForCleanupCommand 属性指定的命令。所调用的存储过程 sp_Customer_SelectMetadata 以用小时数表示的时长为参数。这是元数据保持期。超过此时段的元数据将被清除。如果向该过程传递了值 -1，将清除所有元数据，而不考虑龄期。
备注
此示例显示一个清除元数据的方法。不要求查询或过程使用保持值作为参数或使用 -1 以指示应清除所有元数据。
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
下面的代码示例显示了由清除命令调用的存储过程。
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
下面的代码示例演示了如何调用 CleanupMetadata 方法。该代码对提供程序进行实例化，并调用 SampleSyncProvider 类中的 SetupSyncProvider 方法。所有所需的 DbSyncAdapter 和 DbSyncProvider 属性均在此类中定义。其中包括 SelectMetadataForCleanupCommand 属性。传递给 SetupSyncProvider 方法的值 100 是元数据保持期。
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
完整的代码示例
下面的完整代码示例包括了上面介绍的代码示例，以及用于执行变更和同步这些变更内容的附加代码。该代码执行以下步骤：
同步 SyncSamplesDb_Peer1 (Peer1) 和 SyncSamplesDb_Peer3 (Peer3)。将五行上载到 Peer3。
在 Peer1 上更新某一行。
针对 Peer1 上超过 100 个小时的元数据调用 CleanupMetadata。CleanupMetadata 方法成功返回，但是由于在过去 100 个小时内没有在 Peer1 上执行任何删除操作，所以未清除任何元数据。
同步 Peer1 和 Peer3。由于在两个对等方上仍然存在所有相关元数据，所以同步成功。
从 Peer3 中删除某一行。
为 Peer3 上的所有元数据调用 CleanupMetadata。将清除上一步的删除操作对应的元数据。
同步 Peer1 和 Peer3。由于同步知识不再与对等方的状态匹配，所以同步失败。将引发 DbOutdatedSyncException 类型的异常。
从上例可以看出，仅须清除其他对等方不再需要的元数据，这点很重要。如果第二次清除是在 Peer1 自 Peer3 接收到删除操作之后发生的，同步将成功。
.gif "Note") 重要事项 |
|---|
有意运行以下示例代码将使示例数据库处于不一致状态。运行此代码后，删除这些数据库，然后通过执行用于 Sync Services 帮助主题的安装脚本中的脚本再重新创建它们。 |
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