Replicator
Introduction
The Replicator provides the capability to replicate BusinessCraft ISAM data files to SQL database tables.
SQL Tables are created as mirror copies of the source tables and tables can be selectively replicated.
The Replicator comprises two versions which provide the same functionality but different interfaces:
A Windows Version (BsnCraftReplicatorWinApp) – for interactively selecting tables to replicate
A Command Line Version (BsnCraftReplicatorConsoleApp) – for automating the replication process as a script using a batch file and optionally run on a schedule using a Windows scheduling tool.
Prerequisites
There must be an ODBC DSN in the standard format of BSN_xfODBC_<BC Dataset Name> for each BusinessCraft dataset to be replicated.
SQL Server must be installed (can be SQLExpress) and there must be a SQL database for each dataset to be replicated preferably named using this format : <BC Dataset Name>_ReplicatedData
Installation
BusinessCraft Professional and Enterprise Tier customers can download and use the Replicator at no additional cost other than for any services requested related to installation, configuration and training. BusinessCraft will make the required files available on request:
Window Version BsnCraftReplicatorWinApp.zip
Console Version BsnCraftReplicatorConsoleApp.zip
Copy the files to the MapDr > Releases > Replicator folder and then decompress the files to a location in accordance with company requirements.
Command Line Parameters
Both versions of the Replicator support the passing of the following parameters when the Replicator is started:
/Server = <SQL Server Name>
Enables the specification of the destination SQL Server Instance that will store the replicated data (defaults to LOCALHOST)
/Database = <SQL Database Name>
Enables the specification of the SQL Server Database name that will store the replicated data.
The database must be created in the SQL Server Instance prior to using the Replicator.
Recommended naming convention is <BusinessCraft Dataset Name>_ReplicatedData
/Company = <BusinessCraft Dataset Name>
Enables the specification of the source BusinessCraft Dataset to be replicated. Please note that this is the DSN Dataset name. For example, if the DSN is BSN_xfODBC_BCDEMO, then the BusinessCraft DSN Dataset Name is BCDEMO.
/Tables = List of tables to be replicated
Enables the specification of a comma delimited list of table names to be selected where the whole dataset is not being replicated. If this parameter is omitted, no tables are selected for replication.
Wild cards are supported e.g. JOB* will select all tables that start with “JOB” for replication, “*” will select all tables for replication.
Input files are supported e.g. @RepList will load the list of tables from the text file “RepList.txt” (defaults to .txt file extension). The RepList.txt file needs to be stored in the MapDr\Releases\Replicator\BsnCraftReplicatorWinApp if using the Windows Version of the Replicator. If using the Console Version of the Replicator, the RepList.txt file needs to be stored in the same folder as the Replicator batch.
Each line in the file can contain a comma delimited list of tables including wild cards.
/Pause = True/False
Used to indicate whether the Console Version will pause at the end of execution or close automatically. Ignored by the Windows Version.
Values supported are TRUE/FALSE, YES/NO. First character of each of these options is supported
Case is ignored. Defaults to TRUE
/Username – The user name for access to SQL Server
/Password – The password for access to SQL Server
Note: The Username & Password parameters are not required if the SQL Server Instance has been configured to use Windows Authentication instead of SQL Server Authentication. Contact your database administrator to find out what connection details are required to access the SQL Server Instance.
xfODBC Setup
To prevent possible problems with a large number of files being processed at the one time or tables with a larger number of columns, it is recommended that the Statements and Columns settings in the Driver Maximums for the required xfODBC DSNs are checked and both set to 1024 if they are not already set to those values.
Configuration – Windows Version
When the BsnCraftReplicatorWinApp.zip is decompressed, the following files are displayed:
Right click on the BsnCraft.Replicator.WinApp.exe file and create a shortcut in a suitable location. Multiple shortcuts can be created and configured each with different start-up parameters.
Right click on the shortcut just created, select Properties and go to the Shortcut Tab.
The Command Line Parameters are appended to the existing information contained in the Target field preceded by a space as follows:
Q:\Releases\Replicator\BsnCraftReplicatorWinApp\BsnCraft.Replicator.WinApp.exe /SERVER=GREGBPC\SQLEXPRESS /DATABASE=BCDEMO_ReplicatedData /COMPANY=BCDEMO /PAUSE=TRUE
This completes configuration of the Windows Version and the Replicator is ready to use. Running the the Windows Version of the Replicator is covered later in this document.
Configuration – Console Version
The Console Version of the Replicator can be used to perform any of the replicator actions without user interaction. It can be setup to run from a batch file and scheduled to run on a periodic basis (e.g. hourly, nightly, weekly etc.) Command Line options are available as for the Windows Version and are passed as parameters on the command line. Progress is displayed in the standard output window.
When the BsnCraftReplicatorConsoleApp.zip is decompressed, the following files are displayed:
Create a batch file to run the Console Version with the required command line options. Here is an example:
This completes configuration of the Console Version and the Replicator is ready to use. Running the the Console Version of the Replicator is covered later in this document.
Using the Replicator – Windows Version
On the basis that the prerequisites, installation and configuration has been completed successfully as already described in this document, the Windows Version of the Replicator is ready to run.
Click on the shortcut for the dataset to be replicated and the following screen will appear. Please note that a display resolution of at least 1680 x 1050 is required to properly display the entire replicator screen.
The screen is composed of five panels across the top and a grid below those panels showing the tables available for replication. Each component of the screen and how to replicate the selected tables will be explained shortly.
The information displayed on screen is highly dependent on the command line options recorded in the Target Field of the Shortcut’s properties as shown in the Parameters Panel. Note that there are no Table Parameters.
If the /Tables option is not specified in the shortcut used to open the Replicator, whilst all tables in the dataset are displayed in the grid, none are selected. (Selected is 0 in the Table Summary Panel and no tables will be ticked in the IsSelected column in the Grid)
Here is another example, but in this case the /Tables command line option was set to CUS*, VENMASA – this means select all tables starting with CUS as well as the VENMASA table are to be selected.
On initial inspection, it appears as if the same data is displayed in the Grid, but looking closer the Tables Parameter has details of the Command Line Parameter used and there are now 10 tables selected as shown in the Table Summary Panel. Scrolling down the grid displays the 10 tables satisfying the /TABLES parameter ticked in the IsSelected column.
The concept of Selected Tables is important, as it is only Selected Tables that will be replicated regardless of what is showing in the grid.
Grid
The Grid shows the tables available for selection and replication. Before explaining the use of the panels across the top of the screen, the contents of the grid need explanation.
| Record Selector |
This column with no heading shows the current cursor position in the grid. Standard Windows conventions of Shift and Control enable the selection of multiple tables which will be highlighted. This is used in conjunction with the Set Selected and Clear Selected options in the Selections Panel. Clicking in the blank column heading will highlight all tables. |
| TableID | The standard table identifier for the table in BusinessCraft. |
| Name | The BusinessCraft table name. |
| Tag | A special tag character used to identify records in each logical table that have multiple record types. |
| PrimaryKey |
The field name of the Primary Key in the BusinessCraft table. Generally, the same primary key will be used to identify records in the destination database, however some tables may instead use an auto generated identifier due to some ODBC restrictions (for example the JCLINE* tables). Tables with a Control Record will automatically skip over this special record in the source database when replicating the data (and when determining the source record counts). |
| FileID | The physical file that the table belongs to in the BusinessCraft database. |
| IsControl | This indicates if the table is a Control Record. A Control Record is a special record in the physical file that contains setup information used by the BusinessCraft system. Control records are identified by a blank primary key and will automatically be ignored by both the Replicate and Get Count Actions. |
| HasControl | Indicates that the table has a Control Record associated. A Control Record is a special record in the physical file that contains setup information used by the BusinessCraft system. Control records are identified by a blank primary key and will automatically be ignored by both the Replicate and Get Count Actions. |
| IsSelected |
Indicates that the table has been selected to be processed by any of the Replicator Actions. Entries can be selected or cleared by clicking on any individual checkbox in the grid for this column. Multiple tables can be selected or cleared by clicking the appropriate button in the Selections Panel (after highlighting one or more rows in the grid by holding the Shift or Control keys whilst arrowing with the keyboard or clicking with the mouse.) |
| Srce Recs | The number of records in the source table excluding control records. The count will only be updated after running one of the Replicator Actions. |
| Dest Recs |
The number of records in the destination table. The count will only be updated after running one of the Replicator Actions. A count of negative 1 indicates the table does not exist in the destination database. |
.
Filters Panel
The Filter Panel is used to determine the tables that are shown in the grid. By default, all tables are shown in the grid regardless of whether they are selected or not. As previously explained, If the /TABLES command line option is used in the shortcut properties, then those tables will be selected when the Replicator opens but the grid will still default to show all tables.
Click the Radio Buttons to show All Tables, only Selected Tables or only Unselected tables in the Grid.
Enter the starting portion of a table name in the TABLES field to only show table names that start with that text. This works in conjunction with the radio buttons.
Use the Clear button to clear the Tables field and Apply to re-apply the current filters.
Using the previous example where only CUS* and VENMASA tables were selected, by clicking on the Selected Radio Button the grid now appears as follows:
Now by adding VEN in the Tables field to the existing Selected option, the Grid changes to this (the grid changes during typing):
Parameters Panel
The Parameters Panel displays the command line parameters that were passed to the replicator by the shortcut used to open the Replicator.
| Company | The BusinessCraft Dataset that is the data source |
| Database | The SQL Database that will hold the replicated data |
| Server | The SQL Server Instance |
| Tables | The tables to be selected when the replicator is opened |
Table Summary Panel
The Table Summary Panel displays summary information for the tables in the grid:
| Selected | The number of tables Selected (“IsSelected” column ticked) |
| Total | The total number of tables |
| Shown | The number of tables currently shown in the grid with the current filters applied |
| Total Recs | The sum of all the record counts for all source tables (after running a Replicator Action) |
Selections Panel
The buttons in the Selections Panel provide the ability to set or clear the IsSelected column.
| Clear All | Clears selection for all tables |
| Select All | Sets selection for all tables |
| Clear Selected | Clears selection for all the highlighted rows in the grid |
| Set Selected | Sets selection for all the highlighted rows in the grid |
Actions Panel
The buttons in the Actions panel provide options to carry out the replication process.
| Get Counts | Determines the number of records in the source & destination tables for the selected tables |
| Drop Tables | Removes the selected tables from the destination database. |
| Replicate | Drops and recreates each table in the destination database to ensure schema is up to date and then copies the rows from the selected source tables to the destination database. |
Logger
When any of the Replicator Actions are initiated, a window is displayed which shows the progress as some of the tables may have a large number of records.
Using the Replicator – Console Version
On the basis that the prerequisites, installation and configuration has been completed successfully as already described in this document, the Console Version of the Replicator is ready to run.
Run the batch file for the dataset to be replicated. Depending on the settings in the batch file, progress may be sent to the screen or to a file.
There are no options to fine tune the replication process or interact with the tables as is the case for the Windows Version. However, this provides a replication process that can be run on a scheduled unattended basis.
Replicator Performance Considerations
In most cases, the BusinessCraft database will contain files and tables with a large number of records. The time required to replicate these large tables could be significant and will depend on a number of factors such as:
- Server Configuration
- Server Operating System Version
- Server Processor Speed
- Server Memory
- Server Disk Type & Speed
- Server SQL Version
- Network Configuration
- Speed & traffic on the network
It is recommended the following points are considered as part of data replication planning:
Perform speed tests on selected sample tables in a test environment prior to replicating all tables for a large dataset.
Check the size of files prior to performing any replication actions. This can be done from within BusinessCraft using the “Display File Status” option from the File Utilities Menu.
Solid State Drives (SSD) can provide a significant speed improvement over traditional mechanical hard drives
Running the Replicator on the same server as either the SQL Server or the server where the BusinessCraft xfServer Services are installed should reduce the amount of network traffic and increase throughput
Retrieving the record counts from source tables that have a “tag” may take a significant amount of time as the ODBC query needs to process all the records in the logical table (compared to non-tag tables where the total records in the physical file can be returned almost instantly)
Throughput between 5000 to 8000 rows per second in a configuration where the BusinessCraft source data files were accessed from an external SSD drive connected via USB3 and where the SQL Server was hosted on the machine running the Replicator has been confirmed. Different results will occur depending on system configuration and server load
Revision 2
27th June 2017