Synchronizing data over a message system

Data Replication with SQL Remote
PART 3. SQL Remote Administration
CHAPTER 9. Deploying and Synchronizing Databases

Synchronizing data over a message system

Creating subscriptions

A subscription is created at a consolidated Adaptive Server Anywhere database using the sp_subscription procedure with a first argument of create.

Creating a subscription defines the data to be received. It does not synchronize a subscription (provide an initial copy of the data) or start (exchange messages) a subscription.

Synchronizing subscriptions

Synchronizing a subscription causes the Message Agent to send a copy of all rows in the subscription to the subscriber. It assumes that an appropriate database schema is in place. At an Adaptive Server Anywhere consolidated database, subscriptions are synchronized using the SYNCHRONIZE SUBSCRIPTION statement. At an Adaptive Server Enterprise consolidated database, subscriptions are synchronized using the sp_subscription procedure with a first argument of synchronize.

When synchronization messages are received at a subscriber database, the Message Agent replaces the current contents of the database with the new copy. Any data at the subscriber that is part of the subscription, and which has not been replicated to the consolidated database, is lost. Once synchronization is complete, the subscription is started by the Message Agent using the START SUBSCRIPTION statement or sp_subscription procedure with a first argument of start.

Large volume of messages may result
Synchronizing databases over a message system may lead to large volumes of messages. In many cases, it is preferable to use the extraction process to synchronize a database locally without placing this burden on the message system.

Synchronizing subscriptions during operation

If a remote database becomes out of step with the consolidated database, and cannot be brought back in step using the SQL passthrough capabilities of SQL Remote, synchronizing the subscription forces the remote database into step with the consolidated database by copying the rows of the subscription from the consolidated database over the contents at the remote database.

Data loss on synchronization
Any data in the remote database that is part of the subscription, but which has not been replicated to the consolidated database, is lost when the subscription is synchronized. You may wish to unload or back up the remote database using Sybase Central or, for Adaptive Server Anywhere, the dbunload utility before synchronizing the database.