Syncing SQL Server 2008 Databases Over Http Using Wcf & Sync Framework

Syncing SQL Server 2008 Databases over HTTP using WCF & Sync Framework

I did the following to get Sync Framework working using WCF with SQL Server 2008

• Enabled Change Tracking in SQL Server 2008
• Enabled change tracking for tables participating in the Sync
• Added a metadata table named anchor
• Added a table to track client Ids named "guid"
• Used SqlExpressClientSyncProvider available from MSF's codeplex project site as Client Sync Provider

• Used SqlSyncAdapterBuilder to build adapters for tables participating in the Sync

  foreach (var item in anchorTables)
  {
      // Use adapter builder to generate T-SQL for querying change tracking data and CRUD
      SqlSyncAdapterBuilder builder = new SqlSyncAdapterBuilder();
      builder.Connection = new SqlConnection(this.connectionStringFactory.ConnectionString);
      builder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
      builder.SyncDirection = SyncDirection.Bidirectional;
      builder.TableName = item.TableName;
      // Get sync adapters from builder
      SyncAdapter clientAdapter = builder.ToSyncAdapter();
      clientAdapter.TableName = item.TableName;
      this.clientSyncProvider.SyncAdapters.Add(clientAdapter);
  }
  

• Added anchor commands

  SqlCommand anchroCommand =
     new SqlCommand { CommandText = "SELECT @" 
        + SyncSession.SyncNewReceivedAnchor 
        + " = change_tracking_current_version()" };
  
  anchroCommand.Parameters.Add("@" 
        + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt)
        .Direction = ParameterDirection.Output;
  
  this.clientSyncProvider.SelectNewAnchorCommand = anchroCommand;
  

• Implemented a WCF Service using a instance of DbServerSyncProvider functioning as Server sync provider. You will have generate sync adapters and set anchor command as shown in previous step for Server provider too.

  [ServiceContract]
  public interface ISyncService
  {
      [OperationContract]
      SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession);
      [OperationContract]
      SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession);
      [OperationContract]
      SyncSchema GetSchema(Collection<string> tableNames, SyncSession syncSession);
      [OperationContract]
      SyncServerInfo GetServerInfo(SyncSession syncSession);
  }
  

• Created a proxy class implementing ServerSyncProvider to access WCF service

  public class DbServerSyncProviderProxy : ServerSyncProvider
  {
      SyncServiceProxy.SyncServiceClient serviceProxy = new SyncServiceProxy.SyncServiceClient();
      public override SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)
      {
          return serviceProxy.ApplyChanges(groupMetadata, dataSet, syncSession);
      }
  }
  

• Created an instance of SyncAgent and set RemoteProvider with an instance of proxy class which is used to access WCF service. LocalProvider is set with instance of SqlExpressClientSyncProvider
• Added tables and sync groups to SyncAgent configuration
• SyncAgent.Synchronize()

Use Sync Framework with SQL 2008 change tracking on both local and remote

SQL Server Change tracking is only supported in the older offline provider (SqlCeClientSyncProvider/DbServerSyncProvider) which is the same providers used by the Local Database Cache project item in Visual Studio.

the newer SqlSyncProvider/SqlCeSyncProvider uses a custom tracking mechanism and it will take a great amount of customization to get it to work with Sql Change Tracking short of writing your own custom provider.

the SqlExpressClientSyncProvider is a sample provider that has since been taken out by MS from the download site.

this link still has some bits of it though: http://www.8bit.rs/blog/2009/05/debugging-sql-express-client-sync-provider/

Sync framework with SQL Server 2008 Change Tracking

Yes, this is possible. Use the Sql Express sample sync provider (http://archive.msdn.microsoft.com/sync/Release/ProjectReleases.aspx?ReleaseId=1200).
It is not supported by Microsoft and it uses Sync Framework 2.0 techniques instead of the newer 2.1 techniques for collaborative sync.

Enable change tracking on the server, create the anchor and guid table on the client. Create sync adapters per table in the sync agent and you're off. You have to keep in mind that the sql express client provider uses the server provider under the covers, so you need to reverse it's thinking in some places (download becomes upload).

Sync Framework between SQL Server 2008 and SQL Server CE

Turns out the anchors are collected like this:

this.SelectNewAnchorCommand.CommandText = "Select @sync_new_received_anchor = GETUTCDATE()";

That UTC date is what was causing the 8 hours gap. To fix it either change it to GETDATE(), or convert your columns to UTC time in the WHERE clause of the commands.

Sync Framework and SQL Server in a centralized model - does SF need to be installed on the central server?

No, you don't need to install sync framework on the central sever. All you need from the central database is a connection string. As long as you can access the central database with a login that has sufficient rights, then you don't need to install anything on it. The sync application can run from anywhere as long as it is able to connect to the central server.

Microsoft Sync Framework 2.1 + Change Tracking in Sql-Server 2008

See Syncing SQL Server 2008 Databases over HTTP using WCF & Sync Framework

---

Related Topics