What is the best way to migrate SQL database to another SQL server in Azure. Minimal downtime.

Question

What is the best way to migrate SQL database to another SQL server in Azure. Minimal downtime.

|_SQLViking_| 26

Hi!

I am fairly new to Azure, but have good onprem experience.

Asked to migrate two databases (both used by an app) to another subscription/rg/location.

Databases are hosted on Azure SQL Logical Serve, that is also the target. Standard tier.

So the customer wants this:

move the two databases
check if all is ok from the app side (eg connection ok/user have proper access)
stop app, replicate changes to new server
failover app to use new sql server

All this with minimal downtime of course.

In other words, I think this is quite normal scenario.

Size of databases are aroud 200 GB.

What is the recommended/preferred way of completing this?

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-10-12T11:05:53.72+00:00

I am not going claim expertise here, but out of curiosity I did a quick thing on Google. This thread has a couple of suggestions: https://learn.microsoft.com/en-us/answers/questions/610126/how-to-copy-azure-sql-database-to-another-subscrip. As has https://learn.microsoft.com/en-us/answers/questions/512410/moving-an-azure-sql-server-to-another-subscription. I see a couple of more hits that could be interest (surely, there is a great deal of overlap). My Googles string was migrate azure sql database from one subscription to another.

One recurring theme appears to be that it is a lot easier if the subscriptions are in the same tenant.
NIKHILA NETHIKUNTA 4,600 Reputation points Microsoft External Staff

2024-10-15T15:31:57.48+00:00

@|SQLViking|
Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Anonymous

2024-10-18T02:29:14.0733333+00:00

Hi @|SQLViking| ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress.

By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,

Lucy Chen

2 answers

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-10-12T11:05:53.72+00:00

I am not going claim expertise here, but out of curiosity I did a quick thing on Google. This thread has a couple of suggestions: https://learn.microsoft.com/en-us/answers/questions/610126/how-to-copy-azure-sql-database-to-another-subscrip. As has https://learn.microsoft.com/en-us/answers/questions/512410/moving-an-azure-sql-server-to-another-subscription. I see a couple of more hits that could be interest (surely, there is a great deal of overlap). My Googles string was migrate azure sql database from one subscription to another.

One recurring theme appears to be that it is a lot easier if the subscriptions are in the same tenant.
NIKHILA NETHIKUNTA 4,600 Reputation points Microsoft External Staff

2024-10-15T15:31:57.48+00:00

@|SQLViking|
Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Anonymous

2024-10-18T02:29:14.0733333+00:00

Hi @|SQLViking| ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress.

By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,

Lucy Chen

Answer 1

@|SQLViking|
Thank you for the question and for using Microsoft Q&A platform.
Based on the information you provided, using the Azure Database Migration Service (DMS) to migrate your databases would be better. DMS is a fully managed service designed to enable seamless migrations from multiple database sources to Azure data platforms with minimal downtime.

Here are the steps you can follow to migrate your databases using DMS:

Create a new Azure SQL Database logical server in the target subscription/region.
Create a new Azure SQL Database on the target logical server with the same configuration as the source database.
Create a migration project in DMS and configure the source and target endpoints.
Select the databases you want to migrate and start the migration.
Monitor the migration progress and verify that the databases have been successfully migrated.
Update the app connection strings to point to the new database server.
Perform a final synchronization to ensure that all data changes have been replicated to the new server.
Failover the app to use the new SQL server.

To minimize downtime, you can perform the migration during off-peak hours and use the DMS feature called "continuous data replication" to replicate changes from the source database to the target database in near real-time. This will help ensure that the target database is up to date when you are ready to failover the app.

After the migration is complete, you can use the Azure portal or other tools to verify that the app is connecting to the new database server and that users have proper access.

You can refer to the below links:
https://learn.microsoft.com/en-us/azure/dms/
https://learn.microsoft.com/en-us/data-migration/sql-server/database/overview
https://learn.microsoft.com/en-us/data-migration/sql-server/database/guide
Hope this helps. Do let us know if you have any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

NIKHILA NETHIKUNTA 4,600 Reputation points Microsoft External Staff

2024-10-16T15:52:17.0066667+00:00

@|SQLViking|
Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Hi @|_SQLViking_| ,

Thank you for your reaching out and welcome to Microsoft QA!

Do you mean you want to minimize the downtime when migrating to Azure SQL? or you can check out this article.

Please look at this:

User's image

In addition, here are my suggestions of how to migrate SQL database to Azure. Hope these can help you better understand each step in migrating:

It is unlikely that you'll have any compatibility or feature parity issues if the source and target SQL Server versions are the same.
Before migration, it's a good practice to run an assessment of your SQL Server databases if the versions are different. We recommend the Azure Migrate to discover and assess single databases or at scale from different environments. This extension collects performance data from your source SQL Server instance to provide right-size Azure recommendation that meets your workload's performance needs with minimal cost. You can see this article to learn more. NOTE: Please ensure the logins used to connect the source SQL Server are members of the sysadmin server role or have CONTROLSERVER permission.
There are many migration methods to migrate your databases. Please read this article carefully and choose appropriate method, the steps of each method are list detailed in this article.
We recommend the DMS first. It provides online and offline migration modes. You can check the limitations between them to choose the better method.
Not all SQL Server versions support all compatibility modes. Check your target SQL Server version supports your chosen database compatibility. We recommend the DMA to assess on-premises SQL Server instances and understand the gaps between the source and target versions.

I hope this helps! If you have any more issues or need further assistance, feel free to share with us!

Best regards,

Lucy Chen

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Anonymous

2024-10-31T01:33:33.7566667+00:00

Hi @|SQLViking| ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress.

By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,

Lucy Chen

Share via

What is the best way to migrate SQL database to another SQL server in Azure. Minimal downtime.

2 answers

Your answer