User's Guide Part III. Using SQL Anywhere Chapter 28. SQL Remote AdministrationSQL 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:
Synchronizing a remote database can be done in the following ways:
You should not execute SYNCHRONIZE SUBSCRIPTION at a remote database.
The extraction utility can be accessed from Sybase Central, or as the DBXTRACT command-line utility.
The DBXTRACT command-line utility unloads a database schema and data suitable for building a remote database for a named subscriber. It produces a SQL command file with default name RELOAD.SQL and a set of data files. You can use this file to create a remote database:
To create a remote database from the reload file:
read path\RELOAD.SQL
where path is the path of the reload command file.
When used from Sybase Central, the extraction utility carries out the database unloading task, in the same way that DBXTRACT does, and then takes the additional step of creating the new database.
The extraction utility does not use a message system. The reload file (DBXTRACT) or database (from Sybase Central) is created in a directory accessible from the current machine. Synchronizing many subscriptions over a message link can produce heavy message traffic and, if the message system is not completely reliable, it may take some time for all the messages to be properly received at the remote sites.
You must complete the following tasks before using the extraction utility at a consolidated database.
For a description of how to carry out these steps, see the tutorial in the chapter "Introduction to SQL Remote Replication".
When you use the extraction utility to create a remote database, the user for which you are creating the database receives the same permissions they have in the consolidated database. Further, if the user is a member of any groups on the consolidated database, those group IDs are created in the remote database with the permissions they have in the consolidated database.
For full information on using the extraction utility from Sybase Central, see the Sybase Central online Help. This section describes one way to extract a database for a remote user from the current consolidated database.
To extract a database for a remote user:
For information on the DBXTRACT command-line options, see "The DBXTRACT command-line utility" and the following section.
For information about the extraction utility options, available as command-line options for DBXTRACT or as choices presented by the Database Extraction Wizard, see "Extraction utility options".
While the extraction utility is the recommended way of creating and synchronizing remote databases from a consolidated databases, there are some circumstances where it cannot be used, and you must synchronize remote databases manually. This section describes some of those cases.
Remote databases can have tables not present at their consolidated database as long as these tables do not take part in replication. Of course, the extraction utility cannot extract such tables from a consolidated database.
To understand the role of the extraction utility in multi-tiered arrangements, consider a three-tiered SQL Remote setup.
This setup is illustrated in the following diagram.
From the consolidated database at the top level, you can use the extraction utility to create the second-level databases. You can then add remote users to these second-level databases, and use the extraction utility from each second-level database to create the remote databases. However, if you have to reextract the second-level databases from the top-level consolidated database, you will delete the remote users that were created, along with their subscriptions and permissions, and will have to rebuild those users. The exception is if you resynchronize data only, in which case you can use the extraction utility to replace the data in the database, without replacing the schema.
A subscription is created at a consolidated database using the CREATE SUBSCRIPTION statement:
CREATE SUBSCRIPTIONTO [owner].pubname [ ( string ) ]FOR userlist
The CREATE SUBSCRIPTION statement defines the data to be received. It does not synchronize or start a subscription.
The SYNCHRONIZE SUBSCRIPTION statement causes the Message Agent (DBREMOTE) to send a copy of all rows in the subscription to the subscriber. It assumes that an appropriate database schema is in place.
The SYNCHRONIZE SUBSCRIPTION statement has the following syntax:
SYNCHRONIZE SUBSCRIPTIONTO [owner].pubname [ ( string ) ]FOR userlist
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. The syntax of the START SUBSCRIPTION statement is as follows:
START SUBSCRIPTIONTO [owner].pubname [ ( string ) ]FOR userlist
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, the SYNCHRONIZE SUBSCRIPTION statement 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 the DBUNLOAD utility before synchronizing the database. |
Synchronizing large numbers of subscriptions, or synchronizing subscriptions to large, frequently-used tables, can slow down database access for other users. You may wish to synchronize 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.