Self-hosted IR in Azure Synapse: how to configure

This tutorial explains how to configure a Self-hosted Integration Runtime (IR) in Azure Synapse to copy data from an on-premises SQL Server to an Azure Data Lake Storage Gen2. Configuring a Self-hosted IR in Azure Synapse allows you to move data across secure networks when it is not possible to expose databases directly to the cloud.

Prerequisites

• Azure account with permissions for the Synapse Workspace and ADLS Gen2.
• A Synapse Workspace with Contributor permissions to create Linked Services and Pipelines.
• On-premises machine (Windows) with access to the SQL Server and permissions to install software.
• Network ports open for the server where the Self-hosted IR will be installed (outbound HTTPS).
• Credentials to access the SQL Server and the ADLS Gen2 account.

Step 1: Create the Self-hosted Integration Runtime in Synapse

Open Synapse Studio and go to Manage > Integration runtimes. Create a new Integration Runtime of type Self-hosted. The step generates a registration key that will be used during the local installation.

{
  "name": "SelfHostedIR",
  "type": "SelfHosted",
  "description": "IR para ligar ao SQL on-premises"
}

Step 2: Install and register the IR on the on-premises machine

On the on-premises machine download the Integration Runtime installer (link available in Synapse Studio). Run the installer and, when prompted, paste the registration key created in Step 1. The agent will be registered and connected to your Synapse Workspace.

Step 3: Create Linked Services (SQL on-prem and ADLS Gen2)

Create a Linked Service for the on-premises SQL Server and another for ADLS Gen2. For the SQL Server Linked Service indicate that it uses the Self-hosted IR via connectVia. Minimal JSON example of the Linked Service for the SQL Server:

{
  "name": "LS_OnPrem_SqlServer",
  "properties": {
    "type": "SqlServer",
    "typeProperties": {
      "connectionString": "Server=ONPREM-SQL;Database=MyDb;User Id=myuser;Password=mypassword;"
    },
    "connectVia": {
      "referenceName": "SelfHostedIR",
      "type": "IntegrationRuntimeReference"
    }
  }
}

Minimal JSON example of the Linked Service for ADLS Gen2:

{
  "name": "LS_ADLS_Gen2",
  "properties": {
    "type": "AzureDataLakeStorageGen2",
    "typeProperties": {
      "url": "https://.dfs.core.windows.net"
    }
  }
}

Step 4: Create Datasets and Copy pipeline

Define a source Dataset that points to the SQL Server table and a sink Dataset that writes to an ADLS Gen2 container (for example CSV or Parquet). Then create a Pipeline with a Copy activity.

{
  "name": "Ds_OnPrem_SqlTable",
  "properties": {
    "linkedServiceName": { "referenceName": "LS_OnPrem_SqlServer", "type": "LinkedServiceReference" },
    "type": "SqlServerTable",
    "typeProperties": { "tableName": "dbo.MyTable" }
  }
}

{
  "name": "Ds_ADLS_Output",
  "properties": {
    "linkedServiceName": { "referenceName": "LS_ADLS_Gen2", "type": "LinkedServiceReference" },
    "type": "DelimitedText",
    "typeProperties": { "location": { "type": "AzureBlobFSLocation", "fileName": "MyTable.csv", "folderPath": "ingest/" } }
  }
}

{
  "name": "CopyOnPremToADLS",
  "properties": {
    "activities": [
      {
        "name": "CopyFromSqlToADLS",
        "type": "Copy",
        "inputs": [{ "referenceName": "Ds_OnPrem_SqlTable", "type": "DatasetReference" }],
        "outputs": [{ "referenceName": "Ds_ADLS_Output", "type": "DatasetReference" }],
        "typeProperties": {
          "source": { "type": "SqlSource" },
          "sink": { "type": "DelimitedTextSink", "storeSettings": {} }
        }
      }
    ]
  }
}

Note: in Synapse Studio you can create these artifacts through the graphical interface. The JSON is provided as an example for automation or ARM templates.

Step 5: Run, monitor and schedule

Run the pipeline manually in Synapse Studio to test. Then use Triggers to schedule executions (Schedule trigger) if you need recurring loads. Monitor in Monitor > Pipeline runs to see status, duration and any errors.

Verify the result

Confirm that the files were written to ADLS Gen2: use the Data hub feature in Synapse Studio or a Storage Explorer (or the Azure Portal) to check for the presence of MyTable.csv in /ingest/. Also check the pipeline log in Monitor for success messages and number of rows copied. If common errors occur (authentication, firewall, connection timeout), check the Self-hosted IR log on the local machine and whether the SQL Server allows remote connections.

Conclusion

You now have a Self-hosted Integration Runtime running with a Copy pipeline that moves data from an on-premises SQL Server to ADLS Gen2 via Azure Synapse. Useful next steps include: using efficient formats like Parquet, implementing compression or adding transformation activities in the pipeline. Tip: if you observe connection issues, confirm the system time and whether the agent can resolve the on-premises server DNS names — have you checked that?