Henry Schein v6.5.1

Pro2 Implementation Report

Date: 26 Jun 2024 

Consultant: Donna Staples

• Pro2 Implementation Report 
  • Team
  • Overview
  • Pro2 Server Information
  • Target Database Server Information
  • Source Database Server Information 
    • Databases
    • Files Modified
  • Application Server Information 
    • Files Modified
  • Troubleshooting 
    • Pro2 Technical Support
    • Pro2 Product Documentation
    • Pro2 Replication Log Files
    • How to modify email settings for Pro2 alerts
    • How to check the Pro2 replication status when it appears data is not being replicated to the target
    • How to check status of Pro2 scheduled tasks
    • How to check the Pro2 AppServer in a WAN configuration
    • How to remove replication triggers in an emergency
    • How to reload tables to the target

Team

Overview

The Pro2SQL implementation for Henry Schein uses a WAN configuration given the source and target database servers are separated by a WAN.  The Pro2 server is on the same side as the target database server.  A Progress "AppServer" is configured on the database server which sends data across the WAN to the Pro2 server.

The SQL Target Schema contains all the tables and fields from the OpenEdge source schema with additional Pro2 specific columns and indexes.  If desired for queries on the SQL target, the indexes from the source application can be brought over, with any uniqueness removed.  If the source database indexes are not needed on the target, it would benefit performance to not set them up on the target side to reduce overhead.  Additional tables, columns and indexes can be added to the target SQL database as desired.  However, note that  If you want to add indexes to the target database, they should not be UNIQUE indexes as that may break replication due to the order in which the triggers fire.  The only UNIQUE index must be for the Pro2 PRROWID column. When adding additional objects to the target database such as Tables or Views it is highly recommended to add them under a different schema owner in order to differentiate them from the Pro2 replicated tables.

Pro2SQL replicaiton uses the OpenEdge Change Data Capture (CDC) feature.  CDC policies are configured for those tables in the source database that are to be replicated.  Once activiated, CDC will capture all create, update, and delete events made to a record in a change data capture tracking table.   

The Pro2 'replqueue' table, where change event records are stored, is read continuously by Pro2 clients on the Pro2 Windows server.  The Pro2 clients read each replqueue record and write the change to SQL.  The replqueue records are deleted by Pro2 after processing.

Pro2 Server Information

Notes:

• The Pro2 tasks are launched via Windows Task Scheduler.  The user account must be a service account that is maintained regularly or whose account and password do not expire otherwise the Pro2 tasks will not run.
• Log files are located in the bprepl\repl_log folder under the root Pro2 instance location specified above.  

Target Database Server Information

Notes:

• The target database is a read-only database.  Only no-lock reads should be made on the target data.
• Source database indexes <were/were not> created on the target database.  If the indexes are not used on the target, they should not be configured in the target database to reduce overhead of maintaining the indexes and thus help performance.

Source Database Server Information

Databases

Notes:

• The Lock Table size of the repl database must be at least as large as the lock table size of the source database
• The number of users that can connect to the repl database must be at least as high as for the source database
• Large files must be enabled on the repl database to allow the replqueue to grow over 2 GB if needed.  If the replqueue needs to grow larger than 2 GB and large files are not enabled, the database and application may go down.
• If the repl tables are embedded in the source database and AI is enabled, the disk space used for AI extents should be monitored and increased as needed

Files Modified

Notes:

Application Server Information

Files Modified

Notes:

Troubleshooting

Pro2 Technical Support

Support for Pro2 is provided by Progress Technical Support via standard support channels.  You will be asked for your customer number when contacting Technical Support.  Your customer number is listed in your Progress license addendum for Pro2 and is noted below.  

Pro2 help is also available via the online Pro2 Community and the Progress Knowledgebase.  Be sure to check the Progress Knowledgebase for information on any errors or issues you may have as the solution you need may already be documented.  

Pro2 Product Documentation  

For Pro2 version 6, Pro2 doc is available online:

Pro2 : https://docs.progress.com/category/pro2

Pro2 Replication Log Files

Pro2 log files are located in bprepl\repl_log under the root Pro2 folder on the Pro2 Server  There are two types of log files:

1. repllog - Primary Pro2 log.  The logging level can be set via Pro2 Admin tool.  When troubleshooting, set the logging level to verbose.
2. replproc - Secondary log file where errors that can not be trapped programmatically are written.  If there are no errors in the primary repl log, check the replproc log.

The log file naming convention is  <log-type><date>-<thread#>.log

There is one of each type of log file generated daily for each thread.  Logs from previous days can be deleted at any time.  The current day's log files for a thread can be removed only when the thread has been stopped; a new log file will be generated when the thread is restarted.

For WAN configuration, there are additional repl log files on the database server.

How to modify email settings for Pro2 alerts

