Chapter 8. SQL Remote Design for Adaptive Server Enterprise

4.Replicate the change from the remote to the consolidated database, by running the Message Agent at the remote database to send the message, and then at the consolidated database to receive and apply the message.

5.At the consolidated database, view the Customer table and the ConflictLog table. The Customer table contains the value from the consolidated database:

cust_key

name

rep_key

 

 

 

cust1

Consolidated Sports

rep1

 

 

 

The ConflictLog table has a single row, showing the conflict and recording the value entered at the remote database:

conflict_key

lost_name

won_name

remote_user

 

 

 

 

1

Field Sports

Consolidated Sports

field_user

 

 

 

 

6.Run the Message Agent again at the remote database. This receives the corrected update from the consolidated database, so that the name of the customer is set to Consolidated Sports here as well.

Designing to avoid referential integrity errors

The tables in a relational database are related through foreign key references. The referential integrity constraints applied as a consequence of these references ensure that the database remains consistent. If you wish to replicate only a part of a database, there are potential problems with the referential integrity of the replicated database.

Referential integrity errors stop replication

If a remote database receives a message that includes a statement that cannot be executed because of referential integrity constraints, no further messages can be applied to the database (because they come after a message that has not yet been applied), including passthrough statements, which would sit in the message queue.

By paying attention to referential integrity issues while designing publications you can avoid these problems. This section describes some of the more common integrity problems and suggests ways to avoid them.

Unreplicated referenced Consider the following SalesRepData publication:

table errors

exec sp_add_remote_table ’SalesRep’

exec sp_create_publication ’SalesRepData’ exec sp_add_article ’SalesRepData’, ’SalesRep’ go

173

Page 191
Image 191
Sybase DC38133-01-0902-01 manual Designing to avoid referential integrity errors, 173