10. How to migrate the SQL database to Azure with the Azure Database Migration Service
In cases where we have our SQL databases on-premises and we want to migrate them to Azure, we can do it in a simpler way than manually with the Azure Database Migration Service. The service is analyzing our on-premises databases and providing information in every step of the process in case we want to customize one specific step.
Show you the basic steps to migrate an on-premises database to Azure with the Azure Database Migration Service (ADMS). The process will the explained in detail using the Microsoft Data Migration Assistant, which is part of the ADMS and allows us to do the whole process. In addition, the other options of using this service will be exposed.
In the cases where your on-premises database is not reachable from Azure, and you do not want to create an Azure VPN, you can use the Azure Database Migration Assistant (ADMA). You can download it directly from the microsoft web page. There is a link as well in the migration steps of the Azure Migration service:
Once installed and started the tool, select create a new process:
In the options select Migration, give a name to the migration project and then select the source and target types. In our case we are migrating from a SQL Server to Azure SQL Database.
In this screen we add the configuration to connect to our source SQL database:
Now we put the data of our Azure SQL Database. In case that we do not have it already created, the link Create a new Azure SQL Database... is provided to get more information about how to create one. Write the server name and the connection credentials, and the select the target database, in our case we have named in eshop-modernized-migrated:
The assistant will process the information and then we will select the 'objects' to migrate. In our case we want a full migration. If there is any issue for the migration of any object, it will be shown in this step.
Click Generate SQL script to create the database schema.
The assistant gives us the option to modify the script for the schema generation. Once we are ready, we click Deploy schema to deploy in the target Azure SQL database.
Click migrate data to configure the data to migrate:
Select the data tables Which must be migrated:
Once ready click Start data migration.
Once the process is finished, we will see the results about the whole migration
After this step we have successfully migrated our on-prem database to Azure.
When we have finished the process, we are ready to use the database in Azure. For eShopModernizing we just need to change the connection string to the new one. Go to the Azure SQL database, and in the Overview tab select Show database connection strings. Once there, copy the connection string and change it in the variable in eShopModernizing
Yo can write the connection string in the .env file if you are using Docker Compose. Uncomment the ESHOP_AZURE_STORAGE_CONNECTION_STRING line removing the # character and put the connection string value. For the other cases (Kubernetes, Service Fabric or Virtual Machines) you can follow the other sections of this wiki where we explain how to configure each environment.
In the previous guide we have installed the migration assistant in one of our on-premises machines to do the whole process. This is convenient in cases when we do not want to connect our database to Azure, but this forces our servers to do the whole processing. On the contrary, the Azure Database Migration Service does the whole process in the cloud for us, but we need to give visibility to our on-prem databases from the Azure resources.
The service works as the Data Migration Assistant, we have to create a New migration project and then select the options as with the assistant.
In this case the process is being done from the Azure service, so for the source server name we have to put one SQL server visible from Azure. This can be achieved for on-premises servers using either Azure VPN or Azure ExpressRoute.