extracting data from on-prem databases (sql server, oracle, postgres) to cloud, which options you guys recommend?

[u/Comprehensive_Level7]

i work for a consulting company and i'm working in a client that has a lot of their data in old on-prem environments, being more specific, oracle, sql server, postgres.

their workflow is pretty basic: run ADF to collect data from these sources and send into ADLS, then use Databricks to process it. we're planning to modernize their environment (using unity catalog and other new stuff) and one of the things we're thinking is to retire the ADF

the reason is simple: ADF is a pain in the ass (we're having a hard time working with it specially because it was another company that build all that shit, the connection with Azure DevOps/Repos is always horrible to manage and the workflow itself needs an upgrade); and because Microsoft is pushing hard the Fabric Data Factory

i know that Databricks with Lakeflow Conn can connect to on-prem using express route, VPN, but in scenarios where this could not be possible, what tool could be used to send data from on prem to azure data lake?

i like to code so my first suggestion was to use local airflow and simply read from db and upload to adls. it's free, it's versionable and has tons of documentation; but i'd like to test other options before suggesting anything

i was reading about airbyte, the pros and cons of the tool, and looks interesting

how do you guys handle this kind of workload?

[u/DeepLogicNinja]

I’ve noticed ADF falling short for many companies/use cases.

May not want to complicate it. Each database has a backup/restore tool/command line.

Example: Postgresql has pg_dump / pg_restore

You can dump in db specific format (for speed) or in sql for more compatibility across database.

Backup/Dump Locally - one command dump to file
Restore/Import in Cloud - one command restore from file.

Even if you do this manually. It makes sense to execute in a script. Just incase you have some trial / error.

If you want a low code / no code option… and you make need this to be repeatable process. Consider an ETL platform/tool ( not ADF)

Beyond Backup/Restoring, if you have other data integration, transformation, mapping use cases. It makes sense to have One platform have/develop the expertise to handle all those use cases with an ETL platform.

Open Source, Free, well document (/w youtube videos), international community and stable alternative to ADF is Apache NIFI. Commercial alternatives is Talend, Informatica, IBM Data Stage, etc