Forum Discussion

going_grey's avatar
going_grey
Frequent Visitor
5 months ago
Solved

Stream On-Prem SQL Server Data

Hi   We want to stream data from an on-prem SQL Server database (cdc-enabled). We've initially looked at using an Evenstream to do this, but there isn't an available on-prem data source connector t...
one lake
Real-Time hub
  • MJParikh's avatar
    MJParikh
    5 months ago

    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

MJParikh's avatar
MJParikh
Super User
5 months ago

Hi going_grey,

 

You are running into a real limitation, not a misconfiguration.

Fabric currently does not support your intended workflow: CDC → mirrored database → Spark streaming.

Mirrored databases land data as Delta tables. Those tables do not expose a true change feed in a way Spark Structured Streaming expects.

When you see:

DELTA_SOURCE_TABLE_IGNORE_CHANGES

It means the engine detected updates or merges in the source Delta table. Streaming reads in Fabric today expect append-only behavior unless change data feed is explicitly supported, which mirrored databases do not provide yet.

So your current approach will continue to fail.

 

Below are some options you can try.

Option 1. Go direct, skip mirroring
This is the most stable pattern today.

  • Use Azure Data Factory or Fabric Data Pipeline

  • Use SQL Server CDC as the source

  • Land data into Lakehouse (Delta) in append mode

  • Then use Eventstream or Spark on top of that

This avoids the Delta “change” limitation entirely.

Option 2. Use staging with append-only pattern
If you must stay close to Fabric:

  • Pull CDC changes into a staging table (append-only)

  • Do not use MERGE in that layer

  • Stream from that staging Delta table

You trade some modeling elegance for streaming compatibility.

Option 3. Event-based approach (closer to real-time)
For near real-time:

  • Use SQL Server CDC → Azure Event Hubs (via Debezium or custom capture)

  • Ingest into Fabric Eventstream

  • Land into Lakehouse

This is the only pattern that behaves like true streaming today.

Option 4. Mirroring, but batch, not streaming
If you want to keep the mirrored database:

  • Treat it as batch or micro-batch

  • Use scheduled Spark jobs, not streaming

  • Read latest snapshot or incremental logic manually

No continuous streaming.

 

Key takeaway
Mirrored database ≠ streaming source. It is optimized for replication and analytics, not CDC streaming semantics.

If your goal is real-time pipelines, move upstream of mirroring. Maintain mirroring while accepting batch processing if simplicity is your main objective.



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

going_grey's avatar
going_grey
Frequent Visitor
5 months ago

Hi MJParikh 

Thank you for providing all of these options. I'm looking into Option 1:

I'm using a Copy Job to ingest CDC tables into a Lakehouse using the Merge write method (this is only available method for CDC tables). I guess I'll have to configure the most frequent schedule here (without spiking the CU too much). I haven't tested this yet.


You've mentioned this under Option 1:

"Then use Eventstream or Spark on top of that"

 

How can I use a Lakehouse as an Evenstream data source? Unless I'm misunderstanding?

  • MJParikh's avatar
    MJParikh
    Super User
    5 months ago

    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