What are some different methods to restore/copy/migrate Azure SQL Database across subscription and across the server?

Question

I'm just trying to access different methods to migrate my SQL database from one subscription to another

Answer 1

Option 1: PITR restore method -

Step 1: Create new server in source subscription
Step 2 : Copy the database from source server to newly created server within the same subscription
step 3 : move the entire server to the destination subscription along with the database
step 4: now again copy the database from moved server between the servers within destination server

Ref: Move server https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-move-azure-sql-server-to-another-subscription/ba-p/368790
Ref Copy database: https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell

Option 2: Direct copy the database to another subscription

Copy the database directly to the different subscription. Please note this method is not supported via azure portal.

Ref: https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell#copy-to-a-different-subscription
QnA: https://learn.microsoft.com/en-us/answers/questions/610126/how-to-copy-azure-sql-database-to-another-subscrip.html

Option 3: .bacpac restore method / export-import method

You can also use export import method

Step 1: Export the source database in .bacpac format and store it into the storage account / local storage
Step 2 : Import the bacpac into destination server

Ref :
Export - https://learn.microsoft.com/en-us/azure/azure-sql/database/database-export
Import- https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import?tabs=azure-powershell
Blog: https://www.mssqltips.com/sqlservertip/5189/restore-an-azure-sql-database-from-one-server-to-another-server/

Option 4: use import/export method with SQLCMD

You can use quiet big compute sized VM for this operation so that it will have enough resources to perform export and import operations using SQLCMD

Step 1: Create temporary VM with big compute size
Step 2: Create the copy of the database on the same server to ensure transactional consistency
Step 3: Export copied the database using SQLCMD from source database
Step 4: import the exported .bacpac onto destination server using SLQCMD

The SQLCMD used the compute resources of your VM and the performance will depend on the compute resource allocated to it

When using option 4, you can scale the database to the highest possible service tier --> the SQL VM in the same region as the SQL Database with highest possible configuration while export --> and vice-versa when performing the import operation on the destination server, scale the SQL Db pricing tier to highest SLO> After the import you can immediately delete the un-necessary resources as well you can scale down the database size for the imported database.

Option 5: Data Sync method

You can use azure data sync option to sync your source and the destination databases.

If the subscriptions belong to the same tenant and you have permission to all subscriptions, you can configure the sync group in the Azure portal. Otherwise, you have to use PowerShell to add the sync members .Create a sync group first there you will give the Hub and sync DB details. Next is to select the member db there you can select subscription. I'd recommend to scale both source and destination databases to highest SLO such as business critical. The first sync will do a bulk copy of the data. I'd also suggest not to perform any transactions on the database on the HUB until all the 1TB+ data moves to the destination.

Ref: https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database
Please also make a note of general limitations before using this use case: https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database#general-limitations
Best practices using azure data sync : https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-best-practices

Thank You!

Answer 2

Hello,

Please find below different methods that you can use to restore/copy/migrate Azure SQL DB across subscriptions and Azure SQL Server.

Option 1: PITR restore method -

Step 1: Create new server in source subscription
Step 2 : Copy the database from source server to newly created server within the same subscription
step 3 : move the entire server to the destination subscription along with the database
step 4: now again copy the database from moved server between the servers within destination server

Ref: Move server https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-move-azure-sql-server-to-another-subscription/ba-p/368790
Ref Copy database: https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell

Option 2: Direct copy the database to another subscription

Copy the database directly to the different subscription. Please note this method is not supported via azure portal.

Ref: https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell#copy-to-a-different-subscription
QnA: https://learn.microsoft.com/en-us/answers/questions/610126/how-to-copy-azure-sql-database-to-another-subscrip.html

Option 3: .bacpac restore method / export-import method

You can also use export import method

Step 1: Export the source database in .bacpac format and store it into the storage account / local storage
Step 2 : Import the bacpac into destination server

Ref :
Export - https://learn.microsoft.com/en-us/azure/azure-sql/database/database-export
Import- https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import?tabs=azure-powershell
Blog: https://www.mssqltips.com/sqlservertip/5189/restore-an-azure-sql-database-from-one-server-to-another-server/

Option 4: use import/export method with SQLCMD

You can use quiet big compute sized VM for this operation so that it will have enough resources to perform export and import operations using SQLCMD

Step 1: Create temporary VM with big compute size
Step 2: Create the copy of the database on the same server to ensure transactional consistency
Step 3: Export copied the database using SQLCMD from source database
Step 4: import the exported .bacpac onto destination server using SLQCMD

The SQLCMD used the compute resources of your VM and the performance will depend on the compute resource allocated to it

When using option 4, you can scale the database to the highest possible service tier --> the SQL VM in the same region as the SQL Database with highest possible configuration while export --> and vice-versa when performing the import operation on the destination server, scale the SQL Db pricing tier to highest SLO> After the import you can immediately delete the un-necessary resources as well you can scale down the database size for the imported database.

Option 5: Data Sync method

You can use azure data sync option to sync your source and the destination databases.

If the subscriptions belong to the same tenant and you have permission to all subscriptions, you can configure the sync group in the Azure portal. Otherwise, you have to use PowerShell to add the sync members .Create a sync group first there you will give the Hub and sync DB details. Next is to select the member db there you can select subscription. I'd recommend to scale both source and destination databases to highest SLO such as business critical. The first sync will do a bulk copy of the data. I'd also suggest not to perform any transactions on the database on the HUB until all the 1TB+ data moves to the destination.

Ref: https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database
Please also make a note of general limitations before using this use case: https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database#general-limitations
Best practices using azure data sync : https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-best-practices

Thank You!