Data Replication with SQL Remote
PART 3. SQL Remote Administration
CHAPTER 9. Deploying and Synchronizing Databases
Thorough testing of your SQL Remote system should be carried out before deployment, especially if you have a large number of remote sites.
When you are in the design and setup phase, you can alter many facets of the SQL Remote setup. Altering publications, message types, writing triggers to resolve update conflicts are all easy to do.
Once you have deployed a SQL Remote application, the situation is different. A SQL Remote setup can be seen as a single dispersed database, spread out over many sites, maintaining a loose form of consistency. The data may never be in exactly the same state in all databases in the setup at once, but all data changes are replicated as complete transactions around the system over time. Consistency is built in to a SQL Remote setup through careful publication design, and through the reconciliation of UPDATE conflicts as they occur.
Once a SQL Remote setup is deployed and is running, it is not easy to tinker with. An upgrade to a SQL Remote installation needs to be carried out with the same care as an initial deployment. This applies also to upgrading maintenance releases of the Adaptive Server Enterprise or Adaptive Server Anywhere database software. Any such software upgrade needs to be tested for compatibility before deployment.
Making changes to a database schema at one database within the system can cause failures because of incompatible database objects. The passthrough mode does allow schema changes to be sent to some or all databases in a SQL Remote setup, but must still be used with care and planning.
The loose consistency in the dispersed database means that updates are always in progress: you cannot generally stop changes being made to all databases, make some changes to the database schema, and restart.
Without careful planning, changes to a database schema will produce errors throughout the installation, and will require all subscriptions to be stopped and resynchronized. Resynchronization involves loading new copies of the data in each remote database, and for more than a few subscribers is a time-consuming process involving work interruptions and possible loss of data.
Changes to avoid on a running systemThe following are examples of changes that should not be made to a deployed and running SQL Remote setup. From the list, you will see that there is a class of changes that are permissive, and these are generally permissible, while other changes are restrictive, and must be avoided.
The following changes must be avoided, except under the conditions stated:
Change the publisher for the consolidated database.
Make restrictive changes to tables, such as dropping a column or altering a column to not allow NULL values. Changes that include the column or including NULL entries may already be being sent in messages around the SQL Remote setup, and will fail.
Alter a publication. Publication definitions must be maintained at both local and remote sites, and changes that rely on the old publication definition may already be being sent in messages around the SQL Remote setup.
You can make permissive changes, such as adding a new table or column, as long as you use passthrough to ensure that the new table or column exists in the remote database and in the publication at the remote database.
Drop a subscription. This can be done only if you use passthrough deletes to remove the data at the remote site.
Unload and reload an Adaptive Server Anywhere database.
If an Adaptive Server Anywhere database is participating in replication, it cannot be unloaded and reloaded without re-synchronizing the database. Replication is based on the transaction log, and when a database is unloaded and reloaded, the old transaction log is no longer available. For this reason, good backup practices are especially important when participating in replication.
An Adaptive Server Enterprise database can be unloaded and reloaded as long as the system is quiet and the transaction log is fully scanned. The page_id and row_id rows in the sr_queue_state table of the stable queue must be reset.