Skip to Content
Technical Articles
Author's profile photo Werner Dähn

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

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.

Assigned tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sefan Linders
      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

      Author's profile photo Lars Breddemann
      Lars Breddemann

      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

      Author's profile photo Sefan Linders
      Sefan Linders

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

      Author's profile photo Werner Dähn
      Werner Dähn
      Blog Post Author

      My impression as well (like Lars Breddemann), 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.

       

       

      Author's profile photo Sefan Linders
      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 ;-).

      Author's profile photo kartik kumar
      kartik kumar

      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 .

      Author's profile photo Werner Dähn
      Werner Dähn
      Blog Post Author

      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.

      Author's profile photo kartik kumar
      kartik kumar

      Thanks ,

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

      Author's profile photo Werner Dähn
      Werner Dähn
      Blog Post Author

      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/

      Author's profile photo Brian Strandby
      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

      Author's profile photo Werner Dähn
      Werner Dähn
      Blog Post Author

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

      Author's profile photo Brian Strandby
      Brian Strandby

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

      Author's profile photo Werner Dähn
      Werner Dähn
      Blog Post Author

      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?

      Author's profile photo Frederik Van Gucht
      Frederik Van Gucht

      Hi Werner,

       

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

      Author's profile photo Corina Honegger
      Corina Honegger

      Hi Werner,

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

      Cheers,
      Corina

      Author's profile photo Werner Dähn
      Werner Dähn
      Blog Post Author

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