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:
2) Here’s the target DB with no tables:
3)Create a new pipeline in Data Studio and drag the Copy Data Task to the Design page as shown below:
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.
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):
6)Now click on the sink and hit the pencil icon on the right to open the Sink section:
7)Now enter the details of the Target SQL MI as shown below:
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)
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):
10)Now validate the pipeline to ensure no errors are found:
Now hit Publish at the top to publish the changes:
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):
12)The target MI now contains the table from the Source along with the source data as shown below:
References:
Sqlpackage-
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 in Azure SQL Database - Azure Data Factory & Azure Synapse | Microsoft Learn