Tip 78: Replication Step By Step

A Detailed Demonstration
Of Publish Subscribe Replication Using Sybase SQL Anywhere
With MESSAGE TYPE "FILE"

• List of Figures
• Introduction (Start here)
• A Replication Refresher
• Download supporting files in rep.zip (33K).
  
  
• Section One: Set Up The Consolidated Database
  
  
  • Step Zero - Get ready.
  • Step 1 - Make file replication subdirectories on the central server (required).
  • Step 2 - Run DBINIT, DBSRV50 and ISQL to set up the consolidated database (required).
  • Step 3 - Make a backup: MAKECBAK 3 and MAKERBAK 3 (optional).
  • How To Make A Backup
  • How To Do A Restore
  • Step 4 - Show the replication status after the consolidated database is set up (optional).
  • How to run SQL files via ISQL and ISQLW
  
  
• Section Two: Set Up The Remote Database
  
  
  • Step 5 - Run DBXTRACT on the consolidated database (required).
  • Step 6 - Make file replication subdirectories on each remote computer (required).
  • Step 7 - Set the SQLREMOTE environment variable as required (suggested).
  • Step 8 - Send the DBXTRACT files to the remote computers (required).
  • Step 9 - Run DBINITW and ISQLW to initialize the remote databases (required).
  • Step 10 - Make a backup: MAKECBAK 10 and MAKERBAK 10 (optional).
  • Step 11 - Show the replication status after the remote database is set up (optional).
  • Step 12 - Delete the old DBXTRACT files from the remote computer (suggested).
  • Step 13 - Delete the old DBXTRACT files from the central server (suggested).
  
  
• Section Three: Replicate Changes Among The Databases
  
  
  • Step 14 - Run "Update A" on the consolidated database (required).
  • Step 15 - Make a backup: MAKECBAK 15 and MAKERBAK 15 (optional).
  • Step 16 - Show the replication status after Update A (optional).
  • Step 17 - Run DBREMOTE on the consolidated database to gather the "Update A" changes (required).
  • Step 18 - Make a backup: MAKECBAK 18 and MAKERBAK 18 (optional).
  • Step 19 - Run "Update B" on the remote database (required).
  • 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).
  • Step 22 - Send the "Update A" files to the remote computer (required).
  • Step 23 - Run DBREMOTW on the remote database to apply "Update A" and gather "Update B" (required).
  • Step 24 - Make a backup: MAKECBAK 24 and MAKERBAK 24 (optional).
  • Step 25 - Show the replication status after the "Update A & B" DBREMOTW (optional).
  • Step 26 - Run "Update C" on the consolidated database (required).
  • 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).
  • Step 29 - Get the "Update B" files from the remote computer (required).
  • Step 30 - Delete the old DBREMOTE output files from the central server (suggested).
  • Step 31 - Run DBREMOTE on the consolidated database to apply "Update B" and gather "Update C" (required).
  • Step 32 - Make a backup: MAKECBAK 32 and MAKERBAK 32 (optional).
  • Step 33 - Show the replication status after the "Update B & C" DBREMOTE (optional).
  • Step 34 - Run "Update D" on the remote database (required).
  • 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).
  • Step 37 - Send the "Update C" files to the remote computer (required).
  • Step 38 - Delete the old DBREMOTW output files from the remote computer (suggested).
  • Step 39 - Run DBREMOTW on the remote database to apply "Update C" and gather "Update D" (required).
  • Step 40 - Make a backup: MAKECBAK 40 and MAKERBAK 40 (optional).
  • Step 41 - Show the replication status after the "Update C & D" DBREMOTW (optional).
  
  
• Section Four: Force Consistency Via SYNCHRONIZE SUBSCRIPTION
  
  
  • Step 42 - Run "Update E" on the consolidated database (required).
  • 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).
  • Step 45 - Run "Update F" on the remote database (required).
  • 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).
  • Step 48 - Run "Synch 0001" SYNCHRONIZE commands on the consolidated database (required).
  • Step 49 - Make a backup: MAKECBAK 49 and MAKERBAK 49 (optional).
  • Step 50 - Delete the old DBREMOTE output files from the central server (suggested).
  • Step 51 - Run DBREMOTE on the consolidated database to gather "Synch 0001" and "Update E" data (required).
  • Step 52 - Make a backup: MAKECBAK 52 and MAKERBAK 52 (optional).
  • Step 53 - Send the "Synch 0001" files to the remote computer (required).
  • Step 54 - Delete the old DBREMOTW output files from the remote computer (suggested).
  • Step 55 - Run DBREMOTW on the remote database to apply the "Synch 0001" data (required).
  • Step 56 - Make a backup: MAKECBAK 56 and MAKERBAK 56 (optional).
  • Step 57 - Show the replication status after the "Synch 0001" DBREMOTW (optional).
  
  
