Azure Data Factory: How to migrate on-premise SQL to Azure SQL?

Azure Data Factory: How to migrate on-premise SQL to Azure SQL?

In this post, we will see how to migrate on-premise SQL to Azure SQL using Azure Data Factory.

Azure Data Factory support migration of content from various data sources like SQL, SAP, Postgres SQL, Oracle, SFTP etc. to many different target targets. Full and updated list of Azure Data Factory source and targets can be found here –

https://docs.microsoft.com/en-us/azure/data-factory/data-factory-introduction

Migrate from on-premise SQL to Azure SQL

Points to remember

  1. No need to assign Public IP address to on-premise SQL server
  2. Outbound Internet connection is required from on-premise SQL server
  3. If migrating to PaaS then please go through Azure SQL connection/sizing limits and feature limitations

Step 1: Prepare / Know your environment

  • On-Premise SQL Server = SQLIAAS
  • On-Premise Database Name = sarveshgoel.com
  • Table Name = Azure
  • Total records = 2
  • Target Azure SQL (PaaS) DB name = SQLPaaS
  • Target Azure SQL server name = sqlpaas123.database.windows.net
  1. Snapshot of source Database server, database, table and content
  2. Now, login to Azure SQL using SQL Management Studio and create empty table with columns that needs to be migrated. Azure Data Factory will use these columns during migration.

Step 2: Configure Azure Data Factory to migrate SQL server to Azure SQL

  1. Login to Azure Portal (https://portal.azure.com), and create an Azure Data Factory instance
  2. Select your Azure DF and select “Copy data (Preview)”
  3. Fill the details like “Task Name” etc., and select if you need to run the task just once or migrate at regular intervals. This is important setting because you may want to ensure one-way replication or migration is enabled.
  4. Select your data source like Oracle, SAP etc. In this post, I have selected “SQL Server”
  5. On this screen, before filling the form. Click on “Create gateway”. Since on-premise SQL is not exposed to Internet directly, we need to install Data Management Gateway to start sending data to Azure SQL
  6. Download and copy the installer to on-premise SQL server, copy the key to register, as it is needed post deployment
  7. Install the “Data Management Gateway” on the on-premise SQL server
  8. Launch the Data Management Gateway, copy the Key that you copied previously, and click on “Register”. Wait for confirmation like shown below
  9. Now fill the form, type the name of SQL server, Database name, login details etc. You need to ensure that on-premise SQL server is running for gateway to connect. This screen also verifies the connection details, and enables the “Next” button when the details are correct.
  10. Select table that you want to migrate. In this example, I have selected table “Azure” that we saw before.
  11. If you have data filter like you want to migrate only part of data and not entire table then the custom query can be entered here. I have chosen “None”
  12. Now select Data target, in this case let us select “Azure SQL Database”, database name and login credentials
  13. Type in the values like Azure Subscription, Database server details, 
  14. Perform the schema mapping here, which means mapping source database columns with target database columns
  15. Configure how fault tolerance should be handled
  16. Verify the summary here, and this is the final step before copying of data starts
  17. Now the deployment of job starts, the job can be monitored as well on the new console.

Step 3: Verification of data replication

  1. We now login to target Azure SQL database to validate if content has been replicated or not.

 

Hope you found this article useful.

 

About The Author

Related posts

Leave a Reply

Your email address will not be published. Required fields are marked *