Azure Data Factory with local SQL server database

Azure Data Factory with local SQL server database

Ahmed Sayed's photo
Ahmed Sayed
·

2 min read

Follow these steps to set up and use Azure Data Factory with a local SQL Server database:

  1. Install and configure a Self-hosted Integration Runtime (IR)
  2. ADF uses the Integration Runtime (IR) to access on-premises resources like your local SQL Server. To set up a self-hosted IR, follow the instructions in the official documentation.
  3. Create a SQL Server Linked Service in ADF
  4. In ADF, Linked Services define the connection settings to your data sources. To create a SQL Server Linked Service:
  5. a. Sign in to the Azure portal. b. Navigate to your Azure Data Factory. c. Go to the “Author & Monitor” tab. d. Click the “Author” tab on the left-hand side. e. Click the “Connections” (lightning bolt) icon at the bottom. f. Click “New” and search for “SQL Server”. g. Fill in the required fields, such as the server name, authentication type, username, and password. For “Connect via integration runtime,” choose the self-hosted IR you created earlier. h. Test the connection, and if successful, click “Create.”
  6. Create datasets for source and destination
  7. In ADF, datasets are used to represent the structure of the data within the Linked Services. Create a dataset for your local SQL Server database (source) and another dataset for the destination, such as Azure SQL Database or Azure Blob Storage.
  8. Create a data movement pipeline
  9. Now you can create a pipeline to move and process data from your local SQL Server to the destination. In the pipeline, use activities like “Copy Data” or “Mapping Data Flows” to move and transform the data as needed.
  10. Trigger the pipeline
  11. After creating the pipeline, you can manually trigger it or set up a schedule to run it at specific intervals. Use the “Trigger Now” button to run the pipeline immediately, or create a new trigger with the desired schedule.
  12. Monitor the pipeline
  13. In the “Monitor” tab in ADF, you can track the progress and status of your pipelines, view activity run details, and troubleshoot any issues that may occur.

For a more detailed guide, refer to the official Azure Data Factory documentation.

ADFAzure