An Azure Data Factory copy of an 800 GB on-premises SQL Server table into ADLS Gen2 through a self-hosted integration runtime takes 30 hours. The monitoring view shows one query reading the whole table and a single output file. How do you make it fast?

The copy runs as one query on one thread writing one file, so nothing runs in parallel. Turn on the SQL Server connector's partitioned copy (physical partitions, or a dynamic range on the key with explicit bounds), set parallelCopies to match what the source and the integration runtime can take, write to a folder so each partition becomes its own file, and check the network first: it sets the ceiling no setting can lift.

1. Find the ceiling before tuning

Copy throughput is bounded by the slowest of the source, the sink and the network between them. Microsoft's own table: 1 TB over 100 Mbps takes about 23 hours, over 1 Gbps about 2.3 hours. If the office link to Azure is 100 Mbps, 800 GB takes about 19 hours at best whatever you do in Data Factory; you would need a faster link, compression (below) or a smaller copy.

In the copy activity's monitoring details, see where time goes: reading from the source, transferring, or writing to the sink.

2. Read in parallel

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

• Physical partitions: if the table is partitioned in SQL Server, choose "Physical partitions of table" and the copy reads them in parallel.
• Dynamic range: otherwise, split on an evenly spread integer or date column, usually the primary key. Always give the bounds: without them the copy runs MIN and MAX first, which can itself take long on a huge table. The bounds set the stride only; rows outside them are still copied, in the first and last partitions.
• With a custom query, include ?DfDynamicRangePartitionCondition in the WHERE clause.

3. Set the parallelism the systems can take

parallelCopies is the most threads the copy uses, counted across all runtime nodes. Each one is a query on the production SQL Server, so raise it in steps (4, 8, 16) and watch the server's CPU and I/O and the runtime machine. Run it outside business hours if the source serves an application.

4. Strengthen the self-hosted runtime

• Put it on a dedicated machine, not on the SQL Server itself.
• Scale up (more CPU and memory) while it is the bottleneck, or out to as many as 4 nodes.
• Over a slow link, staged copy compresses data on premises before sending it to a staging store in Azure, which reduces what crosses the network.

5. Write many files

Point the sink dataset at a folder with Parquet format, not a single file name. Each partition writes its own file in parallel, and Spark reads many files better than one 800 GB file.

6. Do not copy 800 GB every night

After the first full load, copy only changes: a watermark on a modified_at column or SQL Server change tracking, so the nightly copy is minutes, not hours.

What interviewers want: finding the bottleneck (network, source, runtime, sink) before tuning, partitioned reads with bounds, parallelCopies sized to the source's capacity, a dedicated and scaled self-hosted runtime, staged copy over slow links, folder output, and incremental loads after the first copy.