Data Replicator (BC to SQL) Plugin
Introduction
The Data Replicator (BC to SQL) Plugin runs within the BusinessCraft Service framework and is used to replicate BusinessCraft ISAM databases to SQL Server databases. The Data Replicator (BC to SQL) Plugin replaces the standalone Replicator application previously available from BusinessCraft.
Key features include:
SQL Tables are created as mirror copies of the source tables.
Tables can be selectively replicated.
Data can be merged, replaced in full or only data modified since a specific date replicated.
Options are available to only replicate specified columns of tables.
Options are available to include conditions to determine data to be replicated.
Replication can be configured to run on every poll of the BusinessCraft Service or use the BusinessCraft Service Schedule capabilities to determine when replication is carried out.
All the standard features of BusinessCraft Service Plugins are available for the Data Replicator (BC to SQL) Plugin.
System Requirements
The following minimum system requirements are necessary to enable use of the Data Replicator (BC to SQL) Plugin:
BusinessCraft V5.05
BusinessCraft Service V5.5.12.5
Synergy V10.3.1b
SQL Server or SQL Express.
System ODBC DSN in the standard format of BSN_xfODBC_<BC Dataset Name> for each source BusinessCraft dataset to be replicated.
SQL database for the replicated data from each source BusinessCraft dataset.
A SQL Server Login with System Admin Rights that will be used to access the SQL Server Database and store the replicated data.
The Data Replicator (BC to SQL) Plugin correctly configured and enabled in the BusinessCraft Service.
System ODBC DSN
The Data Replicator (BC to SQL) Plugin uses ODBC to access data to be replicated from the source BusinessCraft Dataset and requires a System ODBC DSN to carry out the actions required.
Go to the Windows ODBC Data Source Administrator and click on the System DSN Tab. Click Add and select the xfODBC driver to set up a System DSN for the source BusinessCraft dataset containing the data to be replicated. In this data is to be replicated from the BCDEMO BusinessCraft dataset:
Click OK and the System DSN Tab will be displayed containing the System DSN just created:
SQL Server
The Data Replicator (BC to SQL) Plugin supports replication from BusinessCraft to SQL Server or SQL Server Express. BusinessCraft provides the SQL Server Installation Guide for those unfamiliar with SQL Server.
Ensure SQL Server can be accessed
Ensure that SQL Server is installed and connect to SQL Server using SQL Server Management Studio:
Click Connect and SQL Server Management Studio opens with connections established to authorised databases:
Create SQL Database for Replicated Data
Right click on the Databases Folder in the Object Explorer panel to create a new database that will holder the replicated data from BusinessCraft. Select New Database from the Context Menu and enter a Database Name:
Click OK and the created Database appears as a folder underneath the Databases Folder:
SQL Replicator User
The Data Replicator (BC to SQL) Plugin provides the option to use a specified SQL Username and Password to access SQL Server when carrying out replication processes.
If a SQL Username and Password is not defined, then the Data Replicator (BC to SQL) Plugin will use the User Name and Password defined on the Service Tab of the BusinessCraft Service. If no user name or password is defined on the Service Tab, then the Windows Services account will be used.
Regardless of the user account to be used, that account requires access to the SQL Server Replication database with System Admin Role privileges. In this example, we will create a specific user in SQL Server for replication purposes.
In SQL Server Management Studio, right click on the SQL Server instance in Object Explorer, select Properties from the Context Menu and select Security from the Select a Page panel:
Ensure that Server Authentication is set to SQL Server and Windows Authentication Mode. If it is, Cancel. If not, change the setting and click OK.
If the setting was changed, it is essential before proceeding further to right click on the SQL Server instance in the Object Explorer panel and select Restart from the Context menu. Failure to do so will cause SQL Server login issues.
A confirmation prompt will display – click Yes to restart:
After restarting the service (where applicable), create a new user by right clicking on the Logind Folder within the Security Folder:
Provide a login name, ensure SQL Server Authentication is selected and provide a Password for the Login:
Ensure the Password enforcement settings are in accordance with company policy for a system type user. Click OK to create the login.
Right click on the login just created in the Object Explorer and select Properties from the Context Menu. Select Server Roles from the Select a Page Panel and make sure the sysadmin Server Role is ticked:
Click on the User Mapping from the Select a Page panel and make sure the required replication databases are ticked in the Map column.
Click OK to return to the Object Explorer.
BusinessCraft Service
Click on the BusinessCraft Service Maintenance icon on the desktop or if not visible, run the BusinessCraftServiceMaintenance.exe file normally found in the Program Files > BusinessCraft > BusinessCraft Service folder on the BusinessCraft server.
Service Tab
The Service Tab is displayed when the BusinessCraft Service opens – the fields on this screen are explained in the BusinessCraft Service Product Guide.
Only fields directly relevant to the Data Replicator (BC to SQL) Plugin setup and configuration are explained in this guide:
| Check Frequency | Generally, the Data Replicator (BC to SQL) Plugin will be configured to run to a specific schedule, in which case this setting is not relevant. |
| User | Specify a user name if a user account other than the Windows Services Account is to be used by the BusinessCraft Service. The Test Cred button tests the User Name and Password supplied. |
| Password | Specify the password for the User Name supplied. The Test Cred button tests the User Name and Password supplied. |
| Version | Check the version of the BusinessCraft Service in the bottom left hand corner of the screen to ensure it meets the System Requirements outlined earlier. |
| Service Status | Check the status of the BusinessCraft Service. It does not matter whether the service is running or stopped for now as any configuration changes that are made will not take effect until the service is restarted. |
Remote Procedures Tab
The Data Replicator (BC to SQL) Plugin does not use xfServerPlus and so the settings on this tab are not relevant for this plugin.
Plugins Tab
Click on the Plugins tab to see the currently installed plugins. In the screenshot below the Data Replicator (BC to SQL) Plugin has already been installed and is running (green light):
| + Add Plugin | Click the + add Plugin button to add a new plugin. |
| Testing Mode | The Testing Mode check box allows the BusinessCraft Development team to test plugins without updating records. |
| Attempt Plugin Restarts | Indicates whether the BusinessCraft Service will automatically attempt to restart stopped plugins. |
| Enabled | Indicate whether BusinessCraft Service plugins are enabled or not. |
| Sort Name | Provides the option to sort the plugins by ascending or descending order. The Sort column is populated with the Display Name by default but can be overwritten if required. |
| Enabled | A check box that indicates whether the plugin is enabled or not. |
| Sts | The plugin status – Running (Green), Starting, Stopping or Scheduled to Start (Orange), Stopped (Red), Disabled (Black). |
| Display Name | A unique user defined name for the plugin if a name other than the actual name of the plugin is preferred. |
| Plugin Type | The system defined type for the plugin. Select the plugin from the dropdown list. |
| Company | Not applicable to the Data Replicator (BC to SQL) Plugin. |
| Frequency | The polling frequency for this plugin. The Every Poll setting will poll in accordance with the Check Frequency in the Service Settings on the Service Tab. If required, other frequencies can be selected per plugin including an advanced option providing for a customised polling frequency. |
| Log | Indicates whether an activity log is to be maintained for the plugin. |
| Settings Filename | The name and location of the file containing the settings for this plugin. This field should be left blank and will automatically populate after the Data Replicator (BC to SQL) Plugin has been configured. |
| ? | Opens BusinessCraft Service Plugin Help. |
| Summary | Opens the BusinessCraft Service Configuration Summary for Plugins. |
Data (BC to SQL) Replicator Plugin
Add Plugin
Click the + Add Plugin button and the New Plugin Details screen is displayed:
| Plugin Type | Select the Data Replicator (BC to SQL) Plugin from the list. |
| Display Name | Accept the default or type in a preferred Display Name. |
| Sort Name | Accept the default or type in a preferred Sort Name. |
| Company Code | Not applicable for the Data Replicator (BC to SQL) Plugin. |
| Success Reporting | Use to determine whether error messages written to the log are verbose or not – not relevant for this plugin. |
| Enabled | Indicates whether the plugin is enabled or not. Leave unticked as plugin has not been configured yet. |
Settings Maintenance
After clicking Save in the New Plugin Details screen, the Settings Maintenance – Data Replicator (BC to SQL) Plugin screen is displayed:
Settings
| Description | Used to provide a brief explanation of the purpose of the replication. |
| Company | The source BusinessCraft Dataset containing the data to be replicated to a SQL Database. |
| Tables | A comma delimited list of BusinessCraft Tables to be replicated. |
| Merge Tables | Indicates whether data is to be merged on replication (True) or not (False). Needs to be set to True if Modified Only is set to True and a date is provided for Modified Since. |
| Drop Tables | Indicates whether existing tables in the SQL Server Database are to be dropped before replicating (True) or not (False). |
| Modified Only | Indicates whether only modified records are to be replicated (True) or not (False). Merge Tables must be set to True. When running the Replicator for the first time, this setting must be False. |
| Modified Since | Only replicate records modified since a specified date. Leave blank to use last run time. Merge Tables and Modified Only must be True. |
| Ignore Duplicates | Indicates whether duplicate records in the source dataset are to be replicated (False) or not (True). |
| Include Company | Indicates whether the Company Code will be included in the replication (True) or not (False). Provides the ability to replicate data from multiple companies for the same table into one SQL Table. |
| Use DATLOG | Indicates whether the DATLOG file will be used to track when a source BusinessCraft Table to be replicated was last modified (True) or not (False). Only required for BusinessCraft files that do not contain a modified time stamp. |
| System DSN | If DATALOG is set to True, the System DSN to be replicated. |
| Config | An xml file that allows custom attributes of the tables in SQL to be specified. For example, aliases for the table name. |
| Include Notes | Indicates whether notes fields are to be replicated (True) or not (False) if the source BusinessCraft table to be replicated contains an attached spec box. |
| Include Date | Indicates whether a replication date field is added to the SQL Database table (True) or not (False). |
| Show Version Number | Indicates whether the Data Replicator Plugin Version Number is shown in the Test Results screen (True) or not (False). Do not set to True when Replicator is Live. |
| TableSettings | TableSettings provides the ability to specify which fields are replicated for the tables specified in the Table Field. If left blank, then all fields are replicated for the specified tables. |
SQL Configuration
| SQL Server | Select the Data Replicator (BC to SQL) Plugin from the list. |
| SQL Database Name | Accept the default or type in a preferred Display Name. |
| SQL Username | Enter the SQL Server Username that is authorised to access the required SQL Database. Leave blank if the user on the Services Tab is to be used. |
| SQL Password | Enter the SQL Password for the SQL Username that is authorised to access the required SQL Database. Leave blank if the password on the Services Tab is to be used. |
TableSettings Collection Editor
After clicking the Collection button for TableSettings, the TableSettings Collection Editor screen is displayed. Click Add to specify the fields to be replicated and applicable conditions:
| TableCode | The table name to only have certain fields replicated. |
| Primary Keys | No longer supported – will be removed in a future version. |
| Where Clause | Specify conditions applicable to the replication. |
| Columns | The field names for the fields to be replicated. |
Example of Completed Settings
After exiting from the Data Replicator (BC to SQL) Plugin configuration screens the Settings Filename is populated with a path to an xml file containing the settings for the plugin.
The file can be edited directly with a text editor instead of using the Plugins interface, however great care is required in making any changes. The file can also be copied and used for another Data Replicator (BC to SQL) Plugin configuration to save setup time where for example multiple datasets are involved and only minimal differences are required in the plugin settings.
Enabling the Data Replicator (BC to SQL) Plugin
For any plugin to function the Enabled tick box in the top right hand corner of the screen must be checked. To enable a specific plugin, ensure the enabled tick box next to the plugin is also checked as shown. The Sts column provides a visual indicator of the status of the plugin as follows:
Green Running
Orange Starting
Black Disabled
Red Stopped
Click Apply to save and stay on the screen or click Save to save and exit. The following message will appear advising to stop and start the BusinessCraft Service for the settings that have been changed to take effect.
| + Add Plugin | Click the + add Plugin button to add a new plugin. |
| Testing Mode | The Testing Mode check box allows the BusinessCraft Development team to test plugins without updating records. |
| Attempt Plugin Restarts | Indicates whether the BusinessCraft Service will automatically attempt to restart stopped plugins. |
| Enabled | Indicate whether BusinessCraft Service plugins are enabled or not. |
| Service Status |
|
| Version | The current build version for the BusinessCraft Service. |
| ? | Opens BusinessCraft Service Plugin Help. |
| Summary | Opens the BusinessCraft Service Configuration Summary for Plugins. |
| Stop | Stops the BusinessCraft Service. |
| Start | Starts the BusinessCraft Service. |
| Emulate | The Emulate Button provides the ability to operate the plugin using the current user login credentials rather than being run as a service. In all other respects, clicking on the Emulate Button is equivalent to enabling the plug in and restarting the service. When the user logs off the emulation stops. |
| Restart | Stops and starts (restarts) the BusinessCraft Service. |
| Save | Will save any changes made to the current settings and close the BusinessCraft Service. |
| Close | Will close the BusinessCraft Service without saving any changes made since the last save was performed. |
| Apply | Will save any changes made to the current settings and leave the BusinessCraft Service open. |
Restarting the BusinessCraft Service
As indicated by the message received when saving any changes, those changes will not take effect until the BusinessCraft Service has been restarted. To restart the BusinessCraft Service, click the Restart button.
Alternatively, if the Status of the Service is Running, click on the Stop Button and wait until the Status is Stopped. Then click on the Start Button to restart the Service and wait until the Status is Running.
Revision 3
1st March 2021