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.