*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
|
Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
| [Home] | [Table of Contents] | [Previous Section] | [Next Section] |
Breck Carter
Last modified: February 23, 1998
mail to: bcarter@bcarter.com
At some point it may be necessary to force a remote database into step with the consolidated database. The crudest, fastest and simplest way is to rerun DBXTRACT, DBINIT and ISQL (or DBINITW and ISQLW) to replace the REMOTE.DB and REMOTE.LOG files entirely.
A more sophisticated method, albeit slower and more complex, is to run SYNCHRONIZE SUBSCRIPTION statements via ISQL and then DBREMOTE and DBREMOTW to perform the actual synchronization.
Both methods share this danger: updates made to the remote database will be lost forever if they haven't been replicated back to the consolidated database yet.
The crude, fast and simple DBXTRACT method is simply a repeat of earlier steps. It is, in fact, the recommended method because of its speed and simplicity.
This section presents the other method, the more sophisticated SYNCHRONIZE SUBSCRIPTION technique.
Step 42 - Run "Update E" on the consolidated database (required).
This command is run on the central server to execute the INSERTs shown in Figure 42a:
ISQL -c "DBN=consol;UID=DBA;PWD=SQL" READ c:\test\update_e.sql
Figure 42a - UPDATE_E.SQL - Yet More New Rows For The Consolidated Database
(This file is contained in rep.zip.)
INSERT INTO replication1 ( subscription_id1, numeric_data1, string_data1 ) VALUES ( 'Main', 0, 'Update E' ); INSERT INTO replication2 ( subscription_id2, numeric_data2, string_data2 ) VALUES ( 'Sub1', 0, 'Update E' ); INSERT INTO replication2 ( subscription_id2, numeric_data2, string_data2 ) VALUES ( 'Sub2', 0, 'Update E' ); INSERT INTO replication3 ( subscription_id3, numeric_data3, string_data3 ) VALUES ( 'Sub1', 0, 'Update E' ); INSERT INTO replication3 ( subscription_id3, numeric_data3, string_data3 ) VALUES ( 'Sub2', 0, 'Update E' ); |
Step 43 - Make a backup: MAKECBAK 43 and MAKERBAK 43 (optional).
Step 44 - Show the replication status after "Update E" on the consolidated database (optional).
Figure 44a shows that the "Update D" rows are missing from the consolidated database and the "Update E" rows are missing from the remote database.
Figure 44a - Replication Status After "Update E" On The Consolidated Database
Step 45 - Run "Update F" on the remote database (required).
This command is run on the remote computer to execute the INSERTs shown in Figure 45a:
ISQLW -c "DBF=c:\test\remote.db;UID=DBA;PWD=SQL" READ c:\test\update_f.sql
Now the remote database is even more out of step because the "Update F" rows are missing from the consolidated database.
Figure 45a - UPDATE_F.SQL - Yet More New Rows For The Remote Database
(This file is contained in rep.zip.)
INSERT INTO replication2 ( subscription_id2, numeric_data2, string_data2 ) VALUES ( 'Sub1', 0, 'Update F' ); INSERT INTO replication3 ( subscription_id3, numeric_data3, string_data3 ) VALUES ( 'Sub1', 0, 'Update F' ); |
Step 46 - Make a backup: MAKECBAK 46 and MAKERBAK 46 (optional).
Step 47 - Show the replication status after "Update F" on the remote database (optional).
Figure 47a shows that now the "Update E" rows are missing from the remote database and the "Update D" and "Update F" rows are missing from the consolidated database.
Figure 47a - Replication Status After "Update F" On The Remote Database
Step 48 - Run "Synch 0001" SYNCHRONIZE commands on the consolidated database (required).
This command is run on the central server to execute the SYNCHRONIZE SUBSCRIPTION commands for SITE0001 shown in Figure 48a:
ISQL -c "DBN=consol;UID=DBA;PWD=SQL" READ c:\test\sync0001.sql
Figure 48a - SYNC0001.SQL - Synchronize Subscriptions For SITE0001
(This file is contained in rep.zip.)
SYNCHRONIZE SUBSCRIPTION TO pub1 FOR site0001; SYNCHRONIZE SUBSCRIPTION TO pub2_sub1 FOR site0001; SYNCHRONIZE SUBSCRIPTION TO pub3 ( 'Sub1' ) FOR site0001; |
The SYNCHRONIZE SUBSCRIPTION command doesn't actually have any effect on the remote database until DBREMOTE and DBREMOTW are run to send the messages and put the synchronization into effect. For that reason it should be thought of as a "FLAG SUBSCRIPTION FOR SYNCHRONIZATION" command.
Step 49 - Make a backup: MAKECBAK 49 and MAKERBAK 49 (optional).
Step 50 - Delete the old DBREMOTE output files from the central server (suggested).
This command is run on the central server to delete the old DBREMOTE output files that have already been sent to the remote computer:
rem This command only shows one remote computer site0001
erase c:\test\site0001\*.*
Figure 50a shows that all the replication subdirectories on the central server are empty.
Figure 50a - Files On The Central Server Before The "Synch 0001 & Update E" DBREMOTE
Directory of c:\test\consol ...empty Directory of c:\test\site0001 ...empty |
This command runs DBREMOTE on the central server to gather the SYNCHRONIZE SUBSCRIPTION data for SITE0001 and the "Update E" changes for other remote computers:
c:\test\consremo.bat
Figure 51a shows that two large files have been added to SITE0001 subdirectory to contain the SYNCHRONIZE SUBSCRIPTION data. This data includes the "Update E" changes as part of the full synchronization of all three subscriptions for SITE0001.
Figure 51a - Files On The Central Server After The "Synch 0001 & Update E" DBREMOTE
Directory of c:\test\consol ...empty Directory of c:\test\site0001 consol b 49,959 97 11 16 10:52a consol c 46,760 97 11 16 10:52a |
Step 52 - Make a backup: MAKECBAK 52 and MAKERBAK 52 (optional).
Step 53 - Send the "Synch 0001" files to the remote computer (required).
This command is run on the central server to copy the "SYNCHRONIZE SUBSCRIPTION" files (which include "Update E") to the first remote computer:
rem This command only shows one remote computer site0001
copy c:\test\site0001\consol.* f:\test\site0001
Step 54 - Delete the old DBREMOTW output files from the remote computer (suggested).
This command is run on the remote computer to delete the old DBREMOTW output files that have already been sent to the central server:
erase c:\test\consol\*.*
Figure 54a shows that the "Synch 0001" files are ready to be processed and the output subdirectory is empty and waiting.
Figure 54a - Files On The Remote Computer Before The "Synch 0001" DBREMOTW
DIRECTORY OF C:\TEST\CONSOL ...empty DIRECTORY OF C:\TEST\SITE0001 CONSOL B 49,959 11-16-97 10:52a CONSOL C 46,760 11-16-97 10:52a |
Step 55 - Run DBREMOTW on the remote database to apply the "Synch 0001" data (required).
This command runs DBREMOTW on the first remote computer to apply the "SYNCHRONIZE SUBSCRIPTION" data (which includes "Update E") from the central server:
DBREMOTW -c "DBF=c:\test\remote.db;UID=DBA;PWD=SQL" -o c:\test\dbremotw.txt
After DBREMOTW runs the SITE0001 subdirectory is once again empty and Figure 55a shows that another new file has been created in the CONSOL subdirectory.
Figure 55a - Files On The Remote Computer After The "Synch 0001" DBREMOTW
DIRECTORY OF C:\TEST\CONSOL SITE0001 2 380 11-16-97 11:00a DIRECTORY OF C:\TEST\SITE0001 ...empty |
Step 56 - Make a backup: MAKECBAK 56 and MAKERBAK 56 (optional).
Step 57 - Show the replication status after the "Synch 0001" DBREMOTW (optional).
Figure 57a shows that the remote site 0001 database has been forced into step with the consolidated database by removing the rows marked "Update D" and "F" from the remote database and adding the rows marked "Update E". This demonstrates the primary danger of on-the-fly synchronization using either DBXTRACT or SYNCHRONIZE SUBSCRIPTION commands: unreplicated updates to remote databases are lost forever.
Figure 57a - Replication Status After The "Synch 0001" DBREMOTW
This is the end of Section Four.
| [Home] | [Table of Contents] | [Previous Section] | [Next Section] |