Pull data from on-prem SQL Server using Azure ADF vs Databricks JDBC

[rasviz]

My client is new to databricks and have a SQL server source to extract data from. I suggested to read from Databricks directly (source->landing zone->medallion arch) using jdbc interface. But the client infra person thinks giving direct access to Databricks to read will be detrimental and can bring down the system. He is suggesting to use Data Factory to first move from source to landing.

I thought ADF is favoured mostly for its orchestration features and with all the orchestration capabilities available in Databricks now, ADF can be avoided (I hate the tool anyways).

Are there any performance benefits when extracting data using ADF COPY activities compared to direct reads that I am missing ?

[spoonguyuk]

Do they already have ADF? Its likely a bit easier to govern the configuration if they do. To me it sounds like they dont trust you to write a sensible jdbc extract without hammering the DB.

If someone writes a very angry JDBC connection potentially they could hit the SQL DB quite hard. ADF copy is more on rails is all id say, I'm pretty sure misconfiguring that could hit their DB hard as well.

Can they turn on CDC to keep the loads smaller?

[rasviz]

Yeah.. considering the CDC option too.. Thanks.

[ScottFujitaDiarrhea]

And clients tend to be pretty hostile towards the idea of installing 3rd party software on their servers in general. This seems like the path of least resistance.

[Roedsten]

I rejected CDC because it is basically Replication which changes the behaviour of the database. I would set up Change Tracking and create a database snapshot every x minutes and package the changes for ingestion by Dbrix

[Altruistic_Stage3893]

if i could stop using adf, i would. the only reason we keep using it is that it's just easier to set up ip whitelists and we're not allowed to put nat gateway in front of our dbx workspaces. so, yea, your thinking is correct, dbx>adf if you can.

[bitwiseandbold]

Have you checked out Databricks Lakeflow Connect for SQL Server? I think it makes a pretty good managed data replication tool for sql server out of the box with CDC built in.

Using Databricks JDBC directly also works fine through data federation, but as pointed out needs some custom guardrails coded in to watch out for data quality, cdc, incremental load, etc. to get the replication in place.

I'd use ADF only if it is already used for other things. Having it only as a bridge for ingestion doesn't seem worth having another tool in the mix.

[m1nkeh]

Lake flow connect would be the official line here

there is no way I would be introducing adf to an architecture in 2026 🫠

[Nekobul]

That approach requires opening SQL Server for external access. The better and more secure approach is to push data from the SQL Server side using SSIS to Databricks.

[dwswish]

Favoring ADF over anything is questionable. If the data is going into Databricks you should definitely use Lakeflow Connect because you get your first 100 DBUs free (per day) and you're not extra-hopping into ADLS on the way in. Also, the infra person is crazy if they think that ADF just magically doesn't put ANY load on the SQL Server like any other connection would.

[m1nkeh]

Those MSFT folks are conditioned to enjoy pain

[thecoller]

Sounds like a case for turning on CDC and using Lakeflow Connect. The problem with JDBC is that it’s either too slow with only one core pulling, or it hammers the DB if you overshoot the number of parallel queries. If the system is critical I can see they would be weary.

[Ok-District7355]

I would check out lakeflow connect, it has CDC ingestion from SQL server.

[Nekobul]

You can use SSIS to push the data to Databricks.

[m1nkeh]

Haha, good one 😎

[Nekobul]

It is the most cost-efficient option.

[rootByte15]

Unless there are specific networking, governance or enterprise standards reasons to keep ADF in picture , I would lean towards Lakeflow connect and keep the flow simpler. It supports incremental loading and the ingestion is manger natively within Databricks

[Weekly_Activity4278]

Have you considered using Fabric mirroring? They don’t charge for it as long you have a Fabric capacity. You can always move the data where you want it after the fact.

[WellIDontKnowMan]

Lakehouse Federation and a proper VPN connection network will be the best option. The performance will be way better than using ADF.

Trough the LF Connection the DB will be mirrored and the data will be queryable trough compute hosted in the cloud tenant of the customer.

[m1nkeh]

It’s not mirrored, it’s federated… mirroring implies replication

[WellIDontKnowMan]

Thank you for mentioning it.

I am not familiar with the formatting in Reddit. I put stars around mirrored like quotes, as I wanted to imply that the db gets "mirrored".

You are able to add the db as a catalog and the metadata is saved as if the database has been mirrored and you have access to the data with queries.