*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
Section Three shows how ordinary updates are replicated back and forth between the consolidated and remote databases via DBREMOTE and DBREMOTW.
Step 14 - Run "Update A" on the consolidated database (required).
This is the first in a series of simulated application program updates to the databases using ISQL and ISQLW. The following command will load some rows marked "Update A" on to the consolidated database by running the SQL script shown in Figure 14a:
ISQL -c "DBN=consol;UID=DBA;PWD=SQL" READ c:\test\update_a.sql
Now the consolidated database is out of synch with the remote databases.
Figure 14a - UPDATE_A.SQL - Some New Rows For The Consolidated Database
(This file is contained in rep.zip.)
BEGIN
DECLARE i INTEGER;
SET i = 1;
WHILE i <= 1000 LOOP
INSERT INTO replication1 ( subscription_id1, numeric_data1, string_data1 )
VALUES ( 'Main', i, 'Update A' );
INSERT INTO replication2 ( subscription_id2, numeric_data2, string_data2 )
VALUES ( 'Sub1', i, 'Update A' );
INSERT INTO replication2 ( subscription_id2, numeric_data2, string_data2 )
VALUES ( 'Sub2', i, 'Update A' );
INSERT INTO replication3 ( subscription_id3, numeric_data3, string_data3 )
VALUES ( 'Sub1', i, 'Update A' );
INSERT INTO replication3 ( subscription_id3, numeric_data3, string_data3 )
VALUES ( 'Sub2', i, 'Update A' );
SET i = i + 1;
END LOOP;
END;
|
Step 15 - Make a backup: MAKECBAK 15 and MAKERBAK 15 (optional).
Step 16 - Show the replication status after Update A (optional).
Figure 16a shows that the databases are out of step because the consolidated database now has rows marked "Update A" while the remote database only has rows marked "initial test".
Figure 16a - Replication Status After Update A
The sysremoteusers data shows no change: each database knows about the other one but no replication traffic has been sent yet (the time_sent columns are empty).
Step 17 - Run DBREMOTE on the consolidated database to gather the "Update A" changes (required).
DBREMOTE is used to gather the latest "Update A" changes for publication to the remote clients. The following command runs DBREMOTE on the central server via the batch file shown in Figure 17a:
c:\test\consremo.bat
Figure 17a - CONSREMO.BAT - Run DBREMOTE For Consolidated-To-Remote Replication
(This file is contained in rep.zip.)
pause To run DBREMOTE on the consolidated database SET SQLREMOTE=c:\test SET cparm="DBN=consol;UID=DBA;PWD=SQL" SET oparm=c:\test\dbremote.txt DBREMOTE -c %cparm% -b -o %oparm% SET cparm= SET oparm= pause |
The following DBREMOTE parameters are used in CONSREMO.BAT:
Two local environment variables %cparm% and %oparm% are used just to make the batch file easier to read.
Tip: Let the -l message length parameter default to 50K if Windows 3.x will be used anywhere.
The -l parameter can be used to increase the maximum message length. If you do that, however, you must use the same value everywhere, and values over 64K won't work with Windows 3.x. The phrase "won't work" is a bit mild, actually; "GPF" is more to the point.
Steps like this one are usually preceded by a step that deletes old DBREMOTE output files, like Step 30. That's not necessary at this point because old DBREMOTE output files don't exist yet.
Unlike DBXTRACT it is not safe to rerun DBREMOTE repeatedly and expect to get the same output. You can run it again and again but it won't do the same thing each time, and you have to use any message files produced by each run.
Another difference is that a single run of DBREMOTE gathers data for all sites, whereas multiple runs of DBXTRACT are required, one per remote site, to extract data for the initial setup. Of course, multiple runs of DBREMOTE are required over time because data continues to change and the replication process must be repeated. The DBXTRACT runs are a one-time thing to get the remote sites started.
Figure 17b shows the window that appears on the central server when DBREMOTE is executed. When you see "Execution completed" it's OK to press the Shutdown button to close DBREMOTE.
Figure 17b - DBREMOTE Window On The Consolidated Database
Figure 17c shows the same information as it is written to DBREMOTE.TXT. Each one of the "Sending message to..." lines corresponds to a new file that's been created in one of the C:\TEST\SITEnnnn subdirectories.
Figure 17c - DBREMOTE Messages Are Appended To C:\TEST\DBREMOTE.TXT
Sybase SQL Remote Message Agent Version 5.5.03 Build #1666 Copyright by Sybase, Inc., and its subsidiaries, 1988, 1997. All rights reserved. Sybase is a trademark of Sybase, Inc. Incoming messages from directory c:\test\consol 1997-11-16 08:38:23.035 Processing transactions from active transaction log Sending message to "site0001" (0-0000000000-0) Sending message to "site0002" (0-0000000000-0) Sending message to "site0002" (0-0000000000-1) Sending message to "site0001" (0-0000000000-1) Sending message to "site0002" (0-0000000000-2) Sending message to "site0001" (0-0000000000-2) Sending message to "site0002" (0-0000000000-3) Sending message to "site0001" (0-0000000000-3) Sending message to "site0002" (0-0000000000-4) Sending message to "site0001" (0-0000000000-4) Sending message to "site0002" (0-0000000000-5) Sending message to "site0001" (0-0000000000-5) Sending message to "site0002" (0-0000000000-6) Sending message to "site0001" (0-0000000000-6) Sending message to "site0002" (0-0000000000-7) Sending message to "site0001" (0-0000000000-7) Sending message to "site0002" (0-0000000000-8) Sending message to "site0001" (0-0000000000-8) Sending message to "site0001" (0-0000000000-9) Sending message to "site0002" (0-0000000000-9) Execution completed |
The DBREMOTE.TXT file is a good thing to have in a production environment because it receives error messages when there are problems.
Figure 17d shows what the replication subdirectories contain after DBREMOTE runs: C:\TEST\CONSOL is still empty, and several CONSOL.xxx files have been placed in the C:\TEST\SITEnnnn subdirectories.
Figure 17d - Files On The Central Server After The "Update A" DBREMOTE
Directory of c:\test\consol ...empty Directory of c:\test\site0001 consol 0 49,835 97 11 16 08:38a consol 1 49,953 97 11 16 08:38a consol 2 49,797 97 11 16 08:38a consol 3 49,796 97 11 16 08:38a consol 4 49,923 97 11 16 08:38a consol 5 49,931 97 11 16 08:38a consol 6 49,877 97 11 16 08:38a consol 7 49,878 97 11 16 08:38a consol 8 49,854 97 11 16 08:38a consol 9 10,074 97 11 16 08:38a Directory of c:\test\site0002 consol 0 49,842 97 11 16 08:38a consol 1 49,795 97 11 16 08:38a consol 2 49,797 97 11 16 08:38a consol 3 49,822 97 11 16 08:38a consol 4 49,898 97 11 16 08:38a consol 5 49,955 97 11 16 08:38a consol 6 49,855 97 11 16 08:38a consol 7 49,877 97 11 16 08:38a consol 8 49,879 97 11 16 08:38a consol 9 10,215 97 11 16 08:38a |
These CONSOL.xxx files contain the replicated data to be sent to the remote databases. Since SQL Anywhere is said to use "message based replication" these files are often referred to as "messages" or "message files".
Step 18 - Make a backup: MAKECBAK 18 and MAKERBAK 18 (optional).
This is the first backup that actually finds some messages to copy: the 10 c:\test\site0001\consol.* files on the central server.
Step 19 - Run "Update B" on the remote database (required).
At this point the consolidated database is still out of step with the remote databases even though DBREMOTE has been run on the central server. That's because the replication files have not been copied to the remote computers and applied to those databases.
The databases become even further out of step when the following command is run on the SITE0001 computer to execute the INSERTs shown in Figure 19a:
ISQLW -c "DBF=c:\test\remote.db;UID=DBA;PWD=SQL" READ c:\test\update_b.sql
Figure 19a - UPDATE_B.SQL - Some 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 B' ); INSERT INTO replication3 ( subscription_id3, numeric_data3, string_data3 ) VALUES ( 'Sub1', 0, 'Update B' ); |
Step 20 - Make a backup: MAKECBAK 20 and MAKERBAK 20 (optional).
Step 21 - Show the replication status after "Update B" on the remote database (optional).
Figure 21a shows that the remote database now has rows marked "Update B" while the consolidated database has "Update A".
Figure 21a - Replication Status After "Update B" On The Remote Database
The sysremoteusers data shows the following:
For an explanation of these columns see Step 4.
Step 22 - Send the "Update A" files to the remote computer (required).
This command is run on the central server to copy the "Update A" files to the first remote computer:
rem This command only shows one remote computer site0001
copy c:\test\site0001\consol.* f:\test\site0001
Figure 22a shows that the four DBXTRACT files and the ten DBREMOTE files are residing in the SITE0001 subdirectory on the remote computer. The other subdirectory CONSOL is still empty.
Figure 22a - Files On The Remote Computer Before The "Update A & B" DBREMOTW
Directory of c:\test\consol ...empty Directory of c:\test\site0001 CONSOL 0 49,835 11-16-97 8:38a CONSOL 1 49,953 11-16-97 8:38a CONSOL 2 49,797 11-16-97 8:38a CONSOL 3 49,796 11-16-97 8:38a CONSOL 4 49,923 11-16-97 8:38a CONSOL 5 49,931 11-16-97 8:38a CONSOL 6 49,877 11-16-97 8:38a CONSOL 7 49,878 11-16-97 8:38a CONSOL 8 49,854 11-16-97 8:38a CONSOL 9 10,074 11-16-97 8:38a |
This demonstration copies files when it doesn't really have to. That's because it's using a shared file setup and the remote computer could just read the files directly from the central server's hard drive.
In real life, however, a shared file setup is often not available, and some other file transfer mechanism like FTP must be used. This demonstration is using file copying in an attempt to show how the situation becomes interesting (i.e., complex) when the full freedom of file sharing is not available.
Step 23 - Run DBREMOTW on the remote database to apply "Update A" and gather "Update B" (required).
This command runs DBREMOTW on the first remote computer to apply the "Update A" changes from the central server and to gather the local "Update B" changes:
DBREMOTW -c "DBF=c:\test\remote.db;UID=DBA;PWD=SQL" -o c:\test\dbremotw.txt
The following DBREMOTW parameters are used in this command:
Steps like this one are usually preceded by a step that deletes old DBREMOTW output files, like Step 38. That's not necessary at this point because old DBREMOTW output files don't exist yet.
Figure 23a shows what DBREMOTW.EXE displays when it runs.
Figure 23a - DBREMOTW Window On The Remote Database
Figure 23b shows the same information as it is written to DBREMOTW.TXT. Each one of the "Received", "Applying" and "Sending message" lines refers to a message file that's being processed:
If you haven't run Step 12 to delete the old DBXTRACT files from C:\TEST\SITE0001 before running DBREMOTW you may also see some warning messages that say "Deleting corrupt message". That's OK, but it does indicate that you shouldn't use these subdirectories for anything but replication messages.
Figure 23b - DBREMOTW Messages Are Appended To C:\TEST\DBREMOTW.TXT
Sybase SQL Remote Message Agent Version 5.5.03 Build #1666 Copyright by Sybase, Inc., and its subsidiaries, 1988, 1997. All rights reserved. Sybase is a trademark of Sybase, Inc. Incoming messages from directory C:\TEST\site0001 Received message from "consol" (0-0000000000-0) Received message from "consol" (0-0000000000-1) Received message from "consol" (0-0000000000-2) Received message from "consol" (0-0000000000-3) Received message from "consol" (0-0000000000-4) Received message from "consol" (0-0000000000-5) Received message from "consol" (0-0000000000-6) Received message from "consol" (0-0000000000-7) Received message from "consol" (0-0000000000-8) Received message from "consol" (0-0000000000-9) Applying message from "consol" (0-0000000000-0) Applying message from "consol" (0-0000000000-1) Applying message from "consol" (0-0000000000-2) Applying message from "consol" (0-0000000000-3) Applying message from "consol" (0-0000000000-4) Applying message from "consol" (0-0000000000-5) Applying message from "consol" (0-0000000000-6) Applying message from "consol" (0-0000000000-7) Applying message from "consol" (0-0000000000-8) Applying message from "consol" (0-0000000000-9) 1997-11-16 09:16:18.930 Processing transactions from active transaction log Sending message to "consol" (0-0000000000-0) Execution completed |
The DBREMOTW.TXT file is a good thing to have in a production environment because it receives error messages when there are problems. If you're interested in central administration with robust error diagnostics, you might want to write some application code that scans this file and stores the error messages in a database table to be replicated back to the consolidated database. But that's beyond the scope of this demonstration.
Figure 23c shows that the input subdirectory \SITE0001 is now empty and that one new file has been created in the output subdirectory \CONSOL. This file contains both the new "Update B" changes and a confirmation that the "Update A" changes were applied successfully.
Figure 23c - Files On The Remote Computer After The "Update A & B" DBREMOTW
DIRECTORY OF C:\TEST\CONSOL SITE0001 0 377 11-16-97 9:16a DIRECTORY OF C:\TEST\SITE0001 ...empty |
Step 24 - Make a backup: MAKECBAK 24 and MAKERBAK 24 (optional).
This is the first backup that finds message files on both the central server and remote computer.
Step 25 - Show the replication status after the "Update A & B" DBREMOTW (optional).
Figure 25a shows that the remote database now contains the rows marked "Update A".
Figure 25a - Replication Status After The "Update A & B" DBREMOTW
The databases have reached a higher level of consistency but aren't completely in step because the consolidated database still doesn't have the "Update B" rows. This is what's meant by "loose consistency": consistent except for stuff missing because of time lags. It's not unusual for perfect consistency to be forever just out of reach in an active replication environment.
The sysremoteusers data shows the following:
Step 26 - Run "Update C" on the consolidated database (required).
The databases are still prevented from reaching a state of perfect consistency because this command is run on the central server to execute the INSERTs shown in Figure 26a:
ISQL -c "DBN=consol;UID=DBA;PWD=SQL" READ c:\test\update_c.sql
Figure 26a - UPDATE_C.SQL - Some 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 C' ); INSERT INTO replication2 ( subscription_id2, numeric_data2, string_data2 ) VALUES ( 'Sub1', 0, 'Update C' ); INSERT INTO replication2 ( subscription_id2, numeric_data2, string_data2 ) VALUES ( 'Sub2', 0, 'Update C' ); INSERT INTO replication3 ( subscription_id3, numeric_data3, string_data3 ) VALUES ( 'Sub1', 0, 'Update C' ); INSERT INTO replication3 ( subscription_id3, numeric_data3, string_data3 ) VALUES ( 'Sub2', 0, 'Update C' ); |
Step 27 - Make a backup: MAKECBAK 27 and MAKERBAK 27 (optional).
Step 28 - Show the replication status after "Update C" on the consolidated database (optional).
Figure 28a shows that the "Update B" rows are missing from the consolidated database and the "Update C' rows are missing from the remote database.
Figure 28a - Replication Status After "Update C" On The Consolidated Database
The sysremoteusers data shows no change from Figure 25a because there haven't been any runs of DBREMOTE or DBREMOTW since then.
Step 29 - Get the "Update B" files from the remote computer (required).
This command is run on the central server to copy the "Update B" files from the first remote computer:
rem This command only shows one remote computer site0001
copy f:\test\consol\*.* c:\test\consol
Step 30 - 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\consol.*
Figure 30a shows that the "Update B" file is ready to be processed and the output subdirectory is empty and waiting.
Figure 30a - Files On The Central Server Before The "Update B & C" DBREMOTE
Directory of c:\test\consol SITE0001 0 377 97 11 16 09:16a Directory of c:\test\site0001 ...empty |
DBREMOTE "reads and deletes, writes and forgets". In other words, old input files are automatically deleted, but not old output files, and that's the reason for this step.
There is a very good reason that DBREMOTE doesn't delete old output files for us. In a production environment it may not be safe to promptly delete old output files because they may be lost in transmission and have to be resent. Of course, this demonstration assumes that everything is going OK and the problem of lost files is outside the scope.
This command runs DBREMOTE on the central server to apply the remote computer's "Update B" changes and to gather the consolidated database's "Update C" changes:
c:\test\consremo.bat
Figure 31a shows that the file just recently received in the C:\TEST\CONSOL subdirectory has disappeared and a new file has been written to the SITE0001 subdirectory.
Figure 31a - Files On The Central Server After The "Update B & C" DBREMOTE
Directory of c:\test\consol ...empty Directory of c:\test\site0001 consol a 512 97 11 16 09:53a |
Step 32 - Make a backup: MAKECBAK 32 and MAKERBAK 32 (optional).
Step 33 - Show the replication status after the "Update B & C" DBREMOTE (optional).
Whatever you do, don't stop reading now... you're almost at the end of this whole Section. Remember the earlier warning:
This is the kind of article that once you put it down, you can't pick it up again.
Figure 33a shows that the consolidated database is now up to date but the remote database is missing "Update C".
Figure 33a - Replication Status After The "Update B & C" DBREMOTE
The sysremoteusers data shows the following:
Step 34 - Run "Update D" on the remote database (required).
The following command serves to push perfect consistency further out of reach by adding the rows shown in Figure 34a to the remote database:
ISQLW -c "DBF=c:\test\remote.db;UID=DBA;PWD=SQL" READ c:\test\update_d.sql
Figure 34a - UPDATE_D.SQL - Some 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 D' ); INSERT INTO replication3 ( subscription_id3, numeric_data3, string_data3 ) VALUES ( 'Sub1', 0, 'Update D' ); |
Step 35 - Make a backup: MAKECBAK 35 and MAKERBAK 35 (optional).
Step 36 - Show the replication status after "Update D" on the remote database (optional).
Figure 36a shows that the remote database is missing "Update C" and the consolidated database is missing "Update D".
Figure 36a - Replication Status After "Update D" On The Remote Database
The sysremoteusers data shows no change from Figure 33a because there haven't been any runs of DBREMOTE or DBREMOTW since then.
Step 37 - Send the "Update C" files to the remote computer (required).
This command is run on the central server to copy the "Update C" file to the first remote computer:
rem This command only shows one remote computer site0001
copy c:\test\site0001\consol.* f:\test\site0001
Step 38 - 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:
rem This command only shows one remote computer site0001
erase c:\test\consol\site0001.*
|
Figure 38a shows that the "Update C" file is ready to be processed and the output subdirectory is empty and waiting.
Figure 38a - Files On The Remote Computer Before The "Update C & D" DBREMOTW
DIRECTORY OF C:\TEST\CONSOL ...empty DIRECTORY OF C:\TEST\SITE0001 CONSOL A 512 11-16-97 9:53a |
Step 39 - Run DBREMOTW on the remote database to apply "Update C" and gather "Update D" (required).
This command runs DBREMOTW on the first remote computer to apply the "Update A" changes from the central server and to gather the local "Update B" changes:
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 39a shows that another new file has been created in the CONSOL subdirectory.
Figure 39a - Files On The Remote Computer After The "Update C & D" DBREMOTW
DIRECTORY OF C:\TEST\CONSOL SITE0001 1 381 11-16-97 10:21a DIRECTORY OF C:\TEST\SITE0001 ...empty |
Step 40 - Make a backup: MAKECBAK 40 and MAKERBAK 40 (optional).
Step 41 - Show the replication status after the "Update C & D" DBREMOTW (optional).
Figure 41a shows that the remote database is up to date but the consolidated database is missing "Update D".
Figure 41a - Replication Status After The "Update C & D" DBREMOTW
The sysremoteusers data shows the following:
A repeat of Steps 29, 30 and 31 would bring the consolidated database into complete agreement with SITE0001. That might be unlikely in the real world, but if it was to happen, here's what those three steps would look like:
In real life yet another update would probably have been made to the consolidated database, and the third step above would NOT be as simple as "apply Update D". It would also gather changes to send down to the remote database.
And that's the end of Section Three. From now on the process of replicating changes is really repetitive, more of the same... as if it hasn't been repetitive up to this point.
| [Home] | [Table of Contents] | [Previous Section] | [Next Section] |