Migrating an Azure SQL DB to a SQL MI by utilizing SqlPackage/ADF
Published Feb 20 2024 08:16 AM 2,173 Views

Scenario:

We have an Azure SQL DB that needs to be migrated to a SQL MI.

 

Problem:

At the time of writing the article, the Azure portal doesn`t provide a graphical interface to perform such a migration. The problem becomes more complicated with bigger databases (>100 or 150 GB).

 

Solution:

If the DB size is more then 300 GB, the only logical option at this time, could be breaking the original DB into a few parts and taking an export dump via SQLpackage of the individual DBs.

However, if the DB size is smaller up to 150 GB, we may try 2 techniques viz. SQLPackage or Azure Data Factory (ADF). Although, each of the techniques may have to be tweaked depending on the data size and schema complexity. 

 

SqlPackage- While at this moment, sqlpackage is the most straightforward way of migrating an Azure SQL DB to SQL MI, for bigger DBs (>250GB), the option of using SQLpackage can run into issues like timeout or very slow progress ultimately leading to errors. Using a slightly modified command for sqlpackage may possibly help even for bigger DBs between 100-200 GB (Kindly tweak the command below as necessary):

 

sqlpackage.exe /Action:Export /TargetFile:"E:\Backup\dbexporttest.bacpac" /SourceDatabaseName:dbexporttest_new /SourceServerName:"dbexp-tst-sql-server.database.windows.net" /SourceUser:"sysadminmdA" /SourcePassword:"********" /diagnostics:true /p:LongRunningCommandTimeout=0 /p:CommandTimeout=2000000 /p:DatabaseLockTimeout= -1 /p:VerifyExtraction=False /p:compressionOption=notcompressed /p:TempDirectoryForTableData=M:\ dbexporttest\temp /d:True /df:M:\dbexporttest\2024.txt

 

Azure Data Factory (ADF)- Here's how this can be attempted via ADF:

 

Target SQL MI- arsenal.xxxx.database.windows.net

Source Azure SQL DB- yyy.database.windows.net,1433

1)Here are the tables in the Source DB:

Tanayankar_Chakraborty_0-1708403826602.png

2) Here’s the target DB with no tables:

Tanayankar_Chakraborty_1-1708403869512.png

3)Create a new pipeline in Data Studio and drag the Copy Data Task to the Design page as shown below:

Tanayankar_Chakraborty_2-1708403931628.png

4)Click on the source, Open (pencil icon), Click again on the Edit (Pencil icon) and that opens the edit linked server section as shown below. Fill in the Source Azure DB details and hit test connection to ensure the linked server can connect to the Source Azure SQL DB.

Tanayankar_Chakraborty_3-1708403987128.png

Tanayankar_Chakraborty_4-1708404014404.png

 

5)Ensure that the check box below is checked (On the Source Azure SQL DB Networking Tab) so that the ADF pipeline can connect to the Azure SQL DB (If your set up prefers high security & uses a Private Endpoint, kindly set that up in Source & Target Linked servers accordingly):

Tanayankar_Chakraborty_5-1708404060684.png

6)Now click on the sink and hit the pencil icon on the right to open the Sink section:

Tanayankar_Chakraborty_6-1708404184881.png

7)Now enter the details of the Target SQL MI as shown below:

Tanayankar_Chakraborty_7-1708404224862.png

Tanayankar_Chakraborty_8-1708404246293.png

 

8)To ensure the linked server can connect to the target SQL MI, make the following changes in the SQL MI NSG as shown below: (Please ensure that your laptop IP is allowed, make other changes as necessary)

Tanayankar_Chakraborty_9-1708404279416.png

9)Ensure the source & target are mapped as shown below (For the demo I only chose 1 table, Also i had created the empty table in Target for simplicity. You may also use the query option to choose specific/all tables in a schema to perform the migration in batches):

Tanayankar_Chakraborty_10-1708404414146.png

10)Now validate the pipeline to ensure no errors are found:

Tanayankar_Chakraborty_11-1708404458525.png

Now hit Publish at the top to publish the changes:

Tanayankar_Chakraborty_12-1708404496951.pngTanayankar_Chakraborty_13-1708404511633.png

11)Now hit trigger now at the top to trigger the pipeline as shown below (If the pipeline wasn’t triggered in the step above):

Tanayankar_Chakraborty_14-1708404550348.png

12)The target MI now contains the table from the Source along with the source data as shown below:

Tanayankar_Chakraborty_15-1708404590272.png

 

References:

Sqlpackage- 

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/troubleshooting-issues-and-performance-with-s...

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage?view=sql-server-ver16

SqlPackage Export - SQL Server | Microsoft Learn

Azure Data Factory

Copy data in bulk using Azure portal - Azure Data Factory | Microsoft Learn

Copy and transform data to and from SQL Server - Azure Data Factory & Azure Synapse | Microsoft Lear...

Copy and transform data in Azure SQL Database - Azure Data Factory & Azure Synapse | Microsoft Learn

 

2 Comments
Version history
Last update:
‎Feb 20 2024 08:16 AM