• Section Five: Send Commands Via PASSTHROUGH
  
  
  • Step 58 - Run the DDL on the consolidated database (required).
  • Step 59 - Make a backup: MAKECBAK 59 and MAKERBAK 59 (optional).
  • Step 60 - Run the "Passthrough" commands on the consolidated database (required).
  • Step 61 - Make a backup: MAKECBAK 61 and MAKERBAK 61 (optional).
  • Step 62 - Delete the old DBREMOTE output files from the central server (suggested).
  • Step 63 - Run DBREMOTE on the consolidated database to gather the "Passthrough" data (required).
  • Step 64 - Make a backup: MAKECBAK 64 and MAKERBAK 64 (optional).
  • Step 65 - Start replication of the new table on the consolidated database (required).
  • Step 66 - Make a backup: MAKECBAK 66 and MAKERBAK 66 (optional).
  • Step 67 - Run CHECKDDL.SQL on the consolidated database (optional).
  • Step 68 - Run DBREMOTE on the consolidated database to gather the "Start Replication" data (required).
  • Step 69 - Make a backup: MAKECBAK 69 and MAKERBAK 69 (optional).
  • Step 70 - Send the "Passthrough" and "Start Replication" files to the remote computer (required).
  • Step 71 - Delete the old DBREMOTW output files from the remote computer (suggested).
  • Step 72 - Run DBREMOTW on the remote database to apply "Passthrough and Start Replication" data (required).
  • Step 73 - Make a backup: MAKECBAK 73 and MAKERBAK 73 (optional).
  • Step 74 - Run CHECKDDL.SQL on the remote database (optional).
  
  
• And In Conclusion
• On The Next Geraldo...

List of Figures

• Figure i1 - A Replication Refresher
• Figure i2 - Let's Make Some Assumptions
• Figure i3 - Steps And Sections At A Glance
• Figure i4 - Files Provided With This Demonstration
• Figure i5 - Other Resources

Section One

• Figure 1a - MAKECDIR.BAT - Make Replication Subdirectories On The Central Server
• Figure 1b - Naming Convention For Subdirectories And Files
• Figure 2a - CRETABS.SQL - Create The Tables On The Consolidated Database
• Figure 2b - CREUSERS.SQL - Set Up Replication-Related User Ids On The Consolidated Database
• Figure 2c - CREPUBS.SQL - Set Up The Publications And Subscriptions On The Consolidated Database
• Figure 2d - CREDATA.SQL - Some Initial Load Data For The Consolidated Database
• Figure 3a - MAKECBAK.BAT - Make A Backup Of The Central Server Files
• Figure 3b - MAKERBAK.BAT - Make A Backup Of The SITE0001 Remote Computer Files
• Figure 3c - RESTCBAK.BAT - Restore Files On The Central Server
• Figure 3d - RESTRBAK.BAT - Restore Files To The SITE0001 Remote Computer
• Figure 4a - SELDATA.SQL - Summarize The Current Database Status
• Figure 4b - SELDATA.SQL Via ISQL Shows The Current Status Of The Consolidated Database
• Figure 4c - SYSREMO.SQL - Display Current Contents Of SYSREMOTUSERS
• Figure 4d - SYSREMO.SQL Via ISQL Shows SYS.SYSREMOTEUSERS On The Consolidated Database
• Figure 4e - Replication Status After The Consolidated Database Is Set Up
• Figure 4f - Contents Of REPSTAT.PBL
• Figure 4g - Window Open Script From W_Replication_Status In REPSTAT.PBL
• Figure 4h - Configure ODBC Data Source "CONSOL" On The Central Server
• Figure 4i - Startup Options For ODBC Data Source "CONSOL" On The Central Server
• Figure 4j - Configure ODBC Data Source "REMOTE" On The Central Server
• Figure 4k - Startup Options For ODBC Data Source "REMOTE" On The Central Server

Section Two

