For several years now, Synergex has maintained an open-source example that provides an example of how to implement the replication of a Synergy applications data to a Microsoft SQL Server database, in near-to-real-time. The example environment makes considerable use of CodeGen to generate the bulk of the code needed to implement the interaction with the SQL Server database, and much of the remaining required code can be used out-of-the-box, requiring very little, if any change to the original Synergy application to enable the data replication to take place. The example environment has been used as a template by many customers, and our Professional Services team has assisted many others by delivering either proof of concept examples, or full-scale implementations.

As technologies and product capabilities evolve, we periodically revisit the code to ensure that it is taking advantage of the latest features and adhering to best practices. Good performance is also of critical importance in products like this, so we frequently revisit the code looking for opportunities to make improvements in throughput.

We have just completed the latest review of the code, and on this occasion, we did make some changes, which are briefly described below.

• We now generate an additional function that returns the key number of the first unique key for each ISAM structure. This allows us to avoid the need for code that previously detected the first unique key number at runtime; that code required that the replicator had an open channel to each data file being replicated.
• We also generate an additional function that, when passed a record containing data, returns the key value of the first unique key. Previously, the code used the Synergy routine %KEYVAL for this purpose, but it also requires that the replicator has an open channel to every data file replicated.
• Because of the previous two changes, we were able to remove the replicator’s requirement to open the underlying data files that are being replicated. The only files that the replicator now opens are the instruction queue file and log file.
• We added code to make the replicator more resilient to interruptions to network connections when using xfServer to access the instruction queue file on a remote system. If a network problem is detected, the replicator now closes the instruction queue file and then attempts to re-open it on a new channel. If this operation fails, it will retry several times with a delay between attempts. The number of retries and the delay between retries are both configurable via command-line options or environment variables.

If you already have a SQL Replication environment based on our sample environment, then you might consider checking out the latest version and applying the changes to your own codebase, and if you’d like some help with that, then our Professional Services team will be happy to assist. And if you haven’t yet implemented a SQL Replication environment but are interested in doing so, get in touch with your Synergex account rep and ask them to set up a demo.