Working with Table Schemas (Sync Services)
This topic describes several ways in which you can work with the table schemas that are created in client and peer databases.
Client and Server Synchronization
During the initial synchronization session for a client, Sync Services creates tables in the client database. For more information, see How to: Initialize the Client Database and Work with Table Schema. After those tables are created, some schema changes are allowed.
Sync Services does not automatically propagate schema changes from the server to the client. For information about how to handle schema changes on the server, see Deploying and Versioning Applications (Sync Services). The following schema changes are supported in the client database:
Drop tables. The behavior during the next synchronization depends on the value that you specified for CreationOption. If you do not want the table on the client at all, remove it from the SyncTableCollection. When you drop a table that has change tracking enabled, you also drop the system table that tracks delete operations for that table. Therefore, any delete operations since the last synchronization are not uploaded to the server.
Add new columns.
Drop columns for tables that are involved in snapshot synchronization. This change is not supported for other types of synchronization.
Add, drop, and rename indexes.
Add, drop, and rename primary keys and other constraints.
All other changes are not supported and can cause synchronization failures.
In some applications, the schema is different at the client and server. The following points summarize the behavior for a table and the ADO.NET dataset that Sync Services uses to update that table at the client:
If the dataset contains Column1 and Column2, and the table contains Column1, Column2, and Column3: The values in Column1 and Column2 are applied, and Column3 is set to its default value or NULL if the column does not have a default.
If the dataset contains Column1, Column2, and Column3, and the table contains Column1 and Column2: The values in Column1 and Column2 are applied, and Column3 is ignored.
If the dataset contains Column1, Column2, and Column3, and the table contains Column1 and Column2, and Column4: The values in Column1 and Column2 are applied; Column3 is set to its default value or NULL if the column does not have a default; and Column4 is ignored.
Peer-to-peer synchronization does not provide automatic initialization of peer databases or create a change tracking infrastructure at each peer. For information about schema and data initialization, see "Initializing Each Database with Schema and Change Tracking Infrastructure" in How to: Configure Change Tracking and Synchronize Peers. There are no restrictions on schema changes to base tables in peer databases. However, applications must ensure that the commands that select and apply changes are updated if any schema changes occur. For example, if you add a column to a table, the command to insert changes into that table must be updated to include the new column.
In peer-to-peer scenarios, the schema might differ between peer databases. For example, a table schema at the first peer might use numerical column names that are not easy to read. You can use the DbSyncColumnMapping and DbSyncColumnMappingCollection objects to map the columns to more friendly names for other peers. For information about a sample application that demonstrates column mapping, see Sample Applications for Peer-to-Peer Synchronization.