Edit the predefs.i file which is under the root Pro2 folder on the Pro2 Server.

Modify the following lines to specify the mail server, "from" email address (which does not need to be a real email address) and the "to" email address list (comma separated list of email addresses).

&GLOBAL-DEFINE COMPANY-MAIL-SERVER mail.customer.com
&GLOBAL-DEFINE FROM-EMAL-ADDR pro2sql@customer.com
&GLOBAL-DEFINE TO-EMAL-ADDR  

How to check the Pro2 replication status when it appears data is not being replicated to the target

1) Check the main monitor screen of the Pro2 Admin utility.  The status for each thread will be Running or Stopped.  There is also a control radio setting that is either ON or OFF.  The thread can not be started if the control setting is OFF.

2) Check the log files.  Are there any status messages, warnings or errors?  Set the logging level to verbose and then check the logs again for any activity.

3) Run a report of the replqueue from the Pro2 Admin utility Tools menu.  This will show how many replqueue records for each table and thread are in the queue waiting to be processed.  Replqueue records are processed by each thread in first-come first-served order; large batch jobs that generate a large number of replqueue records will delay the processing of replqueue records for other tables in the same thread.  

4) Check the  status of the Pro2 tasks in Windows Task Scheduler.   

5) For Pro2 in a WAN configuration, also check the status of the AppServer on the database server.

How to check status of Pro2 scheduled tasks

Open Windows Task Scheduler and check the task status.  If status is "Ready", right click on the task and select 'Run'.  Does status change to and stay "Running"?  

If the task status will not stay 'Running':

1) Check the Pro2 logs to see if replication is trying to start up or if there any errors; it may help to set the logging level to verbose 

2) Try to start the Pro2 thread manually (by running the replbatch<thread#>.bat file in the bprepl\Scripts directory.  Does the task run?  If so, there is likely an issue with the permissions, settings, or password of the user account the Pro2 tasks are set to "run as".

3) From Task Manager, check to see if there is a process running for that thread.  Add "Command Line" to columns to display in the Task Manager "Details" pane and look for and _progres process running with parameter "Thread=<thread#>

How to check the Pro2 AppServer in a WAN configuration

1) rom the Admin Tool "WAN" menu, check to see if the AppServer is connected.  If not, restart the the AppServer on the db server side.  

2) Query the status of the AppServer and restart if it is not running.  This is done from the command line on the database server:

   To query the status:          asbman -i <Pro2 AppServer Name> -q

   To start the AppServer:    asbman  -i <Pro2 AppServer Name> -start

3) Check the AppServer logs for errors/issues.  There are two types of logs on the database server for the Pro2 AppServer in a WAN configuration.  One is the Pro2 replication log and is similar to the repllog file on the Pro2 server.  This log file shows the activity for the thread and any errors..  The other is the AppServer process server log file itself which show any issues with starting or running the appserver itself.

How to remove replication triggers in an emergency

Once the replication triggers are inserted into the source database schema, the "repl" database (or database in which the repl tables are embedded) must be connected and the replication trigger procedures must execute for the application to function properly.  In emergencies, the replication triggers can be removed from the source database schema by running the "ReplTrigDel.p" procedure which is located under the top-level Pro2 folder on the Pro2 server.   Remove the trigger definitions is a fast process, however, the database must be down in order to run this procedure as it is a schema change and requires exclusive access to the schema.   Once the triggers are removed, the target database will no longer be kept in synch with the source database and must be re-seeded after the triggers have been re-inserted.

How to reload tables to the target

It may be necessary to reload data from the source database to the target database.  To do so, you can use the Pro2 Bulk Load utility which can be launched from the Pro2 Admin "Tools" menu or directly from the bat file in bprepl\scripts.  Note, you must set the "Reload if already loaded once" flag to use to reload a table that has a "COMPLETE" control record from a  previous bulk load.  See the Pro2 Configuration and Administration Guide for more information on the Bulk Load utility.

Log files for the bulkloads are in bprepl\repl_mproclog.  There are two types of bulkload logs:  one for individual tables loaded, and one per bulk load utility "thread".

There are three per table logs generated with the foillowing naming convention:
lmr<dbname>_<tablename>.txt (bulkload log updated every 100,000 rows loaded until finished, then will note total rows loaded).
lmr<dbname>_<tablename>_Err.txt (notes any records that had errors loading, replqueue records are generated to capture these records)
      lmr<dbname>_<tablename>_retent.txt  (notes any records that were skipped due to custom retention rules.)

The second type of bulk load log is for the bulk load utility itself.   The bulkload utility can launch up to nine "threads" to run in parallel, each loading a different table and then moving on to next table in sequence of tables specified to be loaded as each table completes.  Each instance of the bulkload utility is given a different "fun flag" to distinguish the logs for each instance.  The naming convention is:
     bulk-runner_<run flag>_Errors<date>.txt