This topic applies to both two-tier, and multi-tier, multi-level architectures. It assumes that you have created your catalogs and schema names (if required).
The following guidelines are for designing and implementing the schema and using the CREATE TABLE statement to create the master database and replica database tables.
You define tables that are required for synchronization and will be used in a publication. A publication is a set of data to be downloaded from the master database to a replica database. When creating your schema you need to define:
▪ Tables of the master database
▪ Tables of the replica database
▪ Replica databases can contain all tables of the master database or a subset of them.
▪ Replica databases can also contain tables that are for local use only.
▪ Replica tables can contain a subset of columns from the master table.
▪ The name of the replica table can be different from the master table. When publications are created by using the CREATE PUBLICATION statement, a master table name can be associated with a replica table that has a different name. The publication definition takes care of the mapping between the master and replica tables.
Remember the following rules when creating tables:
▪ All tables in the schema must have a user-defined primary key. The primary keys in master and replica tables must be identical and uniqueness must be guaranteed globally. More columns in a replica primary key will lead to conflicts in propagating transactions to the master database. More columns in a master primary key will similarly lead to conflict when refreshing data to a replica.
▪ Apply the ALTER TABLE SET SYNCHISTORY statement to enable incremental publications on the master and replica tables. Otherwise the master table sends the replica table full publications (which use more resources) rather than incremental publications.
By setting the SYNCHISTORY property for each table of the publication in both the master and the replica databases, you allow the creation of a shadow history table that keeps track of data updates for incremental publication. For details on the ALTER TABLE syntax, see Creating incremental publications.