Share via

Sync Services: How to partition data for your offline clients?

  • Article

Offline clients are not born equal; one client would like to sync a lot of server data while another client might just wish for its small share of rows that pertains to the task at hand. Sync services framework recognizes the need for data partitioning and so in this post I’ll try to give some details on how to do that. Let’s start by identifying two type of partitioning: vertical and horizontal.
 
Vertical Partitioning
 
In vertical partitioning a sub set of the columns is synchronized with the client application. So let’s say you have a table with 20 columns and you decided to select only 5 columns for synchronization purposes. That can simply be achieved on the SyncAdapter commands for that table. Note that for bidirectional sync, columns that are not synchronized need to have either a default value or the insert and delete commands would set the value for those columns using some custom logic.
 
To change the horizontal partitioning for different clients, your commands could find what type of client is synchronizing (manager, employee, etc) and branch to different column selection statement. 
 
 
Horizontal Partitioning
 
This is the more interesting one; let’s say that each sales person is assigned to a given state. In this case, sales person in state of CA would be interested to get the customer information located on that state and not the rest. This is also known as filtering. There are multiple ways to implement that:

  1. The assignment information is already known to the server, then the adapter commands can get the state value first for the synchronizing client and use it in the where clause of the select incremental commands.
  2. The client can pass the filter value to the server directly, this can be done using the CustomParameter collection on the SyncAgent interface. The parameter value is then sent to the server during the sync session and can be used directly in the where clause to partition the data.

There are few interesting issues in this scenario though:

  • What if a customer relocated from CA to OR, how can that be reflected on the sales person overseeing CA? This goes back to the tracking infrastructure that you have on the server, your triggers can catch such update to the customer and record it such that the next time CA sales guy synchronizes he would see a delete for that customer info. The OR client won’t have any problem when the customer row surfaces in his partition.
  • What if the sales person changes his coverage area from CA to OR? Well, the client application needs to reset its anchors and data, this can happen by simply deleting the database and let the client synchronizes again.

This was just a quick overview; I am sure many of sync developers would need to do something along these same lines. Give it a try and post all your questions to the public forum, my blog, or email. Have fun!

 

Update: Just to let you know, I left Microsoft to start a new company, Raveable Hotel Reviews. See examples: Romantic Hotels in NYC , Best Hotels in Seattle , Top 10 Hotels in Myrtle Beach , Kid Friendly Hotels in Chicago , Hotels with in-room jacuzziand Best Hotels in Miami. Your feedback is welcome on twitter.com/raveable, raveable blog.

Comments

  • Anonymous
    January 05, 2009
    Can you give a simple example of how the client can pass the filter value to the server using the customparameter collection on the syncagent? I'm trying to do the same thing but I can't find any example code. I'm using the latest sync framework with sql server 2008.