Database Sync Configuration

SyncStudio has a feature that allows you to select which tables and fields will be synchronized. This information is saved into your Sync Project file. When you select the Database Configuration tab on the left-side menu you will see a list of the tables in the database where you can select which tables need to be synced by checking them off, as shown in the figure.By default, when you select a table all the fields will be synchronized. However, you can use the right-hand panel to define a sub-set of the fields.

DbAppSync_DatabaseConfig_1

Notes:

  • You cannot synchronize a table that does not have a primary key. This is a limitation of the Microsoft Sync Framework, which uses the fields in the primary key to uniquely identify a record. Tables that do not have a primary key will be shown highlighted in red in the SyncStudio and you will not be able to select them for synchronization. In any event, having a table without a primary key is probably not a good database design practice. Please note that having a unique index or a unique constraint is not the same thing as having a primary key—if you do not have a primary key the sync will fail, regardless of whether you have unique indexes or constrains or not.
  • You do not have to synchronize all the fields in a table, but you must synchronize all the fields that make up the primary key. Failure to follow this rule will cause the sync to fail. The same thing goes for any fields that are part of any Index that is being synchronized: you have to synchronize every field that is part of an index that is being synchronized, otherwise the client will not be able to create and maintain the index and the sync will fail.
  • Please keep in mind that fields that are not selected for synchronization will not be present in the client-side table. The client-side version of the tables will have only the fields than you select for synchronization in the SyncStudio. You client-side application needs to know which fields are present in the client-side version of your database.
  • Fields that are not selected for synchronization should always either allow null values or provide a default value for the field or the sync will fail. The reason is that whenever a new record is created at the client and uploaded to the server the client will not be providing values for fields that are not being synced. If you do not allow nulls and do not provide a default value for the field this will trigger a server-side error and abort the sync.
  • Client-side data types are not guaranteed to be exactly the same as their Server-side equivalents. The reason is that in client-side database systems such as Sqlite the set of supported data types will, in general, be very different from the MS SQL Server equivalent. SyncStudio will attempt to find the closest match for each field that is being synchronized, though. For example, all the Integer data types in MS SQL Server will be mapped to the Integer data type in Sqlite, regardless of the length of the server-side field; likewise, all the floating-point numeric data types in MS SQL Server will be mapped into the SQLite Real data type, and all character strings in SQL Server will become TEXT data types in SQLite.
  • Some special data types cannot be synchronized because of lack of support in the Microsoft Sync Framework. Some examples of data types that cannot be synchronized are the geography and geometry data types in SQL Server. You will not be able to select these fields in the UI. In any event, most of the client-side databases such as Sqlite simply have no equivalents for these special data types other than storing them as binary objects (blobs).
  • Foreign key relationship are also fully supported.
  • The Microsoft Sync Framework has a few things that Microsoft calls “infelicities”—which are exactly the same thing as bugs, just a little more dignified. These infelicities will affect the naming of your tables and fields. A table cannot have a field that contains the name of the table. For example, a table called “TYPE” cannot have any fields that begin with TYPE. The same thing applies for a primary index that contains the name of the table. Sorry.
  • Table names, Field names and Index names cannot contain any of the following special characters:~ ! @ # $ % ^ & * ( ) { } / ; ‘ ” | \Please note that some of these characters, $ for example, are perfectly valid in field names for many database systems, such as MS SQL Server. However, they cannot be used in table, index or field names that are intended for synchronization because of limitations in the architecture of the Microsoft Sync Framework. These are not exactly bugs, just infelicities.If you have used any of these characters in your database table schema or field names and you need the table synchronized then you really have only two choices: if you have control over the database structures you can always rename the table and/or fields to use only legal characters; otherwise, for tables you could create a “parallel” table with a valid table name, use a trigger to keep the two tables aligned and synchronize only the new table.You could also do something similar with invalid field names, where you create an additional field in the same table but with a valid name and then use a trigger to copy any changes from the invalid field to the new valid one. Regardless of the strategy used you need to keep in mind that this is a hard limitation, as invalid table and/or field names will cause your synchronization to fail. The SyncStudio user interface will try to prevent you from selecting tables and/or fields with invalid names whenever possible.
  • Tables can be marked as Upload-Only, Download-Only or Bi-Directional (both Upload and Download). The default is Bi-Directional, where any changes in the server will be sent to the client and vice-versa. Upload-Only tables are meant to be used to collect data from the clients but not to send or replicate any server data back to the client device.Download-Only tables are meant for data that should never be changed at the client.Some examples of common download-only tables are tax rates and tables with codes, which the client needs to use but should not be able to change. However, please do note that we do not have any way of preventing your client-side application from actually changing the contents of the records in a Download-Only table. What happens in this case is that the SyncStudio client will simply ignore the changes at the client—i.e., the changes will not be sent to the server.Likewise, you could manually add a new record or modify an existing record in an Upload-Only table at the server, but these changes will not be propagated back to the clients. For all the obvious reasons your client-side code should always be written so as to not allow any changes to download-only tables, to avoid confusion and the possibility of data-dependent errors that are very hard to reproduce and debug.
  • Any time that you make changes to either the database structure or to the tables and fields that need to be synchronized you must re-provision your database. There is a complete section of database provisioning later in this document. Please read it carefully, as the provisioning process has the potential for affecting both your application and your users.
  • For every Synchronization Project SyncStudio creates a binary file with a .dbp extension that contains all the information about your project configuration. The configuration file is not human-readable; please do not attempt to make any changes to this file outside of SyncStudio—otherwise your project will fail to load.