Real-time Data Replication for Prescription Benefit Facilitator

Organization

Our client is a family and pharmacist-owned and operated prescription benefit facilitator. Their mission is to deliver an innovative, clinically focused model and provide the industry’s best service. They achieve this by providing a completely transparent, pass-through arrangement that aligns with customer goals while reducing inappropriate drug utilization, improving health outcomes, and guaranteeing savings.

Challenge

Our client did not have the internal expertise to design and implement a cost-effective real-time data replication solution with an on-premise DB2 LUW source and Azure SQL target. They engaged XTIVIA to build a custom data replication solution that would be utilized for replicating from DB2 LUW to Synapse Analytics as well.

Technical Solution

XTIVIA utilized Debezium (an open-source change data capture platform) which captures changes from database change log events. We used the Kafka Connect framework, which needs the source and sink connectors to send the database change events to Kafka for consuming the events downstream. We integrated Kafka Connect with Azure Event Hubs instead of utilizing a Kafka cluster on Azure. Debezium connectors are used as source connectors within the Kafka Connect to stream change data records into Azure Event Hubs.

The events from Azure Event Hubs were consumed using Azure Functions which are executed using Event Hub triggers. This Event Hubs trigger automatically executes the Azure Function whenever new events/records are pushed to the Event Hubs. At the time of building this solution, Azure Functions did not have any supported binding for connecting with Azure SQL. To overcome this issue, we used the pyodbc library within Azure Functions to connect with Azure SQL.

The high-level steps are as follows:

• Installation, configuration, and testing of Kafka Connect
• Configuration of DB2 on Linux for change data capture by using ASN (abstract syntax notation). This requires an IIDR license (IBM InfoSphere Data Replication)
• Download and extract the Debezium JAR files into the Kafka Connect environment and perform other required setup such as JDBC drivers
• Creation of Azure Event Hub Namespace and Event Hub
• Integration of Event Hub with Kafka
• Creation of Azure SQL database (if it does not already exist) or use existing one
• Creation of Azure Function App (we used Python for this)
• Integration of all components Event Hubs (which was already connected with Kafka topic on-premise), Azure Functions, and Azure SQL
• Configuration of Kafka Connect for Event Hub
• Testing of end-to-end data replication pipeline

We documented the solution in detail, including the design, installation steps, configuration settings, and python code.

XTIVIA mentored the client on different change data capture options with their pros and cons:

• Last-modified-timestamp-based SQL queries
• Full data comparison between source and target (difference based)
• SQL triggers at the source
• Database change log-based change data capture

XTIVIA assisted the client with evaluating the capabilities of HVR and Precisely CDC products.

BUSINESS RESULT

Our client realized the following benefits:

• Readily available and configurable real-time data replication solution for Azure SQL or Azure Synapse as a target
• The client obtained detailed knowledge of different change data capture options with their pros and cons

XTIVIA needs the contact information you provide to us to contact you about our products and services. You may unsubscribe from these communications at anytime, read our Privacy Policy here.