Designing a database table for synchronization

Advanced Replication Guide : Getting started with data synchronization - evaluation setup : Preparing to use the advanced replication evaluation sample : Designing a database table for synchronization

The typical requirements for a database table used in a synchronized application are:

▪It must have unique primary key to ensure global uniqueness of rows.

▪It may need to have a row status column to provide a means for handling update conflicts.

▪It should include a version number or the time of the last update to allow detection of update conflicts.

For more information, see Defining a database table.

The evaluation sample creates a table that meets the above mentioned criteria, containing the following columns:

▪REPLICAID contains a unique identification for the database. In the sample script, the value 1 is reserved for the replica and value 2 for the master.

▪ID is a row identifier that is unique inside the database where the row is created.

▪Status has the following values: -1 for updates invalidated by master, 1 for tentative replica data and 2 for official master data.

▪INTDATA and TEXTDATA demonstrate the "business data" of the table.

Setting the SYNCHISTORY property on for the table enables incremental publication, which means that only the modified rows are transferred from the master to the replica when synchronizing the table data. Setting the SYNCHISTORY property creates a "shadow table" for the SYNCDEMO main table. The name of the "shadow table" is the name of the main table with "_SYNCHIST_" prefix. Old versions of updated and deleted rows are moved to this shadow table. SYNCHISTORY must be active in both the master and the replica databases.

As with any table to be synchronized, the table must be defined in both the master and replica databases.