• Figure 5a - XTRACT.BAT - Run DBXTRACT On The Consolidated Database
• Figure 5b - DBXTRACT.TXT - Message Log For SITE0001 On The Central Server
• Figure 5c - RELOAD.SQL - The DBXTRACT Command File To Load The Remote Database
• Figure 5d - DBXTRACT Data Files Used By RELOAD.SQL
• Figure 5e - Mirror-Image User Ids And Addresses In CREUSERS.SQL And RELOAD.SQL
• Figure 6a - MAKERDIR.BAT - Make Replication Subdirectories On A Remote Computer
• Figure 11a - Replication Status After The Remote Database Is Set Up

Section Three

• Figure 14a - UPDATE_A.SQL - Some New Rows For The Consolidated Database
• Figure 16a - Replication Status After Update A
• Figure 17a - CONSREMO.BAT - Run DBREMOTE For Consolidated-To-Remote Replication
• Figure 17b - DBREMOTE Window On The Consolidated Database
• Figure 17c - DBREMOTE Messages Are Appended To C:\TEST\DBREMOTE.TXT
• Figure 17d - Files On The Central Server After The "Update A" DBREMOTE
• Figure 19a - UPDATE_B.SQL - Some New Rows For The Remote Database
• Figure 21a - Replication Status After "Update B" On The Remote Database
• Figure 22a - Files On The Remote Computer Before The "Update A & B" DBREMOTW
• Figure 23a - DBREMOTW Window On The Remote Database
• Figure 23b - DBREMOTW Messages Are Appended To C:\TEST\DBREMOTW.TXT
• Figure 23c - Files On The Remote Computer After The "Update A & B" DBREMOTW
• Figure 25a - Replication Status After The "Update A & B" DBREMOTW
• Figure 26a - UPDATE_C.SQL - Some More New Rows For The Consolidated Database
• Figure 28a - Replication Status After "Update C" On The Consolidated Database
• Figure 30a - Files On The Central Server Before The "Update B & C" DBREMOTE
• Figure 31a - Files On The Central Server After The "Update B & C" DBREMOTE
• Figure 33a - Replication Status After The "Update B & C" DBREMOTE
• Figure 34a - UPDATE_D.SQL - Some More New Rows For The Remote Database
• Figure 36a - Replication Status After "Update D" On The Remote Database
• Figure 38a - Files On The Remote Computer Before The "Update C & D" DBREMOTW
• Figure 39a - Files On The Remote Computer After The "Update C & D" DBREMOTW
• Figure 41a - Replication Status After The "Update C & D" DBREMOTW

Section Four

• Figure 42a - UPDATE_E.SQL - Yet More New Rows For The Consolidated Database
• Figure 44a - Replication Status After "Update E" On The Consolidated Database
• Figure 45a - UPDATE_F.SQL - Yet More New Rows For The Remote Database
• Figure 47a - Replication Status After "Update F" On The Remote Database
• Figure 48a - SYNC0001.SQL - Synchronize Subscriptions For SITE0001
• Figure 50a - Files On The Central Server Before The "Synch 0001 & Update E" DBREMOTE
• Figure 51a - Files On The Central Server After The "Synch 0001 & Update E" DBREMOTE
• Figure 54a - Files On The Remote Computer Before The "Synch 0001" DBREMOTW
• Figure 55a - Files On The Remote Computer After The "Synch 0001" DBREMOTW
• Figure 57a - Replication Status After The "Synch 0001" DBREMOTW

Section Five

• Figure 58a - CONSDDL.SQL - DDL Commands For The Consolidated Database
• Figure 60a - PASS.SQL - Passthrough Script For The Consolidated Database
• Figure 62a - Files On The Central Server Before The "Passthrough" DBREMOTE
• Figure 63a - Files On The Central Server After The "Passthrough" DBREMOTE
• Figure 65a - STARTREP.SQL - Start Replication Of The New Table On The Consolidated Database
• Figure 67a - CHECKDDL.SQL - Check That Everything Is OK
• Figure 68a - Files On The Central Server After The "Start Replication" DBREMOTE
• Figure 71a - Files On The Remote Computer Before The "Passthrough and Start Replication" DBREMOTW
• Figure 72a - Files On The Remote Computer After The "Passthrough and Start Replication" DBREMOTW
• Figure 72b - DBREMOTW Fails When Passthrough DDL And Replicated Data Are In The Same File
• Figure 74a - "Passthrough and Start Replication" Processing Worked OK On The Remote Database