Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Has anyone here worked with Sync Framework and syncing through WCF endpoints? Please share code samples or sample projects. I am specifically looking for offline scenarios where client comes online only to fetch updates from the Server.

like image 441
Raj Avatar asked Nov 01 '09 08:11

Raj


1 Answers

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()
like image 177
Raj Avatar answered Nov 15 '22 05:11

Raj