Sync Services: When should you author your own table schema for your offline clients?

The question of creating the schema on the client store always pops up in almost any sync related conversation. Flexibility is desired but simplicity is a must! I can understand that and I believe that the sync framework has a good balance between both (feel free to tell me otherwise)…

In one of my pervious posts I touched on schema creation logic that is implemented by the DbServerSyncProvider today. Automatic generation of the schema based on the SelectIncrementInserts\Updates command provides the ‘simplicity’ aspect, while authoring the SyncSchema object and pass it to the provider reflects the ‘flexibility’ aspect of the framework. Now, I want to talk a little bit more about when you would want to author you own SyncSchema object:

Custom Schema Scenarios

• Incomplete automatic schema creation
  In some cases, the schema generated using the select command is not complete. This typically the case when ADO.NET provider for your backend database is unable to get needed schema information. For example, the Oracle ADO.NET provider does not return the precision and scale information for numeric columns. See for yourself how I addressed this in Oracle demo. 
• DataType mapping
  Different database brands define different datatypes.Now, if you are using SqlCe on your offline clients, then you must somehow map forigen data types to those that SqlCe understands. You do that through SyncSchema.
• Customizing the schema for each client
  If you decided to implement some sort of identity ranges for your client such that each client new inserts will generate different identity value that is garanteeded to be unique, then you would need to customize the schema for each client. I should not that unlike SQL Server Merge Replication, sync services does not define a way to get a new range once the existing one expires. But anyways, you get the idea; if you need to pass some extra info for each client during schema creation you could author the schema yourself, or intercept the auto generated schema and edit it prior to passing it to the client.
• Custom server provider
  If you don’t have access to the relational store directly (for example, you have to use API or web service to get to the data) or you have non-relational store and thus cannot use ADO.NET; you would need to implement a custom server provider. The only way to describe the schema for your clients in this case is through SyncSchema object.

I should note that in the case you know the schema in advance and you are developing the client app too, then you have the option of just creating the tables on the client database when you app is setup or even ship the app with a SqlCe file that has the initial content which could include the schema along with some initial data or just the schema..

This is a very rich topic and I think I will talk about it some more later on, stay tuned …

 

Update: Just to let you know, I left Microsoft to start a new company, Raveable Hotel Reviews. See examples: Romantic Hotels in Myrtle Beach , Best Hotels in Seattle , Top 10 Hotels in Miami , Kid friendly hotels in San Diego , Hotels with a kitchen or kitchenette and 5 Star Hotels in Las Vegas . Your feedback is welcome ontwitter.com/raveable, raveable blog.