Copying Data from On-Prem SQL to Azure with ADF

Azure Data Factory runs in Azure. Your SQL Server runs in a server room, behind a firewall that exists precisely to stop the internet reaching it. Copying from an on-premises SQL Server to Azure therefore needs one extra piece: software inside your network that can see the database and talk to Azure. That piece is the self-hosted integration runtime.

The definitions in this guide are written from Microsoft's documentation and have not been deployed.

How the hybrid copy works

The design avoids opening the network to inbound traffic. Microsoft describes the flow in four steps (Microsoft Learn: self-hosted integration runtime):

1. You create a self-hosted integration runtime in the data factory, install it on a machine in your network, and create a linked service for the database that names it.
2. The runtime encrypts the database credentials with Windows DPAPI and stores them locally.
3. Data Factory schedules work through a control channel. The service queues a request, and the runtime starts the job after it polls the queue. The runtime calls out; nothing calls in.
4. The runtime reads from the database and writes to cloud storage directly, over HTTPS.

Two things follow. The data does not pass through the Data Factory service on its way to storage; the runtime sends it. And the machine you install the runtime on is doing the real work, so its capacity matters. Integration runtimes in ADF covers the runtime types.

The machine

Three further points from the documentation save trouble later:

• Put it near the database, not on it. Microsoft recommends a different machine from the one hosting the data source, so the two do not compete for resources.
• Parquet needs Java. File creation happens on the runtime's machine, and writing Parquet, ORC or Avro requires a Java runtime, version 11, with the JAVA_HOME variable set. A copy to Parquet that fails immediately on a new runtime is usually this.
• Not beside the Power BI gateway. Both use port 443, and Microsoft advises against installing them on the same machine.

One instance can be installed per machine, and one runtime can serve many on-premises data sources.

The firewall

The runtime makes outbound connections on port 443. At the corporate firewall these destinations must be allowed:

The last row has a documented way round it. With staged copy, the runtime sends data to staging storage over HTTPS on port 443, and the load into Azure SQL happens from there, so port 1433 need not be opened (Microsoft Learn: performance features).

Inside the network, the runtime must reach SQL Server: TCP/IP enabled on the instance, remote connections allowed, and the instance's port, 1433 by default, open in the Windows Firewall of the database server.

Step by step

Microsoft's tutorial follows this order (Microsoft Learn: SQL Server to Blob storage tutorial):

1. Create the runtime. In the Manage hub, under Integration runtimes, choose New, then Self-Hosted. Give it a name.
2. Install and register. Download the installer to the chosen machine, run it, and paste one of the authentication keys shown in the portal. The runtime then shows as running.
3. Create the SQL Server linked service, selecting that runtime under Connect via integration runtime, and use Test connection.
4. Create the linked service for the destination, for example a storage account.
5. Create the datasets: the source table, and the destination folder and format.
6. Create a pipeline with a Copy activity, run it with Debug, then publish and add a trigger.

Test connection in step 3 runs from the runtime's machine. If it fails, the problem is between that machine and the database, not in Azure.

The linked service

{
  "name": "OnPremSalesDb",
  "properties": {
    "type": "SqlServer",
    "typeProperties": {
      "server": "sqlprod01.corp.example.com",
      "database": "Sales",
      "encrypt": "mandatory",
      "trustServerCertificate": false,
      "authenticationType": "SQL",
      "userName": "adf_reader",
      "password": {
        "type": "AzureKeyVaultSecret",
        "store": { "referenceName": "CorpKeyVault", "type": "LinkedServiceReference" },
        "secretName": "sales-db-reader"
      }
    },
    "connectVia": { "referenceName": "CorpSelfHostedIR", "type": "IntegrationRuntimeReference" }
  }
}

The property that makes this a hybrid connection is connectVia. Without it, the default Azure runtime is used, and it cannot see the server (Microsoft Learn: SQL Server connector). Linked services in Azure Data Factory covers the object in general.

Authentication. The connector supports SQL authentication and Windows authentication, where the user name takes the form of domain and user. Either way, use a dedicated account with read access to only the tables being copied.

Where the password lives. There are two documented choices: stored by Data Factory, in which case it is kept encrypted on the runtime's machine, or held in Azure Key Vault and fetched when an activity runs (Microsoft Learn: security considerations). Key Vault keeps the secret out of the factory's definition and lets it be rotated in one place. Azure Key Vault with Data Factory covers the set-up.

The Copy activity

{
  "name": "CopyOrdersToLake",
  "type": "Copy",
  "inputs": [ { "referenceName": "OnPremOrdersTable", "type": "DatasetReference" } ],
  "outputs": [ { "referenceName": "LakeOrdersParquet", "type": "DatasetReference" } ],
  "typeProperties": {
    "source": {
      "type": "SqlSource",
      "sqlReaderQuery": "SELECT OrderId, CustomerId, OrderDate, Amount FROM dbo.Orders WHERE OrderDate >= '2026-01-01'"
    },
    "sink": {
      "type": "ParquetSink",
      "storeSettings": { "type": "AzureBlobFSWriteSettings" }
    }
  }
}

Nothing in the activity mentions the runtime. It is used because the source dataset points at a linked service that names it. Select the columns you need in the query; every unneeded column is data pushed up a link that is usually the slowest part of the route.

Large tables and slow links

• Partition the read. For a big table, a partition option makes the activity run several range queries in parallel. See data partitioning strategies.
• Scale the runtime. If its CPU is high or memory low, add nodes; if the machine is idle while jobs queue, raise the concurrent job limit. DIUs do not apply to a self-hosted runtime. ADF performance tuning explains how to find the bottleneck.
• Compress across the link. Staged copy can compress data on the premises before it is sent.
• Stop copying everything. After the first full load, copy only what changed; see incremental data loading.

Availability

A single runtime machine is a single point of failure. A runtime can have up to four nodes: install the software on another machine and register it with the same authentication key. Jobs continue when a node is down for patching, and throughput improves. Before adding a node, remote access to intranet must be enabled on the first one.

When it fails

Other routes

A self-hosted runtime is the standard answer and not the only one. Microsoft's connector documentation notes that the managed virtual network integration runtime can reach an on-premises network through a private endpoint, without installing a self-hosted runtime. It needs private connectivity between Azure and the site.

A private circuit does not remove the need for a runtime inside the network: the guidance is to treat a data source behind ExpressRoute as on-premises all the same.

Azure Data Factory and hybrid data integration are covered in the Data Engineering course.

Sources

• Copy data from SQL Server to Blob storage using Azure portal (Microsoft Learn) — Microsoft Learn
• Create a self-hosted integration runtime (Microsoft Learn) — Microsoft Learn
• Copy and transform data to and from SQL Server (Microsoft Learn) — Microsoft Learn
• Security considerations for data movement in Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Copy activity performance optimization features (Microsoft Learn) — Microsoft Learn