locked
change tracking + WCF + IIS + SSL for sync framework accomplished RRS feed

Answers

  • Thanks for posting this - I am sure a lot of people will find this useful to get started.

    Thanks
    Deepa


    Deepa ( Microsoft Sync Framework)
    Wednesday, October 21, 2009 5:53 PM
    Answerer

All replies

  • Thanks for posting this - I am sure a lot of people will find this useful to get started.

    Thanks
    Deepa


    Deepa ( Microsoft Sync Framework)
    Wednesday, October 21, 2009 5:53 PM
    Answerer
  • Hi,

    {I am posting here since neither IE/FFox allowed me to post this to your page}

    Thanks a lot for your post. I Checked my code and it essentially had the same looks as the one you posted.

    I started documenting a bit my results in order to explain with more detail what is going on. Later in my post I included the source code used:

    Test 1, with the following line of code commented:
    -Anchor table Sent/Received are type bigint

                //sa.Configuration.SyncTables.Add( "MyTable", TableCreationOption.UseExistingTableOrFail, Microsoft.Synchronization.Data.SyncDirection.Bidirectional );

    cleared change tracking in the client
    deleted anchors and table contents
    set to snaphsot sync.
    result: no commands generated and 0 changes to apply, no failures.
    Restart the program but with download only.
    result: three commands generated and 0 changes to apply, no failures.\
    Restart the program but with upoad only.
    result: five commands generated and 0 changes to apply, no failures.
    In no case I hade record written to my client table.


    Test 2, with the following line of code enabled (and varying the sync method):
    -Anchor table Sent/Received are type bigint

                sa.Configuration.SyncTables.Add( "MyTable", TableCreationOption.UseExistingTableOrFail, Microsoft.Synchronization.Data.SyncDirection.SnapShot );

    set to snaphsot sync.
    result: no commands generated and 1158 changes to apply, no failures. No actual data written to my table!
    Restart the program but with download only.
    Exception at GetTable receivedAnchor : Unable to cast object of type 'System.Int64' to type 'System.Byte[]'.
    Solved (using BitConverter in the code) but then 'End of Stream encountered before parsing was completed', SerializationException, here at
    public virtual SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession) {
    going back to the varbinary64 anchors table.

    -Anchor table Sent/Received are type varbinary(64)

    result: no commands generated and 1158 changes to apply, no failures. No actual data written to my table!
    switched to Bidirectional:
    Total changes downloaded 0;
    Then
    Disabled change traking
    cleared table + anchor
    Enabled Tchange tracking
    Restared program, brougth 1158 records but all of them failed, with "SQL Server change tracking has cleaned up tracking information for table '[MyTable]'"
    Dropped the databse and recreated it
    Bidirectional worked fine.
    But if I click again Sync. then I have a nice "The remote server returned an unexpected response: (400) Bad Request."

    Cant go further than this..


    Now, My code looks like this:


            private void button1_Click(object sender, EventArgs e)
            {
                this.txtConnectionString.Text = connstr;
                
                // Create Conn Str
                conn = new SqlConnection(this.txtConnectionString.Text);
                
                // Open the Web Service
                XXXServiceReference.ServerDataCacheSyncContractClient svc = new WcfSynClient.XXXServiceReference.ServerDataCacheSyncContractClient();
                
                // Create Sync Providers
                SqlExpressClientSyncProvider clientProvider = new SqlExpressClientSyncProvider(); //ClientSyncProvider
                clientProvider.Connection = conn;

                ServerSyncProvider serverProvider = new ServerSyncProviderProxy(svc);
                SyncAgent sa = new SyncAgent();
                
                // Add the adapters for the tables to be synchronized
                CustomSqlSyncAdapterBuilder adapter = new CustomSqlSyncAdapterBuilder("MyTable", conn, Microsoft.Synchronization.Data.SyncDirection.Bidirectional);

                MessageBox.Show(adapter.GetCommandOutput());

                clientProvider.SyncAdapters.Add( adapter.ToSyncAdapter() );

                // <-- line of code used to make different tests
                sa.Configuration.SyncTables.Add("MyTable", TableCreationOption.UseExistingTableOrFail, Microsoft.Synchronization.Data.SyncDirection.Bidirectional);

                try
                {
                    sa.RemoteProvider = serverProvider;
                    sa.LocalProvider = clientProvider;
                    SyncStatistics ss = sa.Synchronize();

                    MessageBox.Show(String.Format("Total changes downloaded: {0}\nTotal changes uploaded: {1} \nDownload changes failed: {2}\nUpload changes failed: {3}", ss.TotalChangesDownloaded, ss.TotalChangesUploaded, ss.DownloadChangesFailed, ss.UploadChangesFailed));
                }
                catch (Exception ex)
                {
                    MessageBox.Show(String.Format("Exception occurred: {0}", ex.Message));
                }
                UpdateDataGrid();
            }



        public class CustomSqlSyncAdapterBuilder : SqlSyncAdapterBuilder
        {
            private string _dataColumns = String.Empty;
            private string _filterClause = String.Empty;

            public CustomSqlSyncAdapterBuilder(string TableName, SqlConnection conn, Microsoft.Synchronization.Data.SyncDirection dir)
            {
                ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
                this.TableName = TableName;
                this.Connection = conn;
                this.SyncDirection = dir;
            }
    ....


            /// <summary>
            /// Gets the changes made on the client since last sync.
            /// </summary>
            /// <param name="groupMetadata"> Contains table metadata </param>
            /// <param name="syncSession"> The current sync session </param>
            /// <returns> SyncContext populated with the incremental changes </returns>
            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 (SyncTableMetadata metaTable in groupMetadata.TablesMetadata)
                {
                    SyncAnchor temp = metaTable.LastReceivedAnchor;
                    metaTable.LastReceivedAnchor = metaTable.LastSentAnchor;
                    metaTable.LastSentAnchor = temp;
                }

                SyncContext context = _dbSyncProvider.GetChanges(groupMetadata, syncSession);

                //swap them back for consistency
                foreach (SyncTableMetadata metaTable in groupMetadata.TablesMetadata)
                {
                    SyncAnchor temp = metaTable.LastReceivedAnchor;
                    metaTable.LastReceivedAnchor = metaTable.LastSentAnchor;
                    metaTable.LastSentAnchor = temp;
                }
                return context;
            }



            /// <summary>
            /// Apply changes downloaded from the server.
            /// </summary>
            /// <remarks>
            /// Inner _dbSyncProvider will take care of applying changes to actual
            /// data, but we need to take care of updating anchor metadata.
            /// </remarks>
            /// <param name="groupMetadata"> Contains table metadata info </param>
            /// <param name="dataSet"> Contains changes to be applied </param>
            /// <param name="syncSession"> Current sync session </param>
            /// <returns> SyncContext object to Sync Agent </returns>
            public override SyncContext ApplyChanges(
                SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)
            {
                SwapAnchors(groupMetadata);

                //_log.Debug( "ApplyChanges" );
                //Map SyncDirection from client POV to our internal server POV
                foreach (SyncTableMetadata tableMetadata in groupMetadata.TablesMetadata)
                {
                    if (tableMetadata.SyncDirection == SyncDirection.DownloadOnly ||
                        tableMetadata.SyncDirection == SyncDirection.Snapshot)
                    {
                        //This SyncDirection DownloadOnly/Snapshot is from a Client point of view. But our client is inturn a Server provider.   Hence switch this to UploadOnly
                        tableMetadata.SyncDirection = SyncDirection.UploadOnly;
                    }
                    else if (tableMetadata.SyncDirection == SyncDirection.UploadOnly)
                    {
                        //This SyncDirection UploadOnly is from Client POV. But our client is inturn a Server provider. Hence switch this to DownloadOnly
                        tableMetadata.SyncDirection = SyncDirection.DownloadOnly;
                    }
                }
                SyncContext syncContext = _dbSyncProvider.ApplyChanges(groupMetadata, dataSet, syncSession);

                SwapAnchors(groupMetadata);

                foreach (SyncTableMetadata table in groupMetadata.TablesMetadata)
                    SetTableReceivedAnchor(table.TableName, groupMetadata.NewAnchor);

                return syncContext;
            }

            private static void SwapAnchors(SyncGroupMetadata groupMetadata)
            {
                // neet to set the LastReceivedAnchor as the LastSentAnchor since
                // DbServerSyncProvider operates from the server's perspective, so
                // we swap the two fields temporarily.
                foreach (SyncTableMetadata metaTable in groupMetadata.TablesMetadata)
                {
                    SyncAnchor temp = metaTable.LastReceivedAnchor;
                    metaTable.LastReceivedAnchor = metaTable.LastSentAnchor;
                    metaTable.LastSentAnchor = temp;
                }
            }



    In the code I've used parts from the sample and patches people have posted in these forums.


    At this point I have more questions:

    1. Why my code does throw exception with an Anchors table that has bigint columns, but does not break when the columns are varbinary(64)?

    2. Why after synching once, if I try to sync again (which should result in zero changes downloaded/uploaded) I am getting a 400 badrequest from the server?

    3. After this happened, I checked the SentAnchor value but still it is null. Isnt it supposed to be non-null if I had just finished a full bidirectional sync?

    4. Also, why do I need to specify the sync direction twice? (first when creating the SqlSyncAdapterBuilder and then once more when creating the sync agent(sa.Configuration.SyncTables)?

    5. Why Bidirectional seems to work (with tweaking, and only once) but none of the other methods seems to?

    6. If I deploy an empty database, am I supposed to initialize it by doing a snapshot or a bidireactional syn first?


    I appreciate your help and advice on this matter. Thanks a lot!




    C# Devlpr, new to Biztalk 2006 R2.
    Thursday, October 22, 2009 4:28 PM
  • 1. Anchors table actually reflects the change version, it is bigint numer, but for SyncAnchor defined inside MSF:

    [Serializable]
    public class SyncAnchor
    {
    // Fields
    private byte[] _anchor;
    private const int MaxSyncAnchorSize = 0x989680;

    // Methods
    public SyncAnchor();
    public SyncAnchor(byte[] anchor);
    public SyncAnchor(SyncAnchor other);
    public bool Equals(SyncAnchor other);
    public bool IsNull();
    private static void ValidateAnchor(byte[] anchor);

    // Properties
    [SuppressMessage("Microsoft.Performance", "CA1819:PropertiesShouldNotReturnArrays")]
    public byte[] Anchor { get; set; }
    }
    2. I am not sure your server setting, seems your server crashed at the end of sync.

    3. For the first time, you only download data from server, so SentAnchor value should be null, because you did not change any value on you client.

    4. Yes, you need to define twice as the one inside the CustomSqlSyncAdapterBuilder is for client to apply changes made on sever side, another one is used by server side. but you are right, this might be partial code redundency.

    5. you always only need to specify Bidirectional if you want your table behavior like that, as the first sync the default filter generated by MSF will be like this:

    e.g. for aspnet_Applications table:

    SelectIncrementalInsertsCommand:
    "IF @sync_initialized = 0 SELECT [ApplicationName], [LoweredApplicationName], [aspnet_Applications].[ApplicationId], [Description] FROM [aspnet_Applications] LEFT OUTER JOIN CHANGETABLE(CHANGES [aspnet_Applications], @sync_last_received_anchor) CT ON CT.[ApplicationId] = [aspnet_Applications].[ApplicationId] WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) ELSE  BEGIN SELECT [ApplicationName], [LoweredApplicationName], [aspnet_Applications].[ApplicationId], [Description] FROM [aspnet_Applications] JOIN CHANGETABLE(CHANGES [aspnet_Applications], @sync_last_received_anchor) CT ON CT.[ApplicationId] = [aspnet_Applications].[ApplicationId] WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION  <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'[aspnet_Applications]')) > @sync_last_received_anchor RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. To recover from this error, the client must reinitialize its local database and try again',16,3,N'[aspnet_Applications]')  END "

    SelectIncrementalUpdatesCommand:                          
     "IF @sync_initialized > 0  BEGIN SELECT [ApplicationName], [LoweredApplicationName], [aspnet_Applications].[ApplicationId], [Description] FROM [aspnet_Applications] JOIN CHANGETABLE(CHANGES [aspnet_Applications], @sync_last_received_anchor) CT ON CT.[ApplicationId] = [aspnet_Applications].[ApplicationId] WHERE (CT.SYS_CHANGE_OPERATION = 'U' AND CT.SYS_CHANGE_VERSION <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'[aspnet_Applications]')) > @sync_last_received_anchor RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. To recover from this error, the client must reinitialize its local database and try again',16,3,N'[aspnet_Applications]')  END "
                                                
    SelectIncrementalDeletesCommand:
     "IF @sync_initialized > 0  BEGIN SELECT CT.[ApplicationId] FROM CHANGETABLE(CHANGES [aspnet_Applications], @sync_last_received_anchor) CT WHERE (CT.SYS_CHANGE_OPERATION = 'D' AND CT.SYS_CHANGE_VERSION <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'[aspnet_Applications]')) > @sync_last_received_anchor RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. To recover from this error, the client must reinitialize its local database and try again',16,3,N'[aspnet_Applications]')  END "

    so you can notice, @sync_initialized parameter will be zero if you do the first sync, so that is why you only need to do:

      sa.Configuration.SyncTables.Add( "MyTable", TableCreationOption.UseExistingTableOrFail, Microsoft.Synchronization.Data.SyncDirection.Bidirectional );

    as the Snapshot is only for downloading refresh data every time from server.

    6. The above will answer this.
                                                 
    Thursday, October 22, 2009 11:41 PM
  • Thank a lot for your detailed response!

    as for #2, The error is due to the client trying to send back to the server the 1158 rows of the table in one shot. I activated diagnostics for wcf and found this out. Moreover, this is the consequence of a bug in my code, which I haven't found how to solve, and that consists on the following (already described in my original post):


    perform bidirectional sync for the first time: OK, get 1158 rows from server to client.
    perform bidirectional again
    Expected:
    -> 0 changes, since already made a bidirectional sync
    Actual:
    -> The client is trying to send the newly downloaded 1158 rows back to the server, but the WCF service rejects because the message is bigger than a maximum size.

    Now, the problem is not the size of the message but the fact the the client is reporting those 1158 in the GetChanges function, when it should not report anything.


    Any ideas are highly appreciated!



    C# Devlpr, new to Biztalk 2006 R2.
    Friday, October 23, 2009 5:27 PM
  • More results: I used SqlServer Profiler  in both the server and the client to help find an explanation to the problems I've described.

    For  Bidirecctional sync

    Conditions:
    Only one table to sync, two SQL servers 2008, one as server the other as client.

    Server:
    1158 rows.

    Client
    0 Rows

    Perform Bidirectional sync.

    Results on the server:

    Result of
    exec sp_executesql N'SELECT * FROM sys.change_tracking_tables WHERE object_id = object_id(@sync_table_name)',N'@sync_table_name nvarchar(15)',@sync_table_name=N'MyTable'

    object_id    is_track_columns_updated_on    min_valid_version    begin_version    cleanup_version
    1167343223    0    10    10    10


    select CHANGE_TRACKING_CURRENT_VERSION()
     = 10


    Now, for the client:
    Received 1185 records from the server, but even after successfuly writing them I noticed the following

    exec sp_executesql N'SELECT * FROM sys.change_tracking_tables WHERE object_id = object_id(@sync_table_name)',N'@sync_table_name nvarchar(15)',@sync_table_name=N'MyTable'
     
    produces

    object_id    is_track_columns_updated_on    min_valid_version    begin_version    cleanup_version
    1269579561    0    0    0    0

    Is that normal? isnt it supposed to be 10?

    My anchors table, after finishing, looks like

    select * from
    Anchors

    TableName    ReceivedAnchor    SentAnchor
    MyTable    10     NULL


    From this, I noticed that even though the Anchors table was correctly updated, somehow the sync info for my table was not updated. I cannot explain why this is happening.

    At this point at least I have the same data on both the server and the client, but if I choose to sync again (which should cause no changes) then the client tries to send all the contents of the table back to the server.

    By using the profiler I noticed that there was no query executed in the server before this happens, meaning, seems to me the client is doing this on its own. The I have an error in the web service, complaining because the mssage size is too big.

    I am using an improved version of the SqlExpressProvider from the sample and synchronization over the web service selecting Server only, because my client is not compact but full sql server.

    Any help on this matter is greatly appreciated!

    C# Devlpr, new to Biztalk 2006 R2.
    Friday, October 23, 2009 9:50 PM
  • the result you get are all correct, the only thing you need to check is the @sync_initialized parameter value in the SelectIncrementalInsertsCommand when you do the second sync, you can verify it via SQL Server profiler to see it is zero or not, if it is zero, that means it is the first sync, if you get zero when you do the second(subsequent) sync, that means something wrong with your client anchor get method. my one like this:

     public override SyncAnchor GetTableReceivedAnchor(string tableName)
            {
                var queryStr = "SELECT ReceivedAnchor FROM " + AnchorTableName + " WHERE TableName = '" + tableName + "'";
                IDbCommand receivedAnchorCom = new SqlCommand(queryStr);
                receivedAnchorCom.Connection = _dbSyncProvider.Connection;
                receivedAnchorCom.CommandType = CommandType.Text;
                receivedAnchorCom.Transaction = _transaction;

                object anchorVal = null;
                bool commandPassed = false;
                try
                {
                    BeginTransaction(null);
                    anchorVal = receivedAnchorCom.ExecuteScalar();
                    commandPassed = true;
                }
                finally
                {
                    receivedAnchorCom.Dispose();
                    EndTransaction(commandPassed, null);
                }

                if (anchorVal == null || anchorVal == System.DBNull.Value)
                    return new SyncAnchor();
                else
                    return new SyncAnchor((byte[])anchorVal);
            }

            /// <summary>
            /// Retrieves the last sent anchor from the 'anchor' metatable.
            /// </summary>
            /// <param name="tableName"> The name of the table for which we want the anchor. </param>
            /// <returns> A sync anchor object containing the last sent anchor. </returns>
            public override SyncAnchor GetTableSentAnchor(string tableName)
            {
                var queryStr = "SELECT SentAnchor FROM " + AnchorTableName + " WHERE TableName = '" + tableName + "'";
                IDbCommand sentAnchorCom = new SqlCommand(queryStr);
                sentAnchorCom.Connection = _dbSyncProvider.Connection;
                sentAnchorCom.CommandType = CommandType.Text;
                sentAnchorCom.Transaction = _transaction;
                object anchorVal = null;
                bool commandPassed = false;
                try
                {
                    BeginTransaction(null);
                    anchorVal = sentAnchorCom.ExecuteScalar();
                    commandPassed = true;
                }
                finally
                {
                    sentAnchorCom.Dispose();
                    EndTransaction(commandPassed, null);
                }

                return anchorVal == System.DBNull.Value ? new SyncAnchor() : new SyncAnchor((byte[])anchorVal);
            }

    Monday, October 26, 2009 3:48 AM
  • Hi Thanks for your response! My code is functionally the same as yours, but keep getting same bad behavior.

    I made a sample project which I can show and that suffers the same problems. Maybe if you could have a look to it you would spot quickly something wrong. I will appreciate it a lot.

    I was using MSF1.0 dlls , changed to MSF2.0 dlls (x64) and keep getting the client trying to echo back to the server.

    I am using MSF 2.0, VS2008, Windows7 64 bit.


    Thanks!









    C# Devlpr, new to Biztalk 2006 R2.
    Tuesday, October 27, 2009 4:37 PM
  • I had a look of your code, and made some changes to make it works, you can download it , and you may need to change connection strings, happy coding!
    Wednesday, October 28, 2009 12:57 AM
  • Thanks a lot! it works like a charm!

    In case the links are broken some day, the piece that I was missing was to initialize the client's SelectNewAnchorCommand, before performing the synchronization:

                    var selectNewAnchorCommand = new SqlCommand();
                    const 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 = conn;
                    clientProvider.SelectNewAnchorCommand = selectNewAnchorCommand;

    Thanks again!
    C# + BizTalk2009 Developer
    Wednesday, October 28, 2009 5:03 PM
  • You are more than welcome!
    Wednesday, October 28, 2009 11:52 PM