How to migrate SQL database to Azure ? What is the standard way?

Question

What is the exact procedure to migrate on-prem SQL databases to azure?

Answer 1

You aren't going to get a comprehensive answer here. It is too complex. You should consider bringing in a consultant to review your DB and determine what will and won't migrate well. There are several options available depending on what you use and need.

Lift and shift involves creating a VM in the cloud and moving your DB to that. This is the least likely to break anything as you are literally running the same database and have total control. However all you've done is moved from an on-prem server to a cloud one so you lose most of the benfits. You're still responsible for everything involving maintenance and you've added the cost of a cloud service. Furthermore any on-prem apps that need access to the DB now need to connect to it via an Azure connection and that is complex as well. Depending on whether you have access to RouteExpress(?), where the server is stored and how much you're willing to pay determines whether this will work well or not.

Another option is to migrate to Azure SQL. That is fully discussed here. This works well if you are just moving databases, you don't rely on features that aren't available in Azure SQL and you don't have highly customized needs. You have to review your DB to determine what you might or might not be able to migrate. This is where a consultant comes in. However if this is an option then it is cheaper than lift and shift but you have to pay for the database usage so you have to monitor performance and deal with scaling issues in your app. Again, a consultant can help with this. On the app side you have similar challenges as to using lift and shift. You need to have a fast connection to Azure, you need to deal with security, latency is more of an issue, etc.

If you don't know what you are doing then this is going to end in disaster for anything beyond the simplest database. I would strongly recommend that you hire a consulting company to review your systems and help plan a migration to the cloud based upon your entire system and not just the database. It is a complex process full of pitfalls if you don't know what you're doing. You could end up with performance issues, security problems and even lose of data.

Answer 2

Hi @Divya wairkar

There are several ways to migrate on-premises SQL databases to Azure SQL. Here are some of the standard ways:

 1. Azure Database Migration Service: You can use Azure Database Migration Service to migrate on-premises SQL Server databases to Azure SQL. This service provides a fully managed solution for migrating databases to Azure SQL with minimal downtime.

 https://learn.microsoft.com/en-us/azure/dms/dms-overview

2. SQL Server Management Studio: You can use SQL Server Management Studio to generate a BACPAC file of your on-premises SQL Server database and then import it into Azure SQL.

Here is reference link- Migration guide: SQL Server to Azure SQL Database

Tutorial: Migrate SQL Server to Azure SQL Database (offline)

Let us know if you need any additional information.

Thank You!

Answer 3

The easiest way to migrate is to use a tool named Data Migration Assistant (DMA). Do the following steps with that tool.

1. Download and install DMA from here.
2. Perform an assessment of the databases you want to migrate using DMA as explained here.
3. Perform the adjustments requested by DMA on the assessment, if any.
4. Let DMA migrate the database for you as explained here.
5. After the migration, validate the schema and data in the Azure SQL Database to ensure everything has been transferred correctly.

Answer 4

Hi @Divya wairkar,

Thank you for reaching out and welcome to Microsoft Q&A.

is there any way to learn the correct and standard procedure

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

1. It is unlikely that you'll have any compatibility or feature parity issues if the source and target SQL Server versions are the same.
2. 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.
3. 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.
4. We recommend the DMS first. It provides online and offline migration modes. You can check the limitations between them to choose the better method.
5. 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.

Hope my answer can help you well.

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