OpenEdge Pro2 is a lightweight, configurable tool that provides near real-time* replication of an OpenEdge database to Microsoft SQL, Oracle, or another OpenEdge database.
Note: Pro2 supports the replication of an OpenEdge database to an Azure SQL database. Due to extensive replication delays, however, performance may not be optimal, and Pro2 may not be able to provide near real-time replication. Progress recommends replicating to a Microsoft SQL Server database hosted on a VM in Azure as the preferred target if the Azure Cloud is going to be your hosting provider.

Learn about Pro2

Pro2 supports enterprises in delivering the critical business data needed to support analytical and other business intelligence initiatives by replicating specific tables and fields, databases, or multiple databases. All replications facilitated by Pro2 can take place while your transactional database is up and running, removing connectivity limitations, disruptions, and risk to your normal business operations.

With Pro2 web user interface (UI) you can intuitively configure and monitor your database replications, create new replication processes, and manage bulk loading. To deliver near real-time replication, Pro2 uses either OpenEdge Change Data Capture (CDC) or trigger-based replication for optimal performance with your OpenEdge database.

How it works

Replication triggers, or CDC, automatically capture all data changes that occur on your source database and send those changes to your target database. This means that data, like tables and rows, are not actually transferred from your source database to target database, what has transferred is a log of the changes on your source database. Information is written to the replication queue (ReplQueue) in the internal repl and Pro2 databases to identify the created, updated, or deleted record. The multi-threaded replication process retrieves the updated record and the queued data in the replication is moved to the target database. Done in near real-time, the I/O operation is optimized with the updated record residing in cache. Any analysis can be run against current data in a replicated database, leaving the transactional database resources to their normal activities, keeping your data accurate and secure. This is how Pro2 remains lightweight while providing an up-to-date copy of your source database.

The Pro2 CDC implementation additionally supports the thread #0 feature. When activated, the CDC Admin thread converts all CDC_change_tracking records regardless of the thread to which the table is mapped. This allows a single CDC thread to convert all CDC records to Replqueue records.

The CDC replication procedure is administered online and is quicker than using ABL triggers. This results in a relatively short database downtime. The client application connection remains unaffected, and there is no requirement to connect to the Repl database. Users can execute multiple threads by duplicating the CDC batch program (CDCBatch.p) and appending the thread number to the file name. For example, CDCBatch0, CDCBatch1, CDCBatch2, and so on. For more information, see Use CDC with Pro2.

The replication tables in the repl and Pro2 databases consist of minimal data, indexing, and a single sequence for process control. The repl database contains the first nine replication tables, while the Pro2 database contains the remaining twenty-six. If your business require it, the nine tables that are associated with the repl database can be embedded directly into the source database. A common use case for this type of configuration is if you need to migrate your data from one OpenEdge database to another in preparation for an upgrade or a dump and load.

To replicate, Pro2 uses a replication processor that cycles through replication records periodically, based on user configuration, and replicates the table data directly to the target database via the OpenEdge DataServer. All replication data is captured and maintained in Pro2 replication database tables to facilitate table mapping between source and target databases, as well as to maintain the queue record, the record lock, the database connection and configuration details, and the thread level information.

Pro2 allows you to configure the time interval between the replication operations. You can customize the interval in real time according to your needs. For example, you can set the interval to every n seconds, hourly, twice a day, or daily depending on your specific business needs.

Additionally, you can pause all replications, or a specific table replication temporarily should the need arise. You can restart the paused replication at any time. Keep in mind that, while the replication process is suspended, users of the target database do not have access to the records that have been updated in your source OpenEdge database until the replication process is restarted and the replication queue is completely cleared.
Note: If the replication process is paused, the backlog of data change events could take a long time to process if paused for long period of time.

While the repl and Pro2 databases are lightweight, their initial setup and configuration should be subject to every consideration and precaution as if you were creating a database for an enterprise application in a production environment. Without these databases, data is not replicated from your source database to your target database. This can cause a loss of synchronization.

Confer with your database administrator and other key stakeholders to when creating the repl and Pro2 database for your production environment. For more information about database administration, see OpenEdge database essentials.

Architecture

The basis of the replication functionality consists of five primary programs that generate the replication triggers, generate the replication processor functionality, and process the replication records, and a web-based user interface application for monitoring and administering the replication processor and configuration.

