VITAL-SYNC
LIVE BIDIRECTIONAL
FOXPRO-MYSQL DATABASE
SYNCHRONIZATION
Designed, developed, and programmed by
David
A. Caruso
-o-
TABLE OF CONENTS
Documentation Update:
1.
Overview
2.
Technical
Description
a. Operation
Methodology
b. Redundant
(Backup) Synchronization
c. Table
Migration
d. Changes
to Existing Foxpro Tables
e.
Field Type Conversions
3.
Features and
Functions
4.
Stability
and Adaptability Features
a. Application
b. Problem
Control
c. Automatic
Backup & Recovery
d. Load
Balancing / Rate Throttling
e. Self-Maintenance
f.
Low Bandwidth & CPU usage
g. Automatic Adaptation to Historical Patterns
h. Non-Interference
With Other Data/Sync Programs
5.
Usage and
Feature List
a. General
Usage
b. Using
the System Tray Icon
c.
Entering ODBC information
6.
Synchronization
Cycle
a. Overview
b. Constant
Cycles for Work-Days or Evenings/Weekends
c. Automatically
Adjust Cycle Based on Traffic History
d. The Automatic Adjustment Check Cycle Interval
e. Manual
Configuration of the Synchronization Cycle
7.
Tables
Administration
a. Overview
b. Adding
a table for Synchronization
c. Adding
a Batch of Tables
d. Remove
Table
e. Verify
Synchronization
f.
Changing the Structure of a Table
g. Automatic
Detection of Table Structure Changes
8.
Extra
Configuration Settings
9.
Remote
Control of Operations
10. Installation of Vital-Sync
a. Installation
b. System
components
11. Trouble-shooting
a. What
Vital-Sync Needs to Run
b. Developer
Contact Info
-o-
Main Activity / Configuration
Screen
1. OVERVIEW
Vital-Sync is an application that continuously monitors for changes on a MySQL or Foxpro database and duplicates those changes on the other database in near real-time. It effectively keeps two databases in perfect synchronization – monitoring and writing data transactions from both sides.
The advantage of implementing a synchronizing method such as Vital-Sync is that you can very quickly gain the functionality of other platforms (e.g. web-services using MySQL) without compromising or rewriting your current application (e.g. custom-written Foxpro).
Vital-Sync has been shown to be effective in high-traffic, multi-user environments, and can safely handle large dumps to either side of the database array (e.g. from various external jobs). The methodology that Vital-Sync uses to synchronize data ensures that information necessary for synchronization is collected whether Vital-Sync itself is running or not. Running Vital-Sync will then process the information. Keeping Vital-Sync running ensures near real-time synchronization.
In order to ensure consistency and stability, Vital-Sync has a dynamic
recovery system. In the event of a
network outage or lag, server reboot, locked tables, etc, Vital-Sync will resume
synchronization seamlessly, since it has been designed to recover during at any
point in the synchronization process.
Any data transactions that happen, even if when Vital-Sync is not
loaded, will be processed.
Vital-Sync provides detailed information related to synchronization processes in various logs. These logs can be used to track all changes to the database, including the nature of the change and when it was originally executed.
2. FEATURES AND FUNCTIONS
· Bi-directional synchronization.
· Select whole table or individual fields for synchronization.
· Adaptability and dynamic problem recovery.
· Highly-configurable.
· Highly-automated (e.g. self-configuration, backup/restoration, log purging, etc).
· Learned synchronization interval based on transaction history.
· Load balancing / Rate throttling.
· Automatic detection and compensation for Foxpro table structure changes.
· Remote control for access from automated scripts and programs.
· Table maintenance scheduling system.
· Automatic backup and restoration of system files.
· Detailed logging (configurable).
· Streamlined updates.
· Add single or batch of tables at once.
· Runs from the system tray.
· In live use at multiple sites.
3. TECHNICAL DESCRIPTION
a. Operation Methodology
Vital-Sync is a stand-alone executable compiled from Visual Foxpro 9.0 source code. The application (or “app”) runs as a system tray icon on a server with access to Foxpro tables on one side and a MySQL database on the other side. Tables from each database have simple triggers programmed into them so that changes are written to a separate table, one for each side. These “bridge” tables are monitored at a configurable interval (e.g. every 10 seconds during the day, every 5 minutes in the evening and weekends).
Vital-Sync watches for activity on these tables, and if found, puts information regarding the activity in a queue and marks it as such in the bridge table. It will store information in the queue from each side respectively, then eliminate all redundant transactions (e.g. multiple updates to the same record). It will then execute these transactions on the other side, and note these transactions in a stack in order to avoid re-executing them when the corresponding trigger-code is activated from the synchronization. (See Figure 1)
Figure 1
b. Redundant (Backup) Synchronization
Vital-Sync is equipped with a redundant synchronization method that will verify the synchronization of all tables that it monitors, and if necessary re-synchronize them by comparing them to a backup table.
c. Table Migration
Vital-Sync is also equipped with a robust table migration function that will allow the user to select a table for synchronization, and then automatically configure, migrate, and begin synchronizing that table. This makes adding or removing tables from the synchronization processes consist of only one step for the system/database administrator (see section below).
Currently, table migration happens from the Foxpro side to the MySQL side only. The table name and field names are copied exactly. Two fields are added to each migrated table (see below)
d. Changes to Existing Foxpro Tables
In order to configure a table for synchronization, Vital-Sync will add two fields to the pre-existing Foxpro tables, as well as two structural indexes related to them. Likewise, these fields will be found in the MySQL version of that table. The first field is fBDB, which is a unique integer and used as a primary key for matching records during synchronization, and the second is fOrig, which is a single-character field and used only during the Redundant Synchronization process.
e. Field Type Conversions
The following table indicates Foxpro field types and the MySQL field types used to correspond to them. Please note that non-standard characters are stripped from Foxpro character or memo fields when converting or verifying data between MySQL and Foxpro.
|
Foxpro |
MySQL |
|
Character |
Varchar |
|
Memo |
Longtext |
|
Double |
Double |
|
Date |
Date |
|
Float |
Float |
|
General |
(Not Converted) |
|
Integer |
Integer |
|
Logical |
Tinyint |
|
Decimal |
Decimal |
|
Datetime |
Datetime |
|
Currency |
Decimal |
4. STABILITY AND ADAPTABILITY FEATURES
Vital-Sync is designed with many features to make it run smoothly and without user intervention under a wide variety of circumstances.
a. Application
Because Vital-Sync uses code embedded in the databases that it synchronizes, information necessary for synchronization is collected whether the application is running or not. If the application is shut down for maintenance, or for other reasons, information is still collected, and then is processed as soon as Vital-Sync is booted up, effectively “catching-up” the synchronization from where it left off.
b. Problem-Control
Vital-Sync is designed so that if the chain of events along the synchronization cycle is cut for any reason at any point during its execution (e.g. if one of the tables becomes locked), then Vital-Sync will note this and continue execution from a normal point. During the next sync-cycle, it will then check that this problem has been resolved, and if so, then execute the synchronization transaction. All information related to synchronization status is stored in logs.
Vital-Sync can also be configured to react to specific kinds of errors, and override normal operations. For instance, it has been configured so that if the MySQL data files are being backed-up by a separate process, and are not available, then it will detect this situation and suspend all synchronizing for 20 minutes, or some other configurable period of time. It will then reconnect to the MySQL database and resume normal operations.
c. Automatic Backup & Recovery
If the application is forced to close for some reason (e.g. the server crashes), and-or system tables become corrupted, the tables will automatically be restored from a recent backup. The back-up process is completed several times a day during the maintenance cycle, and so the restored tables will likely be up-to-date or very close.
d. Load Balancing / Rate Throttling
If VS sees more than a given number
of table transactions to synchronize, then it will spread these transactions out
over a time, freeing up network resources during business hours. After business hours, when network usage is
low, VS will catch up all remaining transactions in one sweep, as is normal. This behavior is configurable in the
Configuration Panel. One can set the
maximum number of transactions to process at any time (default is 500), the
period of time to wait between synchronizations while load balancing is in
effect (default is 90 seconds), and what constitutes business hours (default is
7:00 am to 7:00 pm, M-F).
e. Self-Maintenance
Vital-Sync will automatically refresh and check the integrity of its own system tables on a cyclic basis. It will check the sizes of various log files, and if beyond a certain size, will export old information to a separate back-up file (in Excel spreadsheet format) and/or delete it.
f. Low Bandwidth & CPU usage
Vital-Sync is designed to be bandwidth and CPU sensitive, and to operate continuously with minimal drain on system resources. Because of this, it can reside on the server containing both sides of the database array without compromising the speed of user access to the databases themselves, or the speed and accessibility of the server in general. In the event of a high number of transactions, rate throttling will come into effect and spread transactions out over a given period of time during business hours.
g. Automatic Adaptation to Historical Patterns
Vital-Sync also comes with an adaptable synchronization cycle. This function will monitor the historical usage of the databases during a given day of week and given time, and automatically adjust the interval to compensate. The result is that the usage of resources are automatically adjusted to account for historical data-traffic patterns of a particular environment.
h. Non-Interference With Other Data/Sync
Programs
Vital-Sync will not synchronize information that is already synchronized. Therefore, it will not cause a problem when used in conjunction with other synchronization or data-upload programs. It can handle sudden and large data dumps as well as constant multi-user traffic.
4. USAGE AND FEATURE LIST
a. General Usage
Once tables have been assigned for synchronization (see below), then the click the “Start Synchronization” button on the main screen. You can also check the “Automatically Begin Synchronization” checkbox to do this automatically upon boot-up or after table migration. The program will then run automatically. To turn off synchronization, hit the space bar, or select the system tray icon and click “Halt Synchronization”.
Please note: in order to configure Vital-Sync (see below), you must halt synchronization.
b. Using the System Tray Icon
Vital-Sync usually runs in the background, with no task-bar item. You can bring up the interface by clicking on the Vital-Sync system tray icon and selecting, “Show Vital-Sync”. You can hide the interface by the same method.
c. Entering ODBC information
If the information regarding the ODBC gateway to the MySQL database has not been entered, it will be asked for and confirmed by the program upon boot-up.
5. THE SYNCHRONIZATION CYCLE
a. Overview
A synchronization cycle is the amount of time between a check of either side of the database for a data transaction. This means, effectively, querying the “bridge” table on both the Foxpro side and the MySQL side, and then writing any changes noted to the other side. It can be configured as a constant, or be automatically adjusted (see above).
b. Constant Cycles for Work-Days or
Evenings/Weekends
If you wish to keep a constant interval during business hours, then stop synchronization and click “Use Constant Interval During Work Days” and enter in the number of seconds between each synchronization check. Do likewise for the evening/weekend cycle.
c. Automatically Adjust Cycle Based on Traffic
History
If you wish Vital-Sync to adjust the interval based on historical patterns (see above), then click “Automatically Adjust Interval”. Logging information will be analyzed and used to configure the Synchronization Interval every X minutes (see below).
d. The Automatic Adjustment Check Cycle Interval
Relevant here is the “Adjust Check-Cycle”. Here, you can indicate the number of minutes between each time Vital-Sync will update the Synchronization Interval according to various other settings (already described). A good default is 10 minutes.
e. Manual Configuration of the Synchronization
Cycle
You can manually set the Synchronization Interval by clicking the box that shows the interval or by clicking the “Set Sync Interval Button”.
Tables Administration Screen
6. TABLES ADMINISTRATION
a.
Overview
This is the second main configuration screen. From here you can add tables to the synchronization, migrate them from Foxpro to MySQL, add or migrate tables as a batch, remove tables from synchronization, and verify the synchronization between tables. You can also view disk path information, table names, and when these tables were last manually checked for synchronization (using the redundant synchronization function, or when the table the table were first migrated).
b. Adding a table for Synchronization
Click the “Add Table” button to begin. You will then be asked to select the Foxpro table you wish to keep synchronized. This table will then be configured and migrated to the MySQL database (already connected to at program boot-up). Warnings will be issued if the table is already on the MySQL side, or if exclusive access to the table on the Foxpro side cannot be obtained. The amount of time it takes to configure and migrate a table will obviously depend on the size of the table and the speed of the server doing the operation.
c. Adding a Batch of Tables
By using the “Add Batch” button, you can select a list of tables and then have them be migrated one after the other without user intervention.
d. Specify Fields.
Select a table and click this button to specify which fields in the table to synchronize. A list will be shown of all the fields on one side of the screen, and the currently-synchronized fields on the other. You can move fields from one to the other. By default, all fields are synchronized.
e. Remove Table
Use this button to remove a table from synchronization. Neither table from each side of the database system will be deleted. The table will simply not be monitored for synchronization, and its trigger-code will be removed.
f. Verify Synchronization
Select a table from the list, then click this button. It will check each record of the table on each side for synchronization, and if found to be non-consistent, will check a back-up table from the last known synchronization point and then make the change from one side to the other. All changes will be noted in a log.
g. Changing the Structure of a Table (keyword: table structure change)
In order to change the field structure of a table being synchronized, the table must first be removed from the Synchronization cycle using the “Tables Administration” function in Vital-Sync. The table’s structure must then be changed on the Foxpro side for the changes to be picked up by Vital-Sync. After changing the structure of a Foxpro table, then re-migrate the table in Vital Sync using the “Tables Administration” screen.
If a table’s structure is changed
without following the above steps, then the new fields will not be
synchronized, and if old fields are removed, it could result in a
synchronization error and-or the table becoming out-of-sync.
h. Automatic
Detection of Table-Structure Changes
Vital-Sync
will automatically detect table structure changes on the Vital-Trac (Foxpro)
side, and then compensate. It will look
for changes once per hour, or upon program boot-up. It is possible to configure it for constant
checking, but this would be an unnecessary burden on CPU and bandwidth as such
table structure changes only happen fairly rarely.
If
a table structure changes occurs, usually Vital-Sync will continue operating
without synchronizing the extra field, until such time it detects it. Or, if a field is changed or deleted, an
error may result that will delay synchronization of that table until the proper
detection is made, upon which time the table will be re-integrated and
synchronization caught up. The only
field that cannot be changed in this way is fBDB system-only field, which is
used to record table activity for synchronization. This field can be changed following other
methods if necessary.
Process Description. Once
Vital-Sync detects a table structure change, it will immediately cease synchronizing
that table, although synchronization data will still be collected for it
through the table trigger activity.
Vital-Sync will then wait until it can get exclusive access to the
table, and upon doing so will remove it from the synchronization pool and
return the table to a pre-integration state.
Vital-Sync will then wait until again it can get exclusive access to the
table, and then re-migrate it over to MySQL and resume synchronization
functions automatically. At this point,
the table will be recognized by the system and fully synchronized.
Typically,
tables are being used during business hours, thus preventing exclusive access
by any one process. Thus, business
operations will not be affected by the detection and re-integration process,
except that in the interim period Vital-Web and Vital-Trac will not be fully
synchronized with information recently recorded related to the table in
question. All other tables will continue
to be synchronized normally.
Extra
Configuration Screen
7. EXTRA CONFIGURATION
Besides the main configuration available on the main screen, there are extra configuration settings. Access this control panel by clicking the “Extra Configuration” button on the main screen. Here is a run down of extra configurations:
a. Show Detailed System Activity. Check this if you want to have a log of routine system processes that normally are not logged. Using this, every step of the synchronization process, from checking for new transactions to writing transactions, is stored and viewable.
b. Show Long Descriptions in Log. This will insert a descriptive sentence regarding a logging event in the log. Normally, only a descriptive code word is used in order to save disk space.
c. Show Abbreviated Synchronization Information. Configure this to show, in the activity window, detailed information about synchronization processes or not. Using detail will show how many updates, inserts, or deletes were done on either side of the process. Not using detail will only show the number of synchronizations that happened during a given period.
d. Export Logs to Storage Folder. If you wish to save transactions logs (which show which records in a particular table were modified, and generally how they were modified), then check this box and specify an output folder. All older logging information will be exported to this folder in Excel spreadsheet format. No logging information will be lost.
e. Remote Control of Vital-Sync (Path /Table). Enter the path and name of the table Vital-Sync will create by which you can control Vital-Sync operations from a program or script.
f. Load-Balancing. Select “Balance Transaction Load” to spread a
large number of transactions out over a given period in order to reduce network
congestion. Enter the maximum number of
transactions at once in the appropriate box, as well as the number of seconds
between synchronization intervals when Load Balancing is in effect. For example, by entering “500” transactions
and “90” seconds, then if there are more than 500 transactions at once, then
transactions/synchronizations will be done in groups of 500 every 90
seconds. 500 transactions may only take
several seconds to complete, in which case the network will be free for the
remainder of the 90-second interval.
g. Table Maintenance Interval. This specifies the number of seconds that pass before each maintenance cycle. This will check the sizes of the log files and export and delete information as necessary, and other routine maintenance. It will also check for table structure changes on the Foxpro side (if this option is enabled).
h. Automatically Check for Table Structure Changes. Select this option if you wish Vital-Sync to automatically detect changes on the Foxpro side. See above documentation for details on this process. Note that MySQL side changes are currently ignored. To initiate a change in the MySQL-side structure, conduct the change manually on the Foxpro side and it will automatically be propagated over to the MySQL side.
i. System Table Refresh Interval. This specifies the number of seconds between each system table maintenance, which includes closing, compressing, reindexing, and backing up, and re-opening various system tables. If any system table becomes corrupt (e.g. which might happen in the server freezes), then a backup will be restored from the most recent cycle.
j. Beginning and Ending Work-Day Hours. Specify here the official start and close of
each work-day (Monday through Friday).
This will determine, if this setting is used, how active Vital-Sync is
(and therefore how much CPU it will use).
Military time is used. If you
wish, for example, to set the beginning of each work-day at
k. Suspension Duration for Repeated Connection Errors. Specifies the number of seconds the program will suspend itself in the event that the MySQL server is down for any reason (e.g. if it is being backed up and unavailable). The default is 20 minutes (or 1200 seconds). Operations then resume after this period.
l. Number of Connection Errors Before Suspension. This specifies how many times Vital-Sync will
try to access the MySQL server before it assumes it is down and then suspends
operations for the amount of time indicated above.
8. REMOTE CONTROL OF OPERATIONS
Vital-Sync is typically controlled from
its interface. But this may not always
be convenient in the event of routine operations. To account for this, a way has been added to
control relevant Vital-Sync operations remotely or from a program. This is accomplished through a system table
that is automatically created somewhere on the network. Commands are placed in the table, and are
detected and processed by VS. Right now,
two commands have been programmed, but more can be created as the need arises.
The system table, by default, is called
tvitalsync.dbf and is placed in
Vital-Trac's main data directory. The
name and location of the table is configurable by going into the Configuration
Panel. This table is automatically created by VS whenever it is found not to be
present. The table is only read when it
is not in use by another process. This
allows the process or DBA to have complete control entering information into
the table without risking an unwanted command execution. Once the table is free, it is checked for new
commands.
The table has five main fields: fCommand,
fTable, fResponse,
fCompleted, and fFailed.
Place the command (see below) in fCommand,
and the name of table in question (no path) in fTable. Close the table. After Vital-sync processes the command, it
returns a detailed response in fResponse,
and marks fCompleted with “YES”. If the command failed, then fFailed will contain “YES”. Error information is given in fResponse.
If the command succeeds, then fFailed
will not be populated. Following are the
two commands programmed so far:
DISABLE
Use this command to temporarily remove
trigger-processes from a Foxpro-side table.
In the event of large table updates, this may be desirable. When this command is run, then
synchronization data is still collected from the MySQL database and run on the
table. In lieu of this, synchronization
only happens one-way, as transaction information is not collected from the
Foxpro table. The table is automatically
scheduled for restoration into the synchronization pool after a default of one
day (see below). It can be restored
earlier (as soon as given remote processes are complete) by using the “ENABLE”
command (see below).
ENABLE
This command will restore a
previously-removed table from the synchronization pool. First it will wait until all pending
MySQL-side transactions are run on the table.
Then it will rebuild the table back into the synchronization pool and
migrate the table from scratch to MySQL, ensuring that both sides are
completely synchronized. If the table is
under a certain size (the default is 500,000 records), then the table will be
re-installed as soon as VS can get a lock on the table. If the table is larger, then re-installation
will automatically be done after business hours.
9. INSTALLATION OF VITAL-SYNC
a. Installation
Currently, the installation of Vital-Sync is a multi-step process that must be conducted by a Foxpro programmer. With some simple modifications, this limitation can be eliminated.
The main steps in installation are:
1. Install the latest version of MySQL and configure root account and other parameters.
2. Create the database to host the MySQL side of the synchronization.
3. Install the MySQL ODBC driver and configure it for the above database.
4. Create a bridge table in the MySQL database, by issuing the following command:
CREATE TABLE TBRIDGE (FTYPE VARCHAR(1) NOT NULL
DEFAULT '',
FTABLES INT NOT NULL DEFAULT -1, FBDB INT NOT NULL
DEFAULT 0,
FTIMESTAMP TIMESTAMP, FMARK VARCHAR(1) NOT NULL
DEFAULT '');
5. Go into the Component Services (under Administrative programs in Windows), find MySQL, get properties, then configure so that service automatically restarts after X minutes in case of a fault (which can happen in the event a backup process locks the database files).
6. Put the Vital-Sync system files into a folder of your choice.
7. Create a backup folder and a log-export folder.
8. Copy the dbDBD.* and tBridge.dbf files into the main Foxpro tables folder.
9. Configure the pathways in the procedure code of the dbDBB Foxpro database container.
10. Configure paths in the system table tMain in various fields, both system paths and data paths.
11. Run Vital-Sync, add a small test table, and test synchronization from both sides.
12. Run table-migration procedures.
b. System components
Vital-Sync has the following internal system component files:
· Code (*.fxp): pMain.prg (main program code, all procedures are found here)
· Forms (*.sct, *.scx): mConfig, mField, mMain, mTables, mGetData
· Tables (*.dbf, *.fpt): tAutoMig, tConfig, tConvert, tError, tFields, tHour, tMain, tProcess, tProcessLog, tSchedule, tStack, tTables, tVital-Sync
· Images (*.jpg): image-blue-01, Link 06
· Executable (*.exe): Vital-Sync
· Classes (*.vct, *.vcx): SysTray
9. TROUBLE-SHOOTING & UPGRADING
a. What Vital-Sync Needs to Run
There are several key things Vital-Sync needs to function properly. Once it is installed and tables are migrated, then all aspects should be automatically or programmatically in place. Here is a summary of requirements:
· Each database on both sides of the system (i.e. the MySQL side and the Foxpro side), must have a bridge table, the configuration of which is detailed above under “Installation”. This bridge table is usually located in the same folder as the tables being synchronized.
· Each Foxpro table being synchronized must be part of a database container that contains the imbedded procedure code listed the Vital Sync Foxpro system table tMain, in field fTriggers. The path information listed in the code should point to the Foxpro side bridge table.
· Each table being synchronized must have the insert, update, and delete triggers in place. The code is embedded automatically when the table is added to the synchronization cycle in the “Tables Administration” screen. If the triggers on a table somehow become lost, then re-add the table.
· The network folder in which the Foxpro data lives should be given a network drive letter (usually this is the V: drive).
· All component files (listed above in section System Components) should be present.
· Vital Sync is run when someone logs into the computer. The server must be logged into for Vital Sync to run (the computer can be locked, however), until at some point in the future Vital Sync becomes a system process that runs irregardless of whether an account is logged into the server machine. To check if Vital-Sync is running, look for the Vital-Sync tray icon (see below) and hold the mouse over it. A status message will be displayed.
· The MySQL server should be running, and an active ODBC connection made to it and programmed into Vital-Sync during the installation.
·
All Foxpro tables being synchronized must be shared,
and not kept in exclusive mode or otherwise locked. If a table is locked, Vital-Sync will wait
until the table is free before it can run synchronization commands on that
table. This is true also for the table
on the MySQL side.
b. Developer Contact Info
Please contact David A. Caruso at caruso/at/efn.org or davidacaruso/a/gmail.com, or by phone at 541-870-1442.