Copy Data from MySQL to Azure SQL with Auto Table Creation in ADF to achieve efficient and automated data movement between systems. Azure Data Factory (ADF) stands out as a robust and flexible solution that enables seamless data migration with minimal manual intervention.
In this comprehensive guide, you’ll learn step-by-step how to copy data from MySQL to Azure SQL Database using ADF—leveraging its Auto Create Table feature to eliminate the hassle of manually creating destination tables.
This end-to-end walkthrough is designed to be simple and direct: no transformations, no triggers—just a Copy Data activity that handles everything from schema replication to data transfer automatically.
Step 1: Scenario Overview for Copy Data from MySQL to Azure SQL
Suppose your organization maintains a SalesData table in a MySQL database, and you need an exact copy in an Azure SQL Database. Rather than spending time creating the table structure in Azure SQL, you can configure ADF to automatically generate the destination table based on your MySQL schema. This approach is perfect for migration projects, proof-of-concept pipelines, or any scenario where agility and automation are priorities.
Step 2: Prepare Your MySQL Source
Start by ensuring your SalesData table is well-defined in MySQL, containing fields such as SaleID, ProductName, Quantity, and SaleDate.
Next, check that your MySQL server is accessible from Azure, either through a public endpoint or a secure VPN. Your user account should have sufficient read privileges on the SalesData table.
Test the connection using MySQL Workbench or a similar client to confirm accessibility. Once you’ve verified connectivity, you’re ready to integrate MySQL as a source in Azure Data Factory.
Step 3: Set Up Linked Services in ADF
In Azure Data Factory, Linked Services function as connection managers for your data sources and destinations. For this process, you’ll need two linked services:
- MySQL Linked Service: Configure this with your server’s address, database name, authentication method, and credentials (username and password).
- Azure SQL Database Linked Service: Specify your Azure SQL server name, database name, and preferred authentication (typically Azure Active Directory or SQL authentication).
Use the Test Connection feature within ADF to confirm that both linked services can successfully connect to their respective systems. This validation step helps catch connectivity or credential issues early.
Step 4: Define Source and Sink Datasets
Datasets in ADF represent the specific structures you’ll be reading from (source) and writing to (sink). Here’s what you need to do:
- Source Dataset: Link this to your MySQL SalesData table via the MySQL linked service.
- Sink Dataset: Point this to your target Azure SQL table using the Azure SQL linked service. If the table doesn’t exist, don’t worry—ADF will handle its creation in the next step.
This mapping ensures ADF knows which data to extract and exactly where to load it.
Step 5: Configure the Copy Data Activity
Within your ADF pipeline, add a Copy Data activity—the central component that orchestrates the transfer.
- On the Source tab, choose your MySQL dataset.
- On the Sink tab, select your Azure SQL dataset.
- On the sink side, we will enable the Auto Create Table option.
By enabling “Auto Create Table,” ADF automatically generates the destination table in Azure SQL, mirroring the schema (column names and data types) from the MySQL source. This not only saves time but also ensures consistency between databases. You can preview the schema mapping to confirm accuracy, but since no data transformations are needed, the default mapping typically suffices.
Step 6: Execute and Monitor the Pipeline
With everything set up, click Debug to run the pipeline. ADF establishes a connection to the MySQL source, reads from the SalesData table, and creates the corresponding table in Azure SQL Database—then copies all records.
Monitor the execution progress in the ADF Monitor tab. After completion, verify the results in SQL Server Management Studio (SSMS) or Azure Data Studio. You should see a newly created table in Azure SQL containing all the original data from MySQL.
Step 7: Conclusion and Best Practices
By following these steps, you can Copy Data from MySQL to Azure SQL with Auto Table Creation in ADF quickly and efficiently. This approach minimizes manual effort, ensures schema consistency, and accelerates data migration projects.
ADF’s Auto Create Table capability simplifies ETL workflows, reduces development effort, and ensures schema consistency between environments. For production-grade solutions, consider enhancing your pipeline with features such as scheduling, error handling, and logging—turning this straightforward copy operation into a robust, managed data workflow.
Whether you’re migrating legacy databases or building integrations across hybrid environments, this method provides a fast, reliable, and code-free way to move your data where it’s needed.
Final Tip: As you scale or move to production, leverage ADF’s built-in monitoring, alerting, and automation capabilities to further optimize and govern your data pipelines.
Watch the full video here: