none
Is it possible to instruct Sync FX to ignore certain columns? RRS feed

  • Question

  • Ok so here is our setup, the PK for all of our tables are GUIDs.  Obviously GUIDs generated on multiple clients will not be sequential with each other when uploaded to the corporate server.  So obviously this throws off the benefits of having a clustered index on the GUID PK field.  So to circumvent this we decided we would create a secondary Identity field (let's call it ClusterID), remove the clustered index from the GUID PK field, and put the clustered index on the new ClusterID field.  Our thinking is that the server will auto-increment the ClusterID for every new insert and ClusterID and the clustered index will live happily ever after.

    Now from what I've read so far it seems that when the Sync FX attempts an upload it's going to try and copy the clients ClusterID field and sync it to corporate.  However that's not what we desire.  The preferred action would be to instruct the sync to ignore the ClusterID field and allow the corporate server to auto-increment the new value for ClusterID when the record gets inserted during the sync.  Is that possible?

     

    Thursday, September 30, 2010 4:28 PM

Answers

  • if you're doing UploadOnly sync, quickest workaround is to modify the insert stored proc to not insert the column. if you're doing BiDirectional, when the row is updated on the server and Sync Fx tries to apply the row on the client, it will not find a matching ClusterId since the original ClusterId from the client was ignored and a new id was generated on the server.
    • Marked as answer by Bo416 Thursday, September 30, 2010 9:46 PM
    Thursday, September 30, 2010 4:44 PM
    Moderator
  • Since your client DB is SQL Express, SyncFX peer provider would work for you (http://msdn.microsoft.com/en-us/library/ff928700(v=SQL.110).aspx). When you define the sync scope, you could skip the ClusterID column and this should serve your purpose.

     

    Thanks,


    Ann Tang
    Thursday, September 30, 2010 9:15 PM

All replies

  • if you're doing UploadOnly sync, quickest workaround is to modify the insert stored proc to not insert the column. if you're doing BiDirectional, when the row is updated on the server and Sync Fx tries to apply the row on the client, it will not find a matching ClusterId since the original ClusterId from the client was ignored and a new id was generated on the server.
    • Marked as answer by Bo416 Thursday, September 30, 2010 9:46 PM
    Thursday, September 30, 2010 4:44 PM
    Moderator
  • Regarding the BiDirectional scenario, you're right but wouldn't we want the Sync to ignore ClusterID field in both directions for both inserts and updates?  Reason I'm thinking this way is because every table in the database still has it's own separate GUID column as it's PK.  ClusterID would just be a secondary column that will solely exist to play nicely with the clustered index - it is not the primary key.  Is that sound thinking?  Please correct me if there is something I'm overlooking.

     

    Also, I noticed I just found the "How to: Filter Rows and Columns" article.  In this article I found the code below.  Is this approach applicable to the scenario we're discussing here.  Thanks again!

          //Specify the columns that you want at the client. If you
          //want all columns, this code is not required. In this
          //case, we filter out SalesPerson.
          string[] customerDataColumns = new string[3];
          customerDataColumns[0] = "CustomerId";
          customerDataColumns[1] = "CustomerName";
          customerDataColumns[2] = "CustomerType";
          customerBuilder.DataColumns.AddRange(customerDataColumns);
          customerBuilder.TombstoneDataColumns.AddRange(customerDataColumns);
    
    

     

     

    Thursday, September 30, 2010 5:15 PM
  • Also I just noticed that the example code in the "How to: Filter Rows and Columns" article has a using statement for System.Data.SqlServerCe.  Does the example code in this article only apply to CE databases?  Our laptops will be using Express not CE - can these techniques be used against Express databases?

    Thursday, September 30, 2010 5:44 PM
  • Since your client DB is SQL Express, SyncFX peer provider would work for you (http://msdn.microsoft.com/en-us/library/ff928700(v=SQL.110).aspx). When you define the sync scope, you could skip the ClusterID column and this should serve your purpose.

     

    Thanks,


    Ann Tang
    Thursday, September 30, 2010 9:15 PM
  • Thank you June and Ann, your responses have been very helpful.  The framework is a little daunting in trying to figure out the correct approach to take and I really do appreciate your advice and guidance.  Ann, those tutorials really seem like they will be helpful in getting us up to speed, thanks for pointing them out!
    Thursday, September 30, 2010 9:46 PM