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

Question

I've tried to get a solution for the above said question from MSDN Social and other resources from Microsoft. I was specifically looking for offline scenarios where client come online only to fetch updates from the Server.

There are plenty of articles on Sync covering Sql CE but MSFT team seems to be ignoring questions from the community on Syncing with Sql Express clients. There is no standard provider for Sql Express client yet, and Sql 2008 change tracking is not supported in the latest release too. Since Sql Replication is an expensive alternative, I have to go with Sync Framework.

Have anyone here in SO worked with Sync Framework, syncing through WCF endpoints? Please share code samples or sample projects.

Answer 1

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

• Enabled Change Tracking in SQL Server 2008
• Enabled change tacking 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()