Data Replication with SQL Remote
   PART 2. Replication Design for SQL Remote
     CHAPTER 7. SQL Remote Design for Adaptive Server Anywhere       

Publication design for Adaptive Server Anywhere

Once you understand how to create simple publications, you must think about proper publication design. Sound design is an important part of building a successful SQL Remote installation. This section helps set out the principles of sound design as they apply to SQL Remote for Adaptive Server Anywhere.

  Design issues overview

Each subscription must be a complete relational database 

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.

Transaction integrity must be maintained in the absence of locking 

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.

  Conditions for valid articles

All columns in the primary key must be included in the article.

Supporting INSERTS at remote databases 

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.

  Design tips for performance

This section presents a check list for designing high performance SQL Remote installations.

• Keep the number of publications small     In particular, try not to reference the same table in many different publications.

  The work the database server needs to do is proportional to the number of publications. Keeping the number low and making effective use of subscriptions lightens the load on the database server.

  When operations occur on a table, the database server and the Message Agent must do some work for each publication that contains the table. Having one publication for each remote user will drastically increase the load on the database server. It is much better to have a few publications that use SUBSCRIBE BY and have subscriptions for each remote user. The database server does no additional work when more subscriptions are added for a publication. The Message Agent is designed to work efficiently with a large number of subscriptions.

• Group publications logically     For example, if there is a table that every remote user requires, such as a price list table, make a separate publication for that table. Make one publication for each table where the data can be partitioned by a column value.

• Use subscriptions effectively     When remote users receive similar subsets of the consolidated database, always use publications that incorporate SUBSCRIBE BY expressions. Do not create a separate publication for each remote user.

• Pay attention to Update Publication Triggers     In particular:

  • Use the NEW / OLD SUBSCRIBE BY syntax.

  • Tune the SELECT statements to ensure they are accessing the database efficiently.

• Monitor the transaction log size     The larger the transaction log, the longer it takes the Message Agent to scan it. Rename the log regularly and use the DELETE_OLD_LOGS option.