Data Replication with SQL Remote
PART 3. SQL Remote Administration
CHAPTER 9. Deploying and Synchronizing Databases
SQL Remote replication is carried out using the information in the transaction log, but there are two circumstances where SQL Remote deletes all existing rows from those tables of a remote database that form part of a publication, and copies the publication's entire contents from the consolidated database to the remote site. This process is called synchronization.
Synchronization is used under the following circumstances:
When a subscription is created at a consolidated database a synchronization is carried out, so that the remote database starts off with a database in the same state as the consolidated database.
If a remote database gets corrupt or gets out of step with the consolidated database, and cannot be repaired using SQL passthrough mode, synchronization forces the remote site database back in step with the consolidated site.
Synchronizing a remote database can be done in the following ways:
Use the database extraction utility This utility creates a schema for a remote Adaptive Server Anywhere database, and synchronizes the remote database. This is generally the recommended procedure.
Manual synchronization Synchronize the remote database manually by loading from files, using the PowerBuilder pipeline, or some other tool.
Synchronize over the message system Synchronize the remote database via the message system using the SYNCHRONIZE SUBSCRIPTION statement (Adaptive Server Anywhere ) or sp_subscription 'synchronize' procedure (Adaptive Server Enterprise).
Caution
Do not execute SYNCHRONIZE SUBSCRIPTION or sp_subscription 'synchronize' at a remote database.
Mixed operating systems and database extractionIn many installations, the consolidated server will be running on a different operating system than the remote databases.
Adaptive Server Anywhere databases can be copied from one file or operating system to another. This allows you flexibility in how you carry out your initial synchronization of databases.
For example, you may be running an Adaptive Server Enterprise server on a UNIX system that holds the consolidated database, but wish to deploy remote databases on laptop computers running Windows 95.
In this circumstance, you have several options for the platforms on which you extract the database, including the following, assuming you have the requisite software:
Run the extraction utility on UNIX to create the reload script and data files. Copy the script and data files to a Windows 95 machine. Create the Adaptive Server Anywhere databases and load them up with the schema and data on Windows 95.
Run the extraction utility on UNIX to create the reload script and data files. Create the Adaptive Server Anywhere databases and load them up with the schema and data on the same UNIX platform, and then copy the database files onto Windows 95 machines for deployment.
Run the extraction utility on Windows 95, and carry out all database creation and other tasks on the Windows 95 operating system.
Notes on synchronization and extractionExtracting large numbers of subscriptions, or synchronizing subscriptions to large, frequently-used tables, can slow down database access for other users. You may wish to extract such subscriptions when the database is not in heavy use. This happens automatically if you use a SEND AT clause with a quiet time specified.
Synchronization applies to an entire subscription. There is currently no straightforward way of synchronizing a single table.
For Adaptive Server Enterprise, Sybase Central does not display subscriptions as started until the Message Agent first runs against the database.