Geeks With Blogs
Bruce Ge

I have been working on database synchronization for a couple of weeks, and seems it at the final testing stage, I am using Microsoft Sync Framework 2.0, the DB server is SQL server 2008 Standard, and clients are SQL Server 2008 Express as I use Change Tracking to track DB changes instead of TombStone Tables and Guid tracking columns, I use WCF+IIS+SSL to host server side service, the binding uses Gzip binary encoding.

The reason we are not using SQL Server Replication is because our tables need dynamic filters and some of them logic is quite complicated (we need to download the certain data to client according to the client name and setting), so when Replication try to generate the snapshot, it takes quite long time and CPU usage is quite high.

Coding didn't take me so long to finish (main part is write customized filter queries) but I realize some of the sync tables using bigint as primary key instead of Guid, so I have to assign a new seed to each client to avoid confliction. This new seed number will download and apply when clients reinitialize after schema download and applied.

Also, I found after "Applychanges" has been called, some tables identities will be changed, because in the "InsertCommand" of the Adaptor, using "SET IDENTITY_INSERT [tableName] ON", this will cause "If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.", so we use a new table to hold the current identity for tables before applychanges and set it back after applychanges.

I have found when uploading changes to server side, the dataset contains all changed data, and after ApplyChanges called, the return object SyncContext still contains the dataset which sent to server, this is very inefficient, also when some changes apply failed, the return size grows very big because the GroupProgress inside SyncContext contains all dataset info plus failed table and row info, this makes memory growing very fast on server side as well as transferring.

Server side need to maintain a session as the the clients call is each client based, so the WCF interface is like this:

[ServiceContract(SessionMode = SessionMode.Required)]
    public interface IServiceForSync
    {
        [OperationContract(IsInitiating = true)]
        bool InitialiseSetting();

        [OperationContract(IsInitiating = false)]
        SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession);

        [OperationContract(IsInitiating = false)]
        SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession);

        [OperationContract(IsInitiating = false)]
        SyncSchema GetSchema(Collection<string> tableNames, SyncSession syncSession);

        [OperationContract(IsInitiating = false)]
        SyncServerInfo GetServerInfo(SyncSession syncSession);

        [OperationContract(IsInitiating = false)]
        SyncInfo GetSyncInfo(string SyncHostName, long rpuid);

        [OperationContract(IsInitiating = false)]
        List<String> GetInitSchema();

        [OperationContract(IsInitiating = false)]
        List<String> GetUpdateSchema(string SyncHostName);

        [OperationContract(IsInitiating = false)]
        bool ResetStatus(string SyncHostName);

        [OperationContract(IsTerminating = true)]
        void EndSession(); 
    }

The Service side binding in the web.config is like this:

   <customBinding>
    <binding name="ISyncServer" receiveTimeout="00:30:00" sendTimeout="00:30:00">
     <gzipMessageEncoding innerMessageEncoding="binaryMessageEncoding"/>
     <reliableSession ordered="true" inactivityTimeout="00:10:00" maxPendingChannels="128"/>
     <httpsTransport hostNameComparisonMode="StrongWildcard" manualAddressing="False" maxReceivedMessageSize="2000000000" authenticationScheme="Anonymous" bypassProxyOnLocal="False" realm="" useDefaultWebProxy="True"/>
    </binding>
   </customBinding>

 

On client side the remoteServer of the syncAgent will be the proxy, and I use one SyncGroup as any exception happens, it will reverse back to original.

public class SqlExpressClientSyncProvider : ClientSyncProvider

and in the ApplyChanges of the ClientSyncProvider, we need to Map SyncDirection from client point of view to our internal server point of view, so part of code is like:

 foreach (var tableMetadata in groupMetadata.TablesMetadata)
            {
                switch (tableMetadata.SyncDirection)
                {
                    case SyncDirection.Snapshot:
                    case SyncDirection.DownloadOnly:
                        tableMetadata.SyncDirection = SyncDirection.UploadOnly;
                        break;
                    case SyncDirection.UploadOnly:
                        tableMetadata.SyncDirection = SyncDirection.DownloadOnly;
                        break;
                }
            }

in GetChanges, we need to swap anchors:

public override SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)
        {
            // neet to set the LastReceivedAnchor as the LastSentAnchor since
            // DbServerSyncProvider operates from the server's perspective, so
            // we swap the two fields temporarily.
            foreach (var metaTable in groupMetadata.TablesMetadata)
            {
                var temp = metaTable.LastReceivedAnchor;
                metaTable.LastReceivedAnchor = metaTable.LastSentAnchor;
                metaTable.LastSentAnchor = temp;
            }
          
            var context = _dbSyncProvider.GetChanges(groupMetadata, syncSession);


            if (_bEnableTimeAdjusting.HasValue && _bEnableTimeAdjusting.Value)
                AdjustDSTimeZone(context.DataSet, "CLIENT"); //-TN - adjust all datetime columns before sync.
            //swap them back for consistency
            foreach (var metaTable in groupMetadata.TablesMetadata)
            {
                var temp = metaTable.LastReceivedAnchor;
                metaTable.LastReceivedAnchor = metaTable.LastSentAnchor;
                metaTable.LastSentAnchor = temp;
            }
            return context;
        }

 

 

 

