Stream On-Prem SQL Server Data

Hi going_grey,

You are correct, and sorry for your confusion, which is valid. Let me clarify this cleanly.

A Lakehouse cannot be used as a source for Eventstream.

Eventstream is designed for event-driven, push-based sources such as:

• Azure Event Hubs

• Kafka

• IoT Hub

A Lakehouse, on the other hand, is:

• Delta-based

• Pull-oriented

• Supports updates and merges

So there is no supported pattern like:
Lakehouse → Eventstream

Where the confusion comes from

In Option 1, the flow should be interpreted like this:

SQL Server (CDC) → Ingestion → Lakehouse → Processing (Spark / downstream)

Not:

Lakehouse → Eventstream

Once data lands in the Lakehouse, you are already past the streaming ingestion layer.

You mentioned using Copy Job with MERGE for CDC tables.

This introduces a constraint:

• MERGE creates updates in Delta tables

• Spark Structured Streaming expects append-only sources

• This leads to errors like
  DELTA_SOURCE_TABLE_IGNORE_CHANGES

So even without mirroring, MERGE prevents true streaming.

Option A. Micro-batch processing (most stable)

• Keep your current ingestion (CDC → Lakehouse via Copy Job)

• Run a scheduled Spark notebook

• Process incrementally using:

  • timestamp

  • LSN

  • watermark logic

This is the most reliable pattern in Fabric today.

Option B. Append-only landing (stream-compatible)

If streaming is important:

• Land CDC data as append-only

• Include operation type (Insert / Update / Delete)

• Avoid MERGE in the landing layer

Then:

• Spark readStream works correctly

• No Delta change conflicts

You handle deduplication downstream.

Option C. True real-time streaming (Eventstream)

If you need near real-time:

• SQL Server CDC → Event Hub (Debezium or custom capture)

• Event Hub → Eventstream

• Eventstream → Lakehouse

This pattern:

• Preserves event semantics

• Supports continuous streaming

• Avoids Delta update limitations

Final takeaway

• A mirrored database is not a streaming source

• Lakehouse is not an Eventstream source

• MERGE-based CDC ingestion blocks streaming scenarios

Let me know how it goes. I wish this would help to resolve your question.


Thank you!
Proud to be a Super User!
📩 Need more help?
✔️ Don’t forget to Accept as Solution if this guidance worked for you.
💛 Your Like motivates me to keep helping