Hana SDI Realtime Adapter for Azure SQL DB (and SQL Server)

[werner_daehn]

A customer requested a Hana SDI adapter to replicate Microsoft Azure DB data to SAP Hana in realtime. In case you are in need of the same, feel free to contact me.

While SDI has an adapter for MS SQL Server, its driver is not compatible with the Azure SQL Server and even if, it needs access to the transaction log files which no user has.

Therefore we built a new SDI adapter using the official Microsoft technologies for change data capture, Change Tracking it is called.

 

Capabilities

• Supports primary any standby databases: Queries and initial loads are then executed against the standby database and realtime change data capture happens via the primary database. If no standby database is configured, all queries go against the primary database.


• Transactional consistency: Using snapshot isolation transactional consistency is preserved in Hana as well. What was one transaction in the source remains within one transaction in Hana.


• Supports all data types: Special care has been taken to support all SQL Server data types and to map them to the correct Hana data types.


• Follows the SDI architecture to the letter: No information is stored in SQL Server, SDI provides the restart points in case of error recovery and the such. Hence it does not have limits like only one SDI agent connected to a single SQL Server database.


• Supports Azure and on-Prem SQL Server databases.


• Pushdown is supported but basics only: Where clauses, projections and everything else needed for efficient initial loads but e.g. no pushdown of functions.

[Sefan_Linders]

Hi Werner,

Why did you base it on change tracking and not on triggers? Less impact on the source / no need for full change history? SDI has recently added trigger support for some formerly log-based only databases.

Regards,

Sefan

[lbreddemann]

Hmm... why does the change tracking create more impact than triggers? From what I understand of the documentation, the mechanics seem very similar (store the primary keys of changed records in some other data structure). What's making the triggers the better option here?

Cheers,

Lars

[werner_daehn]

My impression as well (like lbreddemann), it has virtually no negative impact on the operational system performance. And from the consumer side looks and feels like a trigger based solution except you do not have to deal with maintaining the triggers, make sure the log tables are emptied once a while, no log tables to be created, benefit from internal optimizations, etc.

I have been quite impressed with the feature. Well done, Microsoft.

[former_member675268]

Hi Werner,

Thanks for your post.

I would like to know if this SDI adapter or any other  supports replication from HANA DB to Azure DB .

[werner_daehn]

That would need two functionalities:

• Identify changes in Hana


• Insert/update/delete/truncate of virtual tables

Both exists only in parts. You cannot create a remote subscription in a Hana table, it has to be a virtual table. So you would need to configure in Hana a virtual table that points to itself.

And while DML operations on virtual tables are possible, SDA/SDI has not been optimized enough for that.

And finally, the main advantage of SDI to have batch/replication/datafederation in one place would not be utilized.

Therefore I use other solutions for a use cases like that.

[former_member675268]

Thanks ,

My search is on then. I could not find any SAP tool for this case.

[werner_daehn]

One option is https://www.hvr-software.com/product/

The other option would be a trigger based solution to capture the changes. If you are okay with using Apache Kafka as a middleware, we can talk.

https://rtdi.io/software/big-data-connectors/

[brian_strandby]

Hi Werner,

 

Will this azure sql adapter also be available via the data provisioning agent? or how do you actually aquire it?

 

Regards

[werner_daehn]

Yes, it is installed and deployed as any other sdi adapter via the dpagent.

[brian_strandby]

Oh great. Is it provided with the newest version of dpagent from SAP download?

[werner_daehn]

I provide a jar file (and the source code) which you import via the dpagent. Just like other adapters are added.

Can you contact me via my company email werner.daehn and the company name rtdi.io?

[Sefan_Linders]

Somehow I missed your reply and only now I'm reading it as I was searching for something else. For what it's worth: I actually didn't mean to say triggers would have less impact than your solution, instead I meant to ask "did you choose this solution because it has less impact on the source than triggers"? And you already answered that question ;-).

[Sefan_Linders]

As I just replied to Werner (a little late...), I didn't mean to say triggers are the better option.

[Private_Member_51374]

Hi Werner,

 

Would it be possible to share this adapter? My customer is looking for exactly this use case.

[chonegger]

Hi Werner,

we have exactly the same scenario and are interested in your adapter.

Cheers,
Corina

[werner_daehn]

Please send me an email and I add you to the github repository.

[bob_gallo58]

Hello, I have a customer who is looking to get access to the AzureSQLDB adapter.
I see in one of the comments that the Adapter is in GitHub.
Thank you

[werner_daehn]

Please send me an email with your github account name and I add you to the repository.

[bob_gallo58]

Thank you I will pass this info on to the customer.

[former_member308979]

Hello Mr. Werner

 

We are looking for AZURE SQL DB Adapter, Can you please share the location of files and steps to install it in BW/4 HANA 2.0 system

 

 

Regards

Sundar.C

[werner_daehn]

Please send me an email with your github.com account.

[former_member308979]

Hello Werner

 

my email is : sundar.c79@gmail.com

github account : sundarc79

 

Thanks in advance.

Sundar.C

[david_alzate13]

Hello wdaehn ,

Thanks for the information. based on your blog I would like to request you the access to the github repository to analyze the driver. Do you know which license does this drive has ? Anyhow, thanks for sharing the information.

 

My github repository is DavidAlzateOcampo (David Alzate) · GitHub

 

Thanks beforehand,

David Alzate