Microsoft Sync Framework - Local DB and Remote DB have to have the same schema?

Question

When using MSF, is it implied in the technology that the sync tables are supposed to be 1-1?

The reason I'm wondering is that if I'm synching from a SQL2005 database to a SQLCE, I might want the CE one to be a little more flattened out so I can get data out with a simpler SELECT statement (as CE does not support sprocs).

For example, I might have a tblCustomer, tblOrder, and tblCustomerOrder in the central database, but in the local databases one table with all the data might be preferred. Of course I'd still want the updates to reflect back and forth between the two databases. Does MSF make this possible, or does the local DB have to have the same tables as the central?

Answer 1

With Microsoft Sync Framework 2.0 there are two paradigms for the database sync providers. These are generally referred to in the documentation as Offline (Hub and Spoke) and Collaboration (Peer to Peer) scenarios - each comes with its own set of sync providers. How similar the schemata need to be depends on which scenario and by extension which providers you are using.

The Offline Scenario incorporates ADO.Net style adapters which allow a great deal of flexibility.

The Collaboration Scenario does not require the same number of fields but any that are included must have the same name on both sides. Further, any fields used in filters must exist as columns on both sides.

Answer 2

AS Scott Munro has already said, while you can slice and dice the data, the important fields have to be there on both sides.... Depending on the size of your data, what you might want to do is use a trigger on the server side to build the flatter table and then sync that...