locked
Ideas on a model to remove client’s records that no longer meet a filter’s criteria RRS feed

  • Question

  • Setup: SQL Server 2008 (using Change Tracking); upgrading SFx 1.0 to 2.1; n-Tier WCF; PC clients; SqlCe

    This MSDN document http://msdn.microsoft.com/en-us/library/bb726003%28v=sql.110%29.aspxstates that: “Sync Framework does not provide automatic partition management” and offers as a solution that “you could develop a system that enables Sync Framework to download that update as a delete”. (Granted the doc is re: SFx 1.0 … but the issue applies to 2.x also)

     

    Any ideas on a model to accomplish this?

     

    Idea 1:

    The offered solution may work with database triggers that maintain a table of records to notify the client of records that need to be removed.

    Idea 2:

    My first thought is to call a method (external to the sync process – after the sync is complete) on the WCF service (n-tier) that returns a list of all PK values that the client should have … then delete the PKs that don’t exist in the returned dataset but do exist in the client’s database (of course keeping the deletes from flowing back to the server). This would have to be done for each sync’d table defined (maybe just the bi-directionally sync’d tables).

     

    My concern is mostly the size of the dataset that will be returned, but also in a smaller sense … the timing between when the sync processes the table and the time I call my method to return the PKs.

     

    Idea 3:

    My current app (using SFx 1.0) maintains a “subscription table” on the server of everything I send to the client … so I always know what is on the client and when it comes time to clean up the client, I can just send a list to the client of the records that need to be deleted … works great … only problem is that the table is ~6 gig and its index is ~6 gig … together they are more than half of my total database size!

    I may rework my subscription model to not subscribe to every record in every table that has been sent ... but to instead only subscribe to records in PK tables and have all FK tables follow.

    I would appreciate any other models that others have implemented ...

    Tuesday, May 31, 2011 10:27 PM

Answers

  • first of all the scenario you described above is not supported out of box. there were several posts discussing the similar scenario before. one of them is http://social.microsoft.com/Forums/en-US/syncdevdiscussions/thread/1f070b12-2e25-421e-90ab-2021087cb543.

    unfortunately there is no easy way to deal with the "move in "/"move out" records. a reinitialization to the client DB would be a suitable way to get a fresh data from the server. if for any reason this is hard to do ( such as huge data sync for it), then what you have implemented can be an alternative way. but the general recommended way is reinitialize the clients.

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by P H T Thursday, June 2, 2011 7:39 PM
    Thursday, June 2, 2011 3:14 AM

All replies

  • which provider are you using ? based on your setup, it seems it is the sqlceclient provider with the dbsyncprovider, but I want to confirm on this.

    #2 and #3 might work, however i think they didn't utlilze the filtering feature of sync and they don't follow the best prarice of filtering data to the clients.

    I would suggest

    1. always do the best to design the sync solution so that the data was filtered to the clients, the can minimze the set of data to be transfered and also restrict the clients to review unnecessary data

    2. if in cases where filters changes, we recommend to reinit the client to get the new set of filtered data.

    3. if "local deletes" that you don't want to flow back to the server, you can put the logic at the client side in the changesSelected event to remove the records.

     

    maybe you can described your scenarios with more details if the above suggestions cannot fit and we can discuss more on this.

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, June 1, 2011 2:17 PM
  • SQL Server 2008 (using Change Tracking)

    Sync Framework 2.1 (SqlCeSyncProvider & SqlSyncProvider)

    WCF n-Tier

    PC clients with SqlCe

     

    Here is an example of the “partitioning” referred to in the article I referenced:

    I create one client data file that is filtered for all records that belong to @UserID=100 and I create a second data file that is filtered for all records that belong to @UserID=200 (i.e. the dataset is partitioned into 2 logical groups by use of filtering).

    The problem arises when a record assigned to user 100 is moved to user 200 in the SQL database. When user 200 syncs, the existing filter picks up the “moved record” and it is inserted on the device as intended; however, when user 100 syncs, the filter does include the “moved record” … so it is not a part of the sync. The undesirable result for user 100 is that the “moved record” remains on user 100’s device as an orphaned record – this is because “Sync Framework does not provide automatic partition management” as the article explains.

     

    In reference to the 3 suggestions:

    1.  Yes, I am filtering

    2.  No, The filter is not changing

    3.  Yes, local deletes does refer to the client … and your suggestion is how I am handling that

     

    The question is: What are recommended ways of removing the orphaned records (or performing “partition management” described by the article) and a request for comments on the 3 scenarios I provided?

     

    Wednesday, June 1, 2011 2:59 PM
  • first of all the scenario you described above is not supported out of box. there were several posts discussing the similar scenario before. one of them is http://social.microsoft.com/Forums/en-US/syncdevdiscussions/thread/1f070b12-2e25-421e-90ab-2021087cb543.

    unfortunately there is no easy way to deal with the "move in "/"move out" records. a reinitialization to the client DB would be a suitable way to get a fresh data from the server. if for any reason this is hard to do ( such as huge data sync for it), then what you have implemented can be an alternative way. but the general recommended way is reinitialize the clients.

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by P H T Thursday, June 2, 2011 7:39 PM
    Thursday, June 2, 2011 3:14 AM