Data Mirroring/Replication/changes between SAP and SQL Server through PO/PI

[Former Member]

My requirement is to replicate the data changes(Insert/Update/Delete) of few SAP tables to SQL server through PO/PI and vice versa. (almost real time/ frequency 1 or 2 minutes)

I know JDBC adapter is one option to connect to SQL server.

Following are the challenges

1. How to track the data changes of SAP table (Insert/Update/Delete) in PO/PI to send it SQL server?

2. How to track the data changes of SQL Server table (Insert/Update/Delete) in PO/PI to send it SAP system tables?

3. Please help me on performance problems.

Any help would be appreciated.

Thanks for the help.

Regards,

Tushar

[weberpat · Accepted solution]

SAP PI/PO is not the right tool for what you are trying to accomplish. I suggest you take a look into SAP SLT, which allows trigger-based replication of SAP database changes to any SAP-supported database, irrespective of whether or not the target DB is connected to an SAP system.

[Former Member · Accepted solution]

Thank you for the reply Patrick. SAP SLT needs licensing and additional server maintenance, that would lead to more cost/budget. therefore we are not considering this option. We have only PO/PI to read/write data from/to SQL Server DB. Please suggest if you have any other approach which can be fit in with PO/PI.

[weberpat · Accepted solution]

Evgeniy already suggested to implement logic in your backend that keeps track of the changes and periodically triggers a transfer to PI. This could be implemented in a variety of different ways. For instance you could set up a program that periodically scans your table for modified records since the time it last ran successully and sends the delta to a proxy.

What you will have to be really, really careful about is the volume that you are creating with this type of setup. If you have a large table with a lot of changes, you will be putting a lot of strain both on your backend and your PI system. Also PI is notoriously bad at processing large messages, so if your selection could yield a very large result set, you might need to break it down into more manageable chunks before sending it out.

I can only repeat:
PI is not the right tool for database replication - no matter if real-time, near-real-time or period bulk loads. PI is a message oriented middleware that is supposed to process large volumes of small to medium size messages rather than bulk data loads.

[weberpat · Accepted solution]

Maybe one additional thing:

SLT is not the only solution that offers trigger-based replication from SAP to another database. There are other products out there that supposedly are significantly cheaper. One client of mine once experimented with the Simplement Data Liberator, which is based on HVR technology. In principle this worked without too many performance issues for small to medium scenarios but there was a noticeable impact for very high volume scenarios that we did not observe with SLT.

[Former Member · Accepted solution]

Thank you for the reply. That was helpful. Thanks a lot Patrick.

[former_member190293]

Hi Tushar!

In case the changes should be tracked in backend tables the straight way is to track it in that systems itself and to trigger appropriate methods to initiate data transfers via PI.

Regards, Evgeniy.

[Former Member]

Trigger for SAP tables and SQL server DB tables to track the changes. I understand that Trigger will have performance problems if there is a frequent update with high volume. Please let me know if you have any other approach. Thanks for the help.

[former_member190293]

Hi Tushar!

I think there are no other possible approaches. You use either SLT as Patrick has already suggested or trigger update events from backend's side. PI itself can't track the changes, moreover, it shouldn't do it.

You can use polling mechanism of JDBC sender adapter, for example, to select data from database tables using some trigger field value but PI can't set that trigger value on any change happening. This trigger is set by backend system when data is changed.

On SAP side in most cases data transfer is initiated by backend system. It checks for data changes, composes messages and sends it using appropriate communication channel (RFC, ABAP Proxy, IDOCs etc.).

Regards, Evgeniy.

[Former Member]

Thank you for the reply. That was helpful. Thanks a lot Evgeniy.