如何处理对等同步中的数据冲突和错误
本主题说明如何在 Sync Services for ADO.NET 中处理对等同步的数据冲突和错误。本主题中的示例着重介绍以下 Sync Services 类型和事件：
有关如何运行示例代码的信息，请参见对常见对等同步任务进行编程中的“帮助主题中的示例应用程序”。
了解数据冲突和错误
在 Sync Services 中，将在行级别上检测冲突和错误。如果在两次同步之间在多个节点上变更了同一行，则该行便存在冲突。同步期间发生的错误通常涉及违反约束，如主键重复。在设计应用程序时便应避免产生冲突（如果能够做到这一点），因为冲突的检测和解决会增加应用程序的复杂性，增加处理负担和网络流量。避免冲突的一些最常见方法包括：只在一个节点上更新某个表；或者对数据进行筛选以便只有一个节点会对特定行进行更新。在某些应用程序中，冲突是不可避免的。例如，在销售应用程序中，两个销售人员可能共享一个区域。他们都能更新同一客户和订单的数据。因此，Sync Services 提供了一组可供应用程序检测和解决冲突的功能。
在任何同步方案中，只要在多个节点上变更数据，便会发生数据冲突。在双向同步的情况下会产生冲突，但是，在只进行下载或上载的同步情况下也有可能产生冲突。例如，如果在某一节点上删除了某行，同时在其他节点上更新了这一行，那么当 Sync Services 尝试上载和应用第一个节点上的更新时便会发生冲突。
冲突总是出现在当前进行同步的两个节点之间。请考虑下列方案：
节点 A 和节点 B 同时与节点 C 进行双向同步。
在节点 A 上更新了某行，然后对节点 A 进行同步。此时不会出现任何冲突，会在节点 C 上应用该行。
在节点 B 上更新同一行，然后对节点 B 进行同步。现在，来自节点 B 的该行与节点 C 上的该行发生了冲突，因为节点 A 已对该行进行了更新。
如果以节点 C 优先的方式解决此冲突，Sync Services 会将节点 C 上的该行应用于节点 B。如果以节点 B 优先的方式解决冲突，则 Sync Services 会将来自节点 B 的该行应用于节点 C。在节点 A 与节点 C 之间进行下一次同步时，源自节点 B 的更新将应用于节点 A。
冲突和错误的类型
Sync Services 检测以下类型的冲突。这些类型定义在 DbConflictType 枚举中：
在两个节点插入的行带有相同主键时会发生 LocalInsertRemoteInsert 冲突。此类型的冲突又称为主键冲突。
两个节点对同一行进行变更时会发生 LocalUpdateRemoteUpdate 冲突。这是最常见的冲突类型。
当某一节点更新另一节点已经删除的行时，会发生 LocalUpdateRemoteDelete 和 LocalDeleteRemoteUpdate 冲突。
当某个错误阻止应用行时会发生 ErrorsOccurred 冲突。
冲突和错误的检测
如果在同步期间无法应用某行，通常是由于发生了错误或数据冲突。这两种情况均会引发 ApplyChangeFailed 事件。提供程序会在检测到冲突的节点引发错误。例如，如果为 Direction 属性指定了值 UploadAndDownload，将首先从本地提供程序将变更上载到远程提供程序。在这种情况下，您为 RemoteProvider 属性指定的提供程序将引发事件。如果首先下载变更然后再上载变更，将由为 LocalProvider 属性指定的提供程序引发该事件。不论由哪一个提供程序引发事件，也不论同步组件位于何处，都会将引发事件的节点上的数据变更视为本地变更 (LocalChange)，而将另一行视为远程变更 (RemoteChange)。这与客户端和服务器同步不同，后者的 ClientChange 和 ServerChange 始终分别与客户端数据库和服务器数据库关联。
引发 ApplyChangeFailed 事件后，通过使用为每个表的 DbSyncAdapter 对象定义的命令选择发生冲突的行。为 SelectRowCommand 属性指定的查询或存储过程可从对等数据库的基表中选择发生冲突的行。如果插入、更新或删除操作返回的 @sync_row_count 值为 0，则 Sync Services 将执行此命令。此值指示操作失败。
冲突和错误的解决
应该根据 ApplyChangeFailed 事件来相应地解决冲突和错误。DbApplyChangeFailedEventArgs 对象提供对冲突解决过程中可使用的多个属性的访问：
通过将 Action 属性设置为 ApplyAction 枚举值之一可指定如何解决冲突：
Continue：忽略冲突并继续执行同步。
RetryApplyingRow 和 RetryNextSync：重新尝试应用该行。如果没有通过变更存在冲突的行之一（或二者）来解决导致冲突的原因，则重试操作将失败，并将再次引发该事件。
RetryWithForceWrite：重新尝试逻辑以强制应用变更。若要使用此选项，请使用 @sync_force_write 参数并在将变更应用于远程数据库的命令中添加支持。例如，对于某个 LocalUpdateRemoteUpdate 冲突，可以在 @sync_force_write 设置为 1 时强制选择从本地提供程序进行的变更。有关示例代码，请参见本主题后面的“示例”一节。
通过使用 Conflict 属性获取冲突类型并查看来自各个节点的发生冲突的行。
通过使用 Context 属性获取要同步的变更数据集。通过 Conflict 属性公开的行是副本行。因此，覆盖这些行不会变更所应用的行。使用由 Context 属性公开的数据集可开发自定义解决方案（如果应用程序需要）。
备注
Sync Services API 中有一个类型和一个属性与冲突解决相关，但在本版本的 API 中未使用，它们是 DbResolveAction 和 ConflictResolutionPolicy。
示例
下面的代码示例演示如何为 Sync Services 对等示例数据库中的 Customer 表配置冲突检测和冲突解决。
API 的要点
本节提供了一些代码示例，指出了在冲突检测和冲突解决中使用的 API 的要点。下面的代码示例创建了一个存储过程，使用该过程可以从对等数据库的基表中选择发生冲突的行。
CREATE PROCEDURE Sales.sp_Customer_SelectRow
@CustomerId uniqueidentifier
AS
SELECT t.CustomerId,
c.CustomerName,
c.SalesPerson,
c.CustomerType,
t.sync_row_timestamp,
t.sync_row_is_tombstone,
t.sync_update_peer_key,
t.sync_update_peer_timestamp,
t.sync_create_peer_key,
t.sync_create_peer_timestamp
FROM Customer c RIGHT JOIN Customer_Tracking t ON c.CustomerId = t.CustomerId
WHERE t.CustomerId = @CustomerId
下面的代码示例为 SelectRowCommand 属性指定了 sp_Customer_SelectRow 存储过程。
SqlCommand selRowCustomerCmd = new SqlCommand();
selRowCustomerCmd.CommandType = CommandType.StoredProcedure;
selRowCustomerCmd.CommandText = "Sales.sp_Customer_SelectRow";
selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
adapterCustomer.SelectRowCommand = selRowCustomerCmd;
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
下面的代码示例创建一个将更新应用于对等数据库的存储过程。此过程是为 UpdateCommand 属性指定的。还可以使用存储过程将插入和删除操作应用于对等数据库。有关这些过程的示例，请参见用于 Sync Services 帮助主题的安装脚本。
CREATE PROCEDURE Sales.sp_Customer_ApplyUpdate (
@CustomerId uniqueidentifier,
@CustomerName nvarchar(100),
@SalesPerson nvarchar(100),
@CustomerType nvarchar(100),
@sync_min_timestamp bigint ,
@sync_row_count int OUT,
@sync_force_write int)
AS
UPDATE c
SET c.CustomerName = @CustomerName, c.SalesPerson = @SalesPerson, c.CustomerType = @CustomerType
FROM Customer c JOIN Customer_Tracking t ON c.CustomerId = t.CustomerId
WHERE ((t.sync_row_timestamp <= @sync_min_timestamp) OR @sync_force_write = 1)
AND t.CustomerId = @CustomerId
SET @sync_row_count = @@rowcount
sp_Customer_ApplyUpdate 更新过程基于 @sync_force_write 参数的值执行更新。如果该行已在本地数据库中更新并且参数设置为 0，则不应用远程更新。不过，如果参数设置为 1，远程更新将覆盖本地更新。
下面的代码示例说明如何在 ApplyChangeFailed 事件处理程序中处理“更新-更新”冲突。在本例中，冲突行显示在控制台中，同时有一个选项指定哪一行应在冲突中入选。如果运行本主题结尾处的完整代码示例，您将看到两组冲突行（分别在对等方 1 与对等方 2 同步时以及对等方 2 与对等方 3 同步时出现）。
if (e.Conflict.Type == DbConflictType.LocalUpdateRemoteUpdate)
{
//Get the conflicting changes from the Conflict object
//and display them. The Conflict object holds a copy
//of the changes; updates to this object will not be
//applied. To make changes, use the Context object.
DataTable conflictingRemoteChange = e.Conflict.RemoteChange;
DataTable conflictingLocalChange = e.Conflict.LocalChange;
int remoteColumnCount = conflictingRemoteChange.Columns.Count;
int localColumnCount = conflictingLocalChange.Columns.Count;
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine("Row from database " + DbConflictDetected);
Console.Write(" | ");
//Display the local row. As mentioned above, this is the row
//from the database at which the conflict was detected.
for (int i = 0; i < localColumnCount; i++)
{
Console.Write(conflictingLocalChange.Rows[0][i] + " | ");
}
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine("Row from database " + DbOther);
Console.Write(" | ");
//Display the remote row.
for (int i = 0; i < remoteColumnCount; i++)
{
Console.Write(conflictingRemoteChange.Rows[0][i] + " | ");
}
//Ask for a conflict resolution option.
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine("Enter a resolution option for this conflict:");
Console.WriteLine("A = change from " + DbConflictDetected + " wins.");
Console.WriteLine("B = change from " + DbOther + " wins.");
string conflictResolution = Console.ReadLine();
conflictResolution.ToUpper();
if (conflictResolution == "A")
{
e.Action = ApplyAction.Continue;
}
else if (conflictResolution == "B")
{
e.Action = ApplyAction.RetryWithForceWrite;
}
else
{
Console.WriteLine(String.Empty);
Console.WriteLine("Not a valid resolution option.");
}
}
If e.Conflict.Type = DbConflictType.LocalUpdateRemoteUpdate Then
'Get the conflicting changes from the Conflict object
'and display them. The Conflict object holds a copy
'of the changes; updates to this object will not be
'applied. To make changes, use the Context object.
Dim conflictingRemoteChange As DataTable = e.Conflict.RemoteChange
Dim conflictingLocalChange As DataTable = e.Conflict.LocalChange
Dim remoteColumnCount As Integer = conflictingRemoteChange.Columns.Count
Dim localColumnCount As Integer = conflictingLocalChange.Columns.Count
Console.WriteLine(String.Empty)
Console.WriteLine(String.Empty)
Console.WriteLine("Row from database " & DbConflictDetected)
Console.Write(" | ")
'Display the local row. As mentioned above, this is the row
'from the database at which the conflict was detected.
Dim i As Integer
For i = 0 To localColumnCount - 1
Console.Write(conflictingLocalChange.Rows(0)(i).ToString & " | ")
Next i
Console.WriteLine(String.Empty)
Console.WriteLine(String.Empty)
Console.WriteLine(String.Empty)
Console.WriteLine("Row from database " & DbOther)
Console.Write(" | ")
'Display the remote row.
For i = 0 To remoteColumnCount - 1
Console.Write(conflictingRemoteChange.Rows(0)(i).ToString & " | ")
Next i
'Ask for a conflict resolution option.
Console.WriteLine(String.Empty)
Console.WriteLine(String.Empty)
Console.WriteLine("Enter a resolution option for this conflict:")
Console.WriteLine("A = change from " & DbConflictDetected & " wins.")
Console.WriteLine("B = change from " & DbOther & " wins.")
Dim conflictResolution As String = Console.ReadLine()
conflictResolution.ToUpper()
If conflictResolution = "A" Then
e.Action = ApplyAction.Continue
ElseIf conflictResolution = "B" Then
e.Action = ApplyAction.RetryWithForceWrite
Else
Console.WriteLine(String.Empty)
Console.WriteLine("Not a valid resolution option.")
End If
下面的代码示例将错误信息记录到文件中。
else if (e.Conflict.Type == DbConflictType.ErrorsOccurred)
{
string logFile = @"C:\SyncErrorLog.txt";
Console.WriteLine(String.Empty);
Console.WriteLine("An error occurred during synchronization.");
Console.WriteLine("This error has been logged to " + logFile + ".");
StreamWriter streamWriter = File.AppendText(logFile);
StringBuilder outputText = new StringBuilder();
outputText.AppendLine("** APPLY CHANGE FAILURE AT " + DbConflictDetected.ToUpper() + " **");
outputText.AppendLine("Error source: " + e.Error.Source);
outputText.AppendLine("Error message: " + e.Error.Message);
streamWriter.WriteLine(DateTime.Now.ToShortTimeString() + " | " + outputText.ToString());
streamWriter.Flush();
streamWriter.Dispose();
}
ElseIf e.Conflict.Type = DbConflictType.ErrorsOccurred Then
Dim logFile As String = "C:\SyncErrorLog.txt"
Console.WriteLine(String.Empty)
Console.WriteLine("An error occurred during synchronization.")
Console.WriteLine("This error has been logged to " & logFile & ".")
Dim streamWriter As StreamWriter = File.AppendText(logFile)
Dim outputText As New StringBuilder()
outputText.AppendLine("** APPLY CHANGE FAILURE AT " & DbConflictDetected.ToUpper() & " **")
outputText.AppendLine("Error source: " & e.Error.Source)
outputText.AppendLine("Error message: " & e.Error.Message)
streamWriter.WriteLine(DateTime.Now.ToShortTimeString() & " | " & outputText.ToString())
streamWriter.Flush()
streamWriter.Dispose()
End If
完整的代码示例
下面的完整代码示例包括了上面介绍的代码示例以及用于执行同步的其他代码。示例需要 Utility 类，可通过用于 Sync Services 帮助主题的 Utility 类获得该类。
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. Note that data is not synchronized during the
//session between peer 1 and peer 3, because all rows have already
//been delivered to peer 3 during its synchronization session with peer 2.
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
sampleSyncAgent = new SampleSyncAgent(util.Peer1ConnString, util.Peer2ConnString);
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
sampleSyncAgent = new SampleSyncAgent(util.Peer2ConnString, util.Peer3ConnString);
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
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);
}
//Make conflicting changes in each peer database.
util.MakeConflictingChangesOnPeer(util.Peer1ConnString, "Customer");
util.MakeConflictingChangesOnPeer(util.Peer2ConnString, "Customer");
util.MakeConflictingChangesOnPeer(util.Peer3ConnString, "Customer");
try
{
//Subsequent synchronization. Changes are now synchronized between all
//peers.
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
sampleSyncAgent = new SampleSyncAgent(util.Peer1ConnString, util.Peer2ConnString);
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
sampleSyncAgent = new SampleSyncAgent(util.Peer2ConnString, util.Peer3ConnString);
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
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);
}
//Make a change in SyncSamplesDb_Peer2 that will fail when it
//is synchronized with SyncSamplesDb_Peer1.
util.MakeFailingChangeOnPeer(util.Peer2ConnString);
try
{
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
sampleSyncAgent = new SampleSyncAgent(util.Peer1ConnString, util.Peer2ConnString);
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);
}
//Return peer data back to its original state.
util.CleanUpPeer(util.Peer1ConnString);
util.CleanUpPeer(util.Peer2ConnString);
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
{
//Create class-level variables so that the ApplyChangeFailedEvent
//handler can use them.
private string _localProviderDatabase;
private string _remoteProviderDatabase;
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 on the sample class.
sampleSyncProvider.SetupSyncProvider(localProviderConnString, localProvider);
localProvider.SyncProviderPosition = SyncProviderPosition.Local;
sampleSyncProvider.SetupSyncProvider(remoteProviderConnString, remoteProvider);
remoteProvider.SyncProviderPosition = SyncProviderPosition.Remote;
//Specify event handlers for the ApplyChangeFailed event for each provider.
//The handlers are used to resolve conflicting rows and log error information.
localProvider.ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(dbProvider_ApplyChangeFailed);
remoteProvider.ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(dbProvider_ApplyChangeFailed);
//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;
_localProviderDatabase = localProvider.Connection.Database.ToString();
_remoteProviderDatabase = remoteProvider.Connection.Database.ToString();
}
private void dbProvider_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)
{
//For conflict detection, the "local" database is the one at which the
//ApplyChangeFailed event occurs. We determine at which database the event
//fired and then compare the name of that database to the names of
//the databases specified as the LocalProvider and RemoteProvider.
string DbConflictDetected = e.Connection.Database.ToString();
string DbOther;
DbOther = DbConflictDetected == _localProviderDatabase ? _remoteProviderDatabase : _localProviderDatabase;
Console.WriteLine(String.Empty);
Console.WriteLine("Conflict of type " + e.Conflict.Type + " was detected at " + DbConflictDetected + ".");
if (e.Conflict.Type == DbConflictType.LocalUpdateRemoteUpdate)
{
//Get the conflicting changes from the Conflict object
//and display them. The Conflict object holds a copy
//of the changes; updates to this object will not be
//applied. To make changes, use the Context object.
DataTable conflictingRemoteChange = e.Conflict.RemoteChange;
DataTable conflictingLocalChange = e.Conflict.LocalChange;
int remoteColumnCount = conflictingRemoteChange.Columns.Count;
int localColumnCount = conflictingLocalChange.Columns.Count;
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine("Row from database " + DbConflictDetected);
Console.Write(" | ");
//Display the local row. As mentioned above, this is the row
//from the database at which the conflict was detected.
for (int i = 0; i < localColumnCount; i++)
{
Console.Write(conflictingLocalChange.Rows[0][i] + " | ");
}
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine("Row from database " + DbOther);
Console.Write(" | ");
//Display the remote row.
for (int i = 0; i < remoteColumnCount; i++)
{
Console.Write(conflictingRemoteChange.Rows[0][i] + " | ");
}
//Ask for a conflict resolution option.
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine("Enter a resolution option for this conflict:");
Console.WriteLine("A = change from " + DbConflictDetected + " wins.");
Console.WriteLine("B = change from " + DbOther + " wins.");
string conflictResolution = Console.ReadLine();
conflictResolution.ToUpper();
if (conflictResolution == "A")
{
e.Action = ApplyAction.Continue;
}
else if (conflictResolution == "B")
{
e.Action = ApplyAction.RetryWithForceWrite;
}
else
{
Console.WriteLine(String.Empty);
Console.WriteLine("Not a valid resolution option.");
}
}
//Write any errors to a log file.
else if (e.Conflict.Type == DbConflictType.ErrorsOccurred)
{
string logFile = @"C:\SyncErrorLog.txt";
Console.WriteLine(String.Empty);
Console.WriteLine("An error occurred during synchronization.");
Console.WriteLine("This error has been logged to " + logFile + ".");
StreamWriter streamWriter = File.AppendText(logFile);
StringBuilder outputText = new StringBuilder();
outputText.AppendLine("** APPLY CHANGE FAILURE AT " + DbConflictDetected.ToUpper() + " **");
outputText.AppendLine("Error source: " + e.Error.Source);
outputText.AppendLine("Error message: " + e.Error.Message);
streamWriter.WriteLine(DateTime.Now.ToShortTimeString() + " | " + outputText.ToString());
streamWriter.Flush();
streamWriter.Dispose();
}
}
}
public class SampleSyncProvider
{
public DbSyncProvider SetupSyncProvider(string peerConnString, DbSyncProvider peerProvider)
{
//Set the amount of time to retain metadata.
const int MetadataAgingInHours = 100;
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. Note that data is not synchronized during the
'session between peer 1 and peer 3, because all rows have already
'been delivered to peer 3 during its synchronization session with peer 2.
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
sampleSyncAgent = New SampleSyncAgent(util.Peer1ConnString, util.Peer2ConnString)
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
sampleSyncAgent = New SampleSyncAgent(util.Peer2ConnString, util.Peer3ConnString)
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
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
'Make conflicting changes in each peer database.
util.MakeConflictingChangesOnPeer(util.Peer1ConnString, "Customer")
util.MakeConflictingChangesOnPeer(util.Peer2ConnString, "Customer")
util.MakeConflictingChangesOnPeer(util.Peer3ConnString, "Customer")
Try
'Subsequent synchronization. Changes are now synchronized between all
'peers.
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
sampleSyncAgent = New SampleSyncAgent(util.Peer1ConnString, util.Peer2ConnString)
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
sampleSyncAgent = New SampleSyncAgent(util.Peer2ConnString, util.Peer3ConnString)
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
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
'Make a change in SyncSamplesDb_Peer2 that will fail when it
'is synchronized with SyncSamplesDb_Peer1.
util.MakeFailingChangeOnPeer(util.Peer2ConnString)
Try
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
sampleSyncAgent = New SampleSyncAgent(util.Peer1ConnString, util.Peer2ConnString)
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
'Return peer data back to its original state.
util.CleanUpPeer(util.Peer1ConnString)
util.CleanUpPeer(util.Peer2ConnString)
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
'Create class-level variables so that the ApplyChangeFailedEvent
'handler can use them.
Private _localProviderDatabase As String
Private _remoteProviderDatabase As String
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 on the sample class.
sampleSyncProvider.SetupSyncProvider(localProviderConnString, localProvider)
localProvider.SyncProviderPosition = SyncProviderPosition.Local
sampleSyncProvider.SetupSyncProvider(remoteProviderConnString, remoteProvider)
remoteProvider.SyncProviderPosition = SyncProviderPosition.Remote
'Specify event handlers for the ApplyChangeFailed event for each provider.
'The handlers are used to resolve conflicting rows and log error information.
AddHandler localProvider.ApplyChangeFailed, AddressOf dbProvider_ApplyChangeFailed
AddHandler remoteProvider.ApplyChangeFailed, AddressOf dbProvider_ApplyChangeFailed
Me.LocalProvider = localProvider
Me.RemoteProvider = remoteProvider
Me.Direction = SyncDirectionOrder.UploadAndDownload
_localProviderDatabase = localProvider.Connection.Database.ToString()
_remoteProviderDatabase = remoteProvider.Connection.Database.ToString()
End Sub 'New
Private Sub dbProvider_ApplyChangeFailed(ByVal sender As Object, ByVal e As DbApplyChangeFailedEventArgs)
'For conflict detection, the "local" database is the one at which the
'ApplyChangeFailed event occurs. We determine at which database the event
'fired and then compare the name of that database to the names of
'the databases specified as the LocalProvider and RemoteProvider.
Dim DbConflictDetected As String = e.Connection.Database.ToString()
Dim DbOther As String
DbOther = IIf(DbConflictDetected = _localProviderDatabase, _remoteProviderDatabase, _localProviderDatabase)
Console.WriteLine(String.Empty)
Console.WriteLine("Conflict of type " & e.Conflict.Type.ToString & " was detected at " & DbConflictDetected & ".")
If e.Conflict.Type = DbConflictType.LocalUpdateRemoteUpdate Then
'Get the conflicting changes from the Conflict object
'and display them. The Conflict object holds a copy
'of the changes; updates to this object will not be
'applied. To make changes, use the Context object.
Dim conflictingRemoteChange As DataTable = e.Conflict.RemoteChange
Dim conflictingLocalChange As DataTable = e.Conflict.LocalChange
Dim remoteColumnCount As Integer = conflictingRemoteChange.Columns.Count
Dim localColumnCount As Integer = conflictingLocalChange.Columns.Count
Console.WriteLine(String.Empty)
Console.WriteLine(String.Empty)
Console.WriteLine("Row from database " & DbConflictDetected)
Console.Write(" | ")
'Display the local row. As mentioned above, this is the row
'from the database at which the conflict was detected.
Dim i As Integer
For i = 0 To localColumnCount - 1
Console.Write(conflictingLocalChange.Rows(0)(i).ToString & " | ")
Next i
Console.WriteLine(String.Empty)
Console.WriteLine(String.Empty)
Console.WriteLine(String.Empty)
Console.WriteLine("Row from database " & DbOther)
Console.Write(" | ")
'Display the remote row.
For i = 0 To remoteColumnCount - 1
Console.Write(conflictingRemoteChange.Rows(0)(i).ToString & " | ")
Next i
'Ask for a conflict resolution option.
Console.WriteLine(String.Empty)
Console.WriteLine(String.Empty)
Console.WriteLine("Enter a resolution option for this conflict:")
Console.WriteLine("A = change from " & DbConflictDetected & " wins.")
Console.WriteLine("B = change from " & DbOther & " wins.")
Dim conflictResolution As String = Console.ReadLine()
conflictResolution.ToUpper()
If conflictResolution = "A" Then
e.Action = ApplyAction.Continue
ElseIf conflictResolution = "B" Then
e.Action = ApplyAction.RetryWithForceWrite
Else
Console.WriteLine(String.Empty)
Console.WriteLine("Not a valid resolution option.")
End If
'Write any errors to a log file.
ElseIf e.Conflict.Type = DbConflictType.ErrorsOccurred Then
Dim logFile As String = "C:\SyncErrorLog.txt"
Console.WriteLine(String.Empty)
Console.WriteLine("An error occurred during synchronization.")
Console.WriteLine("This error has been logged to " & logFile & ".")
Dim streamWriter As StreamWriter = File.AppendText(logFile)
Dim outputText As New StringBuilder()
outputText.AppendLine("** APPLY CHANGE FAILURE AT " & DbConflictDetected.ToUpper() & " **")
outputText.AppendLine("Error source: " & e.Error.Source)
outputText.AppendLine("Error message: " & e.Error.Message)
streamWriter.WriteLine(DateTime.Now.ToShortTimeString() & " | " & outputText.ToString())
streamWriter.Flush()
streamWriter.Dispose()
End If
End Sub 'dbProvider_ApplyChangeFailed
End Class 'SampleSyncAgent
Public Class SampleSyncProvider
Public Function SetupSyncProvider(ByVal peerConnString As String, ByVal peerProvider As DbSyncProvider) As DbSyncProvider
'Set the amount of time to retain metadata.
Const MetadataAgingInHours As Integer = 100
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()
With selMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sales.sp_Customer_SelectMetadata"
.Parameters.Add("@metadata_aging_in_hours", SqlDbType.Int).Value = MetadataAgingInHours
End With
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