On-Premise to Azure SQL Database Migration

Starting from scratch on cloud platform is quite easier when you need to setup a database say on Azure but the moment discussions start happening about on-premise database(s), workload, servers, data-centers to migrate to cloud, the actual struggle or challenge begins. It is what this post makes an attempt to answer the questions, clear the doubts and pickup an appropriate direction. You can be in any of the situations as –

  1. Looking forward to assess your on-premise databases or database servers specially the SQL Server ones to determine if they are a good candidate for migration?
  2. Already decided to move some or all of your on-premise workload to Azure offerings?
  3. But getting confused how to begin with, what kind of exercises needs to be done beforehand so that you choose the right offering and ensure a smooth migration too?
  4. With so many tools provided by Microsoft itself making it further challenging?

Let’s take a quick look at the tools provided by Microsoft to help you with the assessment, recommendations or migrations. In fact, not just tools, Microsoft provides couple of services to better organize the entire exercise in a more scalable way. Below is the snapshot of what all it provides for all different possibilities –

In order to get an understanding of the capabilities and purpose of each tool and service, let us first take a pause and try to brainstorm about what all different tasks, areas and the scopes might be there that we need to give some consideration to or you can say, the real-life scenarios can be like –

  1. We might be interested in just evaluating or migrating only one database to cloud
  2. We might be willing to move few databases
  3. Possibly migrating a complete instance or all the databases hosted on an instance
  4. A complete server or even all the servers from an on-premise data center
  5. And a database is not an independent entity in a real-world. It is a host of data generated or consumed by some application i.e. it needs to be assessed by keeping in mind the entire workload
  6. Or this simply could be just an assessment exercise to justify your business case if you are more inclined or advocating migrating to cloud but need to convince the business owners with some facts like the benefits, efforts and the cost involved
  7. The SQL code is not just lying within SQL Server database itself but the data access layer of a front-end application might also have queries directly embedded in their code which means application code also needs to be evaluated if there can be any not-supported features or if any changes needs to be made to syntaxes
  8. Your all source databases might be SQL Server or they can be heterogenous
  9. When migrating, it’s not just the database code i.e. schema but data also needs to be migrated
  10. You might be willing to do migration on your own or expecting the tool itself to help you with the end-to-end migration

After giving this much thought, let’s now see which tool can be used for what all different purposes –

If your source is SQL Server and target is Azure SQL Database offerings or a SQL Server upgrade, you need to go with Data Migration Assistant. It helps you finding the feature parities between source vs. target platform and the actual migration too covering both schema and data with the flexibility to bypass if any objects need not to be moved to the target.

As we considered during brainstorming session that database code might be embedded in application’s data access layer so Data Access Migration Toolkit which is under Preview at the time of writing this post is something that you need to take further help from. Since this is in context of a front-end application’s context so it comes as an extension to Visual Studio which helps you identifying the SQL code written inside the application. The output which can be JSON files are analyzed along with database assessment in Data Migration Assistant tool.

However, if any of your source databases is different than SQL Server then you need to take some help from another tool called SQL Server Migration Assistant. There are different assistants for different sources other than SQL Server –

One last tool in this category is Database Experimentation Assistant which is purely for assessment purpose but personally I found this not so user-friendly and not of much worth to be explored to a great extent.

Before proceeding further, let’s take a note of couple of points about all the tools discussed so far –

  1. All of them are offline tools i.e. you need to get them installed in your on-premise and
  2. Among all four tools discussed so far, Data Migration Assistant (DMA) is the only one that can help with actual migration process too i.e. deploying code and moving data to target

Apart from these tools, Microsoft provides two services on Azure platform too to help with the migration exercise – Azure Migrate, also called as AMT (Azure Migrate Tool) and Azure Database Migration Service (DMS). Both of them allows the assessment as well as migration though they vary in the way that AMT provides the option to analyze all the underlying hardware including servers whereas DMS checks only databases. One important pre-requisite for them to work is to setup a VPN which is obvious because Azure Service has no idea about your on-premise network to reach out to your servers and databases. This can introduce a bit of complexity in utilizing these services so if you want to proceed with simple path, DMA as an offline tool is already there.

Microsoft recommends if your source databases are huge then prefer to use the services as they offer near zero-downtime during the migration.

One last option we would like to mention is MAP (Microsoft Assessment and Planning) toolkit. This is for the scenarios if you yourself don’t know how many SQL Server instances are running on your network i.e. it has various discoverability options based on domain, network, IP ranges etc. with the option to supply your own list. This is also an offline tool and is a good option if you need to do a thorough analysis and discoverability by creating an inventory and a deep consideration to your hardware; suitable for planning to move data centers to Azure; something similar to AMT as online option. Its major drawback seems that Microsoft has not updated its documentation for last several years.

From migration perspective, Microsoft classifies the entire migration process into three phases as – Pre-migration, Migration and Post-migration as summarized in the picture as follows –

This article must have given your some good understanding of the concept and to learn further in more depth, you may visit Microsoft’s official documentation. While navigating to the documentation, hover your cursor over any of the sources and you will see applicable target options to read further as shown in the below snapshot too for SQL Server –

If you have read thus far and liked any or all parts of the content don’t forget to motivate by hitting the like button below.