You can provision and de-provision a database as often as you want. However, this is not something that you want to do often! The reason is that when you de-provision a database it loses any memory of the state of the clients. That means that when the clients sync after a de-provision/re-provision cycle SyncStudio must send them all the records again, as it does not know which records need to be synced. Depending on the size of your database, this might take quite some time and use much bandwidth. Essentially, de-provisioning and re-provisioning a database is something that should only be done when there are changes to the database structure.

Tip: some developers add a number of “reserved” fields and even whole tables to their database structures on the first release of their product, set them to synchronize but leave these fields blank or null. So long as you do not change the field names, lengths or data types you can then use them later for enhancements, new features, etc. without having to de-provision and re-provision the database. Considering the possible disruption to the users caused by the de-provisioning of a large database this may be a good alternative for you to consider.

If you change the schema of your database (i.e., if you add, change or delete tables, fields or indexes) you must de-provision and re-provision the server, but only if the changes apply to tables that are being synchronized. Adding, changing or deleting tables that are not being synchronized (and which you do not need to synchronize) does not require a de-provision/re-provision cycle.

If you change the tables and/or fields that are being synchronized you have to de-provision and re-provision the database—even if the database structure itself has not changed—and you must re-generate and re-deploy the server-side code. Failure to follow this rule will almost certainly break the synchronization process, as the database structures will no longer correspond to the server-side code.

Your data should still be protected because of the full transaction support in SyncStudio, but you are running the risk that some synchronization sessions may not fail even if the database structures have changed, because of data-dependent factors. To repeat: if you change the database structures or if you change which fields and/or tables are being synchronized you have to de-provision, re-provision, re-generate and re-deploy the server side code.

When you de-provision, change the structure of your database and then re-provision the database using the new database schema you do not have to manually make the same modifications on the client side. SyncStudio has an Automated Schema Synchronization feature that will take care of this for you, providing that the changes that you made to the database schema do not break the synchronization. There is a more complete description of which changes will break synchronization in the next section below.

The current version of SyncStudio does not support Foreign Keys. This is a limitation of the Microsoft Sync Framework, which does not support Foreign Keys either. We will try to lift this limitation in future releases, but for now if you have foreign keys in your database you have to modify your data structures so as to eliminate all foreign keys—otherwise it is almost certain that you will experience sync failures, because the order of uploading of the tables might cause the foreign-key table—i.e., the “parent” table that contains the foreign keys—to be sent to the server during an upload after the “child” table that references it. When this happens your data will not be corrupted, as we operate under a full session-level transaction, but the sync will fail.

In MS SQL Server you will notice that you cannot make changes to the database structures when a table is provisioned for synchronization. This means that in MS SQL Server you have to de-provision, exit the SyncStudio, make whatever changes are required to your database schema, then re-open the SyncStudio, verify the configuration to make sure that the appropriate tables and fields are being synced, re-provision the database, re-generate the code and re-deploy.

We are aware that some developers do try to manually change the table structures and synchronization-related stored procedures on the server side to avoid having to de-provision a database for minor changes. In our opinion this is such a dangerous process and so prone to failure that we cannot recommend it. SyncStudio has been designed with a Database Schema Synchronization feature precisely to simplify the task of upgrading your database to support new fields and features in your code, but the architecture of the Microsoft Sync Framework basically requires us to force the de-provisioning and re-provisioning of the database, and also forces us to re-send all the records to the client after a de-provisioning.

Please note that we will not provide support for problems in a database in which the synchronization data structures and/or stored procedures have been manually altered—in this case we will simply instruct you to de-provision, re-provision and re-try the sync!