We have an operational mySQL DB running on AWS for a transactional system and an Azure SQL DB for reporting with PowerBI. Now I’d like to regularly (e.g. every night) do an update of certain tables from the mySQL DB to the Azure SQL DB.
I found this description on how to do incremental copies using Azure Data Factory, however the alternatives don’t seem feasible to me:
Delta data loading from database by using a watermark requires adding watermark columns to the source DB, but I don’t want to make changes to the operational DB because it is managed and regularly updated by the transactional system.
Delta data loading from SQL DB by using the Change Tracking technology seems to require an SQL Server DB as the source if I understand this correctly.
The remaining two alternatives apply only to updates from files, not DBs, to my understanding.
Are there other feasible alternatives based on the described conditions? They don’t necessarily need to involve Azure Data Factory, however the updates should run completely automated in the cloud.
Maybe a non-incremental update (i.e. full replacement of the target DB tables every time) would be an option too, but I’m afraid that this would lead to high costs on the Azure SQL Server side – please share any experience on that as well, if available.
I have a chance to work with Log Shipping (Copy logs of each transaction and recover them at another replication DB) for SQL server. However, I think there are options for MySQL as well.This StackOverflow answer will help to configure log shipping in MySQL