Synchronizing databases through BPEL services

Introduction

This blog post is about how to synchronize two databases through BPEL, focusing on transaction, rollback and fault handling.

During a project, I’ve encountered a situation where we wanted to migrate from an old database to a new one. However, in order to gradually move external systems from the old to the new database, it was required that both databases would be kept in sync for a limited amount of time. Apart from the obvious database tools, for example Oracle Golden Gate, this can be done through the service layer as well and that’s what this article is about. I will explain how I have done it with a strong focus on fault handling, since that’s the most complicated part of the deal. In this case, since keeping things in sync is what we’re aiming for, a rollback needs to be performed on one database when the other fails to process the update.

One of the requirements is that it should be easy to throw the synchronization code away, as it has no place in our future plans. Another requirement is that the service layer should return faults in a decent manner.

Preparation

In order to enable out-of-the-box rollback functionality, make sure that the data sources connecting to both databases are XA enabled. As there is plenty of information about this subject, I will not get into detail about it in this blog.

Now we will be developing two services:

• SalesOrderBusinessService: a BPEL process that receives messages from a BPM process and forwards them to our integration service
• UpdateSalesOrderIntegrationService: a BPEL process that receives messages from SalesOrderBusinessService and updates two databases through adapters

We need to make sure that both services have a fault specified in their wsdl operation in order to return the recoverable fault.

<wsdl:message name=”UpdateSalesOrderRequestMessage”>
<wsdl:part name=”UpdateSalesOrderRequest” element=”cdm:UpdateSalesOrderEBM”/>
</wsdl:message>

<wsdl:message name=”UpdateSalesOrderResponseMessage”>
<wsdl:part name=”UpdateSalesOrderResponse” element=”hdr:ServiceResult”/>
</wsdl:message>

<wsdl:message name=”UpdateSalesOrderFaultMessage”>
<wsdl:part name=”UpdateSalesOrderFault” element=”hdr:ErrorMessages”/>
</wsdl:message>

<wsdl:portType name=”SalesOrderBusinessService_ptt”>
<wsdl:operation name=”updateSalesOrder”>
<wsdl:input message=”tns:UpdateSalesOrderRequestMessage”/>
<wsdl:output message=”tns:UpdateSalesOrderResponseMessage”/>
<wsdl:fault name=”TechnicalFault” message=”tns:UpdateSalesOrderFaultMessage”/>
</wsdl:operation>
</wsdl:portType>

Development

Once the data sources and wsdl definitions are in place, we can start developing our BPEL services. Let’s start with UpdateSalesOrderIntegrationService. It will be a SOA composite, containing a BPEL process, a web service and two database adapters. In the end it should look like this:

While we can create the database adapters with default settings, we have to make an adjustment to the BPEL process: the transaction will have to be changed from “required” to “requiresNew”. See picture below:

The UpdateSalesOrderBPEL process will first update the new database and, if the update is successful, the old database too. This can easily be achieved when the database procedure returns, for example, OK or NOK (with a business reason) to let us know about the processing result. If the update in the old database is not successful, however, we need to throw a fault to rollback the update in the first database. This is out-of-the-box functionality, but we need to be aware that the rollback will only take place when the entire transaction fails. This means that we can’t catch any faults in this BPEL process, because then it will be considered a successful transaction. Also, this is why we set the transaction property to “requiresNew”: in SalesOrderBusinessService we do want to catch faults, but if UpdateSalesOrderIntegrationService is in the same transaction, the transaction will still be considered successful and we will not get our rollback. In the end, the BPEL process should look something like this, between the “receive” and “reply” activities:

The throw activity goes as follows and we can either assign error information from the database procedure or our own information to the faultVariable:

The next step is to create SalesOrderBusinessService. The composite should look like this and we can keep the transaction property for the BPEL process at “required”:

Our BPEL process will look like this:

As you can see, the main flow is very basic and we don’t need to do anything out of the ordinary here. The interesting part is the Catch, where the Technical Fault coming from the IntegrationService will be handled. In this case, we can simply assign the fault message to the fault message of the Business Service and reply the fault to the requestor. Consequently, the requestor can, for example, re-send the message once the problem in the old database has been resolved. If there is a business problem (NOK) in the new database, it should be handled as a business problem and no SOAP fault will be returned. Should there be any other technical faults, like a database being down, the CatchAll will handle those as usual.

That’s it, we’re done. Now, once the old database can be shutdown, it will be fairly easy to remove the code: just throw away the first “CheckResult” component and the database adapter from UpdateSalesOrderIntegrationService, as well as the Catch activity in the Business Service.

Keep in mind the most important parts of the deal:

1. XA data sources are required
2. Integration Service should have its transaction property at “requiresNew”
3. Integration Service cannot have any fault handling
4. Business Service should handle specific faults from the Integration Service
5. Make sure that the temporary code can be easily removed