Posted on Sunday, October 18, 2009 11:30 PM Sync Framework | Back to top

Related Posts on Geeks With Blogs Matching Categories

Comments on this post: Sync Framework Common Practise of ADO.NET

# re: Sync Framework Common Practise of ADO.NET
Requesting Gravatar...
Hi, I started a project based on the publicly available sample SqlExpressProvider, trying to get an scenario very similar to yours to work but no success. In this post (http://social.microsoft.com/Forums/en-US/uklaunch2007ado.net/thread/ac7307fa-c17a-4a6d-a495-491c47ee86c0) I describe some of the issues I went through

I am using SqlServerChangeTracking at both sides, but in the client I am using a custom made Anchor table, should I be using something that is already present in SqlServer 2008 instead?

I started using an anchor table because before I was having an error saying that there was no adapter associated to my table, and then, that the anchor table "anchor" could not be found.

Will you post some minimal working sample?

Thanks a lot for your input.



Left by Miguel on Oct 20, 2009 2:42 AM

# re: Sync Framework Common Practise of ADO.NET
Requesting Gravatar...
Yes, I use anchor table too, becuase you are using SQL Server 2008, you need to change "SelectNewAnchorCommand".

currently the sample code is like:

SqlCommand anchorCmd = new SqlCommand();
anchorCmd.CommandType = CommandType.Text;
anchorCmd.CommandText = "Select @" + SyncSession.SyncNewReceivedAnchor + " = @@DBTS"; // for SQL Server 2005 SP2, use "min_active_rowversion() - 1"
anchorCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.Output;

you need to change it to be like:

SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText =
"SELECT " + newAnchorVariable + " = change_tracking_current_version()";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;

If you still have problem, could you pass me your code so I can have a look?

BTW: you need to use SqlSyncAdapterBuilder instead of using SyncAdapter directly, so you can specifying use change tracking:

var SyncBuilder = new SqlSyncAdapterBuilder(serverConn);
SyncBuilder.TableName = kvp.Key;
SyncBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
SyncAdapter syncAdapter = SyncBuilder.ToSyncAdapter();
this.SyncAdapters.Add(syncAdapter);
Left by Bruce Ge on Oct 20, 2009 9:23 AM

# re: Sync Framework Common Practise of ADO.NET
Requesting Gravatar...
Hi, posted a long put to {http://social.msdn.microsoft.com/Forums/en-US/uklaunch2007ado.net/thread/27e43dc3-e271-4b3d-bf0a-0024e739ffef} under the alias of mape1082, since it seems it is not allowed here.

Please have a look. I appreciate a lot your help.

Thanks.
Left by Miguel on Oct 23, 2009 2:47 AM

# re: Sync Framework Common Practise of ADO.NET
Requesting Gravatar...
Hi could you please post a working sample project with code and database. I've seen many people struggling to get sync framework work for them. Such a sample would be very well appreciated.

Thanks in advance.
Left by Raj on Oct 30, 2009 2:41 AM

# re: Sync Framework Common Practise of ADO.NET
Requesting Gravatar...
I will make a sample working project and put it here, hopefully I can complete it within this week, thanks for the comment.
Left by Bruce Ge on Nov 02, 2009 9:45 AM

# re: Sync Framework Common Practise of ADO.NET
Requesting Gravatar...
also looking forward for the entire sample project
Left by Vandamme Korneel on Nov 17, 2009 2:38 AM

# re: Sync Framework Common Practise of ADO.NET
Requesting Gravatar...
I'd be very appreciative of a sample working project - particularly to see how you dealt with updating the primary key updates in detail and more on the WCF SSL parts as I've gotten the sample code from the SQL Express sample to work in my project by I now need to make this an SSL service and I'm struggling as I have no experience with WCF or SSL. Any help you can provide would be hugely appreciated. Thanks!
Left by Liz on Dec 05, 2009 4:30 AM

# re: Sync Framework Common Practise of ADO.NET
Requesting Gravatar...
Hi, do you already have the link for the sample working project? Thank you very much. Thanks for sharing the post too.
Left by Boiler Insurance on May 10, 2010 8:14 PM

# re: Sync Framework Common Practise of ADO.NET
Requesting Gravatar...
Nice examples, I am studying this technology will it be possible to have copy of this apps
Left by Norwil on Jun 01, 2011 1:44 PM

Your comment:
 (will show your gravatar)


Copyright © argot | Powered by: GeeksWithBlogs.net