Pioneering Bidirectional Data Replication in Relational Databases

Digital Transformation and the methodologies facilitating a smoother and seamless process for companies have become increasingly pertinent in recent times. This impetus led us to consult a highly skilled specialist, Roman Saltanov, renowned as a Data Architect, Team Lead, and Java back-end developer. He is also the program’s author that enables a bank to manage employee accounts centrally, control their access to all banking software products in one place, and maintain a log of all their operations across the bank’s entire ecosystem. Saltanov will provide further insights into the development and implementation of this software solution, sharing how he overcame the challenges encountered and the specific technologies used to address this endeavour.

Legacy System Challenges

In the dynamic landscape of project implementation, the occasional need arises for innovative solutions, and bidirectional data replication is one such complex challenge. It’s my firm belief that embracing such replication strategies requires prudence, especially due to the inherent constraints of data transfer speed—especially vital in the high-volume world of modern applications. However, in my unique case, the demand for bidirectional data replication stemmed from the intricacies of a specific project.

Strategic Parallel Development

The challenge presented itself as follows: tasked with the modernisation of a legacy software ecosystem, hereafter referred to as the “Legacy System,” interfacing with a Sybase ASE database. This Sybase ASE database was traditionally configured for one-way data replication, serving as the data source for numerous other databases in more than a dozen software systems—all unified under the Sybase ASE database umbrella (see Image).

Database Design Deviations

My new project sought to reimagine and enhance the existing system’s functionality, leveraging cutting-edge technologies. Recognizing the Legacy System’s pivotal role and extensive feature set within the broader ecosystem, a strategic decision was made to run the development of the new project in parallel. As the new project matured, its services gradually superseded the functionalities of the Legacy System.

Adhering to licensing constraints and nuanced considerations, the database for the new software complex was designed on the Microsoft SQL (MS SQL) platform, with deliberate deviations from the Legacy System. Although the table structures bore similarities, differences persisted in data types and field counts.

Bidirectional Data Exchange

Faced with the imperative of concurrent operation between the new and legacy systems, I embarked on implementing bidirectional data exchange mechanisms with data type conversions. It’s essential to underscore that this solution served as an interim measure until the full realization of the new project, at which point the new database would assume its role as the primary data source for consumers.

The comprehensive implementation of bidirectional replication through a programmatic approach was considered intricate and time-intensive. Consequently, I opted to leverage existing tools and technologies. In this context, data replication between Sybase servers was executed through the Sybase Replication Server, while replication between Sybase and MS SQL environments utilized Oracle GoldenGate. The latterfacilitated customizable data type conversions and replication management through scripts (see Image).

However, the initial approach to replication proved unviable. Simultaneously subscribing to a single table using both Sybase Replication Server and Oracle GoldenGate resulted in an unexpected circular data exchange, leading to an impasse (see Image ).

A solution was devised by introducing an additional field in the replicated tables of the new database, responsible for halting replication operations. In the codebase of the new project, this field was systematically cleared during UPDATE/INSERT operations. Consequently, when changes occurred in the legacy database, replication proceeded through conventional channels until it reached the GoldenGate component of the new system. During subsequent UPDATE/INSERT operations, the GoldenGate component raised a replication stop flag, effectively breaking the cycle. Likewise, during UPDATE/INSERT operations in the new database, replication operations were initiated, and a stop flag was concurrently set. Once again, replication followed standard routes until it reached the GoldenGate component, where the stop flag was examined, culminating in the termination of replication operations (see Image ).