18 พฤศจิกายน 2552 23:01We are looking for a way to implement data partitiioning when doing a sync. Our current architecture is that we have one "master" sql server 2008 store and N SQL Server CE peers. Each CE peer is configured for a specific client and we want to use filters to specify the data that they should get.
So, if we have a Customer table, an Orders table and an OrderDetail table then is is clear how to filter the Customer and Orders table; they both would have "CustomerCode" in them. The problem is the OrderDetail table; it just has a foreign key to the Orders table in it so we cannot filter by CustomerCode.
A first thought is that we could sync with a view of the OrderDetails table; one that included the correct customer code. When I tried to provision the Master db specifying the view as a "table" to sync, this did not work. I really don't want to denormalize all our tables to make this work, and I would much rather work with one of the out-of-the-box Sync providers rather than roll our own if possible.
Thanks for your help!
25 พฤศจิกายน 2552 7:07เจ้าของ
25 พฤศจิกายน 2552 14:01Thanks for the reply Mahesh - yes, we are using MSF 2.0.
I am aware of the filtering functionality; that is what I am trying to use. The problem as I see it is that in a normalized database, not every table contains the correct filtering criteria that is needed to filter (please read my OrderDetail example above).
The options then seem to be that we need to denormalize our db a bit in order to have the filter column present in every table we want to filter and sync, or possibly we could achieve the same result without altering table structure by using views. This does not work when following the examples in the documentation:
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("myscope");
DbSyncTableDescription customerDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("ViewOfTableToSync", serverConn);
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
serverConfig.Tables["ViewOfTableToSync"].FilterClause = "[side].[Code] = '2'";
The error happens on the Apply: "The object 'ViewOfTableToSync' does not exist or is invalid for this operation."
So I have 2 questions:
1) is it even possible to use views instead of tables for syncing?
2) what is the suggested pattern to use to partition a normalized database?
25 พฤศจิกายน 2552 14:04I will also add that the above code works fine if "ViewOfTableToSync" is instead the real table instead of the view "TableToSync" (assuming the table in question is denormalized to have the filter column "Code"
25 พฤศจิกายน 2552 18:17เจ้าของWhat I was saying was that you can filter the Customer and Orders tables with the filter on CustomerCode column. Then for the OrderDetail table, why dont you put a join to the Order table in the filter clause.
This posting is provided AS IS with no warranties, and confers no rights
- ทำเครื่องหมายเป็นคำตอบโดย Mahesh DudgikarMicrosoft, Owner 28 พฤศจิกายน 2552 5:30
25 พฤศจิกายน 2552 21:21
Ok - i think I see what you are saying now. Thanks for your help!