Data Replication with SQL Remote
PART 2. Replication Design for SQL Remote
CHAPTER 8. SQL Remote Design for Adaptive Server Enterprise
Once you understand how to create simple publications, you must think about proper design of publications. This section describes the issues involved in designing publications, and how to take steps towards sound design.
A remote database shares with the consolidated database the information in their subscriptions. The subscription is both a subset of the relational database held at the consolidated site, and also a complete relational database at the remote site. The information in the subscription is therefore subject to the same rules as any other relational database:
Foreign key relationships must be valid For every entry in a foreign key, a corresponding primary key entry must exist in the database.
The database extraction utility ensures that the CREATE TABLE statements for remote databases do not have foreign keys defined to tables that do not exist remotely.
Primary key uniqueness must be maintained There is no way of checking what new rows have been entered at other sites, but not yet replicated. The design must prevent users at different sites adding rows with identical primary key values, as this would lead to conflicts when the rows are replicated to the consolidated database.
The data in the dispersed database (which consists of the consolidated database and all remote databases) must maintain its integrity in the face of updates at all sites, even though there is no system-wide locking mechanism for any particular row.
Locking conflicts must be prevented or resolved In a SQL Remote installation, there is no method for locking rows across all databases to prevent different users from altering the rows at the same time. Such conflicts must be prevented by designing them out of the system or must be resolved in an appropriate manner at the consolidated database.
These key features of relational databases must be incorporated into the design of your publications and subscriptions. This section describes principles and techniques for sound design.
All columns in the primary key must be included in the article.
For INSERT statements at a remote database to replicate correctly to the consolidated database, you can exclude from an article only columns that can be left out of a valid INSERT statement. These are:
Columns that allow NULL.
Columns that have defaults.
If you exclude any column that does not satisfy one of these requirements, INSERT statements carried out at a remote database will fail when replicated to the consolidated database.
There are two ways of including only some of the rows in a publication:
WHERE clause You can use a WHERE clause to include a subset of rows in an article. All subscribers to the publication containing this article receive the rows that satisfy the WHERE clause.
In SQL Remote for Adaptive Server Enterprise, the only supported WHERE clause is
WHERE column-name IS NOT NULL
Subscription columns You can use a subscription column to include a different set of rows in different subscriptions to publications containing the article.
For more information on restrictions on rows, see Creating articles containing some of the rows in a table.