LAN or WAN

Pro2 architecture varies over local area network (LAN) or wide area network (WAN). Each configuration, whether WAN or LAN, includes:
  • An OpenEdge source database. There is usually a user application tied to the source database.
  • The Pro2 and repl databases.
  • A target database that is either a Microsoft SQL Server, Oracle, or an OpenEdge database.

LAN

Pro2 architecture over LAN consists of the following components:
  • A source, OpenEdge, database with a user application tied to it.
  • A Repl database at the source database.
  • Pro2 server, that contains the following:
    • Progress Application Server (PAS) for Open Edge (PASOE)
    • Schema holder database
    • Replication processor
    • Pro2 database
  • A target database which can be either Microsoft SQL Server, Oracle or an OpenEdge database.
    Note:
    • Starting from Pro2 v6, you require a PASOE license on the Pro2 Server, which further requires OpenEdge to be at least 11.5.1 or later to support PAS. Progress recommends installing OpenEdge12.x for Pro2 v6.2.x to take advantage of PASOE improvements, or at least the latest update of the long-term supported OpenEdge release 11.7.
    • If your target database is not an OpenEdge database, you need an additional schema holder database which contains information about the data definitions of the target databases.

WAN

When the target database is in a different geographic location than the source OpenEdge database, configure Pro2 to include a Progress Application Server on the source database server in addition to the standard configuration on the Pro2 server. In this context, the Pro2 server is where the Pro2 replication instance is configured and operating.

Instead of running the replication procedures using typical client/server mode connections to the source and repl databases, Pro2 sends requests to PAS for OpenEdge (or a classic application server) on the database server which returns data to the calling procedure on the Pro2 server.

Note: The Pro2 server must be on the same local area network as the target database server.
Pro2 architecture over WAN consists of the following components:
  • A source, OpenEdge, database with a user application tied to it.
  • A Repl database at the source side that is used to record create, update, and delete transaction events.
  • PAS on the source database server.
  • Pro2 server, that contains the following:
    • PASOE
    • Local Repl database that stores information about synchronization between the source Repl database and the Pro2 local Repl database.
    • Schema holder database
    • Replication processor
    • Pro2 database
  • A target database which can be either Microsoft SQL Server, Oracle or an OpenEdge database.

Installation planning and considerations

Before you proceed with the initial installation and configuration of Pro2, take time to create an installation plan for your implementation. Confer with your database administrator, system administrator, and other key stakeholders. Gather information about your implementation needs and consider the following questions.
  • What is the size of your OpenEdge database?
  • Do you have an environment to test the installation in?
  • Where will Pro2 be installed? Keep in mind that it can be installed on its own physical or virtual machine, or on the same machine as your target database.
  • Do you require a LAN or WAN configuration?
  • Which tables and fields need to be replicated? You can replicate your entire database a portion of it.
  • Do you need to embed the replication tables in your source database?

Pro2 setup process

* The actual Pro2 replication throughput is also determined by server and resource availability, network performance, source table structures, and other factors external to Pro2. Prior to implementing Pro2 in your environment, it is essential to be cognizant of the following considerations:
  • Separate Windows Servers for Pro2 and Microsoft SQL Server or Oracle Target.
  • The Pro2 Windows Server and Target Microsoft SQL Server or Oracle Server must be located in the same data center. Ideally, within the same subnet.
  • Pro2 Server minimum resource requirements: 4 processors, 16GB RAM, and a solid-state hard drive.
  • The Pro2 server should only run Pro2 and not any other applications.
  • Because of the system resources required for Microsoft SQL Server and Oracle, the server should have more resources than the Pro2 server.
  • 1GB network speed (bandwidth requirements may vary depending on replication activity).
  • For LAN network configuration, all servers, including the source server, must be in the same data center. Ideally, within the same subnet.
  • Pro2 (PRROWID) uses only one primary unique index on the target tables. Additional target indexes should not be unique. Plan the creation of target table indexes in accordance with the target data access requirements. When writing to the target database, the overhead of additional indexes may affect Pro2 replication performance.
  • Users running reports against the target database must use READ UNCOMMITTED as their isolation level. Otherwise, replication efficiency will suffer due to target record or table locks.
  • Replicating system tables and, if applicable, workfile tables may have an impact on replication and is not recommended.