Forum Discussion

joel_delima's avatar
joel_delima
Regular Visitor
23 days ago
Solved

Fabric Copy Job Timeout

I have a legacy SQL 2008 server (CDC enabled) and a SQL Server 2025, both have fabric connections and use an on prem data gateway. I'm using the Fabric copy job to incrementally transfer data from th...
data engineering
data pipeline
  • joel_delima's avatar
    joel_delima
    16 days ago

    Thanks, I agree with your first statement but its a business requirement that needs to get done in a very short timeframe, and fabric's copy job/cdc feature proved to be the quickest and most efficient option.

    I tried using a Fabric pipeline to seed the initial data onto sql2025 then enable cdc to only capture the deltas, however CDC just tries to copy the full dataset and runs into that same timeout error.

    I've resolved to using an incremental pipeline instead using a watermark column to migrate new rows.

tayloramy's avatar
tayloramy
Icon for Super User rankSuper User
23 days ago

Hi joel_delima​,

Fabric is not exactly intended to be used to move data from one on prem system to another on prem system. 

When this big table is running, what do your resources on the gateway server look like? Are you maxing out your CPU and memory? If so you may need to upgrade your gateway server to add more power.

What you could also try is to split the job into two steps, one to ingest the data from the 2008 SQL into a Fabric datastore like a SQL Database, then a second job to take it from the Fabric database back to your on prem SQL 2025 server. 

If your SQL Servers are on the same network, you can also skip fabric directly and create a linked server or external table on the 2025 server that points to the 2008 server and use cross server queries to load the data, this is the approach I would take first. 

  • joel_delima's avatar
    joel_delima
    Regular Visitor
    16 days ago

    Thanks, I agree with your first statement but its a business requirement that needs to get done in a very short timeframe, and fabric's copy job/cdc feature proved to be the quickest and most efficient option.

    I tried using a Fabric pipeline to seed the initial data onto sql2025 then enable cdc to only capture the deltas, however CDC just tries to copy the full dataset and runs into that same timeout error.

    I've resolved to using an incremental pipeline instead using a watermark column to migrate new rows.

    • tayloramy's avatar
      tayloramy
      Icon for Super User rankSuper User
      16 days ago

      Hi joel_delima​ 

      Did you check what the resources on your gateway look like when you are trying the full load? And did you try my staging it in Fabric idea?