Copy On-Premises SQL Database to Azure SQL Server Using ADF: A Step-by-Step Guide

Migrating an on-premises SQL database to the cloud can streamline operations and enhance scalability. Azure Data Factory (ADF) is a powerful tool that simplifies this process by enabling seamless data transfer to Azure SQL Server. In this guide, we’ll walk you through the steps to copy your on-premises SQL database to Azure SQL Server using ADF, ensuring a smooth and efficient migration.

Prerequisites

Before you begin, ensure you have:

• An active Azure subscription. If you do not have an active subscription, you crate one for free here:  Create Your Azure Free Account
• Access to an on-premises SQL database.
• Administrative access to install the Integration Runtime on your local machine.

Step 1: Create an Azure SQL Server Database

First, set up your target database in Azure:

• Log in to the Azure portal.
• Navigate to “Create a resource” and search for “SQL Database.”
• Configure the database with the Basic subscription tier, which offers 5 DTUs and 2 GB of data size (estimated cost: 403.48 INR/month).
• Complete the setup and note the database name (e.g., adf-test-database).
  
  

Step 2: Configure the Azure Firewall

To allow ADF to access your Azure SQL Database, configure the firewall settings:

• In the Azure portal, go to your SQL Database.
• Click on Set server firewall.
• Enable Public Access.
• Check the box to allow Azure services (like ADF) to access the database.

Step 3: Connect Your On-Premises SQL Database to ADF

Next, use ADF Studio to link your on-premises database:

Step 4: Set Up a Linked Service

A Linked Service is required to connect ADF to your on-premises SQL database:

• In ADF Studio, go to the “Manage” tab.
• Under “Connections,” select “Linked Services” and click New.
• Choose SQL Server as the data source type and configure the connection details for your on-premises database.
  
  
  

Step 5: Install the Integration Runtime for On-Premises Data

Since your data source is on-premises, you need an Integration Runtime:

• In ADF Studio, navigate to the “Manage” tab.
• Under “Integration Runtimes,” click New.
• Select Self-Hosted Gateway.

Finally, ensure everything is set up correctly:

Step 6: Verify and Test the Connection

• Test the connection in ADF Studio to confirm ADF can communicate with your on-premises SQL database.

To conclude, migrating you’re on-premises SQL database to Azure SQL Server using ADF is a straightforward process when broken down into these steps. By setting up the database, configuring the firewall, and establishing the necessary connections, you can ensure a secure and efficient data transfer. With your data now in the cloud, you can leverage Azure’s scalability and performance to optimize your workflows. Happy migrating!

Please refer to our case study of the city Council https://www.cloudfronts.com/case-studies/city-council/ to know more about how we used the Azure Data Factory and other AIS to deliver seamless integration.

We hope you found this blog post helpful! If you have any questions or want to discuss further, please contact us at transform@cloudfronts.com.