How to sync only certain rows in a database

Question

I am trying to sync a couple of tables from a central (sql-server) database dbA to a local (sql-ce) client database dbB. To this I'm planning to use the Microsoft sync framework. And I have managed to get it working so far as to sync all the rows (or updated rows) in some specified tables.

However, my dbA contains very much data, and it is also growing, so I don't want the customers to have a full copy of the dbA in their local databases. Instead I just want them to have some parts of the database (some rows that depends on some expression, for instance from tableA just select rows where moduleId = 4)

So initially I thought that filters seemed to be the thing for me. Specifically parameter based filters where I can specify a parameter to the filter. However after some investigation it appears that when creating a parameter based filter, you are actually just creating a template, and then creating different scopes from that.

I can't describe exactly what my application does, but you could think of it almost as a version handling system, and the parameter would represent what version I want to select from. So you can see that it would become a large number of scopes.

So in the end, what are my options?

Answer 1

Could you maybe use a SQL function for the filter-parameter so this can change?

Something like

serverTemplate.Tables["tableA"].AddFilterColumn("ModuleId");
serverTemplate.Tables["tableA"].FilterClause = "[side].[ModuleId] = sf_GetCurrentModule()";

You could create a SQL function sf_GetCurrentModule() to return the ModuleId to be synched. If the rows to be synched vary from client to client, you would at least need a synch scope per client - which is not a problem, just use an unique name for the scope, and maybe pass an id from the client to the function:

serverTemplate.Tables["tableA"].FilterClause = string.Format("[side].[ModuleId] = sf_GetCurrentModule({0})", ClientId);

So you could return the ModuleId to be synched depending on the client.

Hope this helps.

Travis