Fabric Copy Job Timeout

Hi joel_delima​,

One thing about this setup that hasn't come up yet: both your source and your target are on-premises. For the initial load, every one of those 100M+ rows travels from the SQL 2008 server through the gateway up to the Fabric service, then back down through the gateway to SQL 2025:

SQL 2008 (on-prem) -> gateway -> Fabric -> gateway -> SQL 2025 (on-prem)

That round trip is the most likely reason a single long-running read/write session hits a resource timeout around 40 minutes, especially on the first full copy. The incremental runs move far less data, which is why they are fine.

I would split the problem in two:

initial load   -> make it smaller per run, or keep it on-prem
ongoing CDC    -> keep using the Copy job

Option A: stay in Fabric, but partition the initial load. Use a pipeline Copy activity for the first load with dynamic range partitioning, so it runs as several shorter, parallel reads instead of one long session:

"source": {
  "type": "SqlServerSource",
  "partitionOption": "DynamicRange",
  "partitionSettings": {
    "partitionColumnName": "Id",
    "partitionLowerBound": "1",
    "partitionUpperBound": "120000000"
  }
},
"parallelCopies": 8

Pick an indexed integer or date column for partitionColumnName, and keep parallelCopies modest so the gateway and the 2008 server aren't overloaded.

Option B: seed the big tables on-prem. Since both servers sit on your network, a native bulk copy in key-range chunks avoids the cloud round trip entirely, and each chunk is short enough to restart on its own:

rem export a 10M-row range from SQL 2008 (native format)
bcp "SELECT * FROM SourceDb.dbo.BigTable WHERE Id BETWEEN 1 AND 10000000" ^
    queryout BigTable_001.dat -S SQL2008 -T -n

rem load it into SQL 2025 in 100k-row batches
bcp TargetDb.dbo.BigTable in BigTable_001.dat ^
    -S SQL2025 -T -n -b 100000 -h "TABLOCK"

After seeding, check how your Copy job handles a target that already has data before switching it on for those tables. If it insists on a full initial copy, Option A is the safer route.

In both cases, watch the gateway machine's CPU and memory during the run. The gateway does the format conversion for both legs here, so it works twice as hard as in a normal cloud-destination load.

So my overall approach would be:

recognize the on-prem -> cloud -> on-prem round trip
-> initial load: partitioned Copy activity, or on-prem bcp seed
-> ongoing changes: Copy job with CDC as today
-> support case with run ID if chunked loads still time out

That keeps the large first load out of a single long-running session, which is where this timeout comes from.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.