Problem
We have an on-premises SQL Server database and we have used the Data Migration Assistant (DMA) to check for any compatibility issues before migration to Azure SQL database. Is it possible to use this tool to perform the actual migration of the database to the new target?
Solution
In part 1, of the tip series, you learned about using the tool to check for any sort of compatibility issues before the migration. You can refer to part 1, to follow the steps to download and install the tool. In this tip, we will perform a demo of the actual migration.
Migrate from SQL Server to Azure SQL database
First, I created a blank Azure database and the plan is to migrate data from my on-premises database. You can refer this tip for creating an Azure SQL database. Once the Azure database is available, get the actual Azure server name. Start the Data Migration Assistant tool and begin the migration. You can refer to the screenshot below.
In the migration scope, we have additional options to choose – schema only, data only. In this demo, we will go for migrating both schema and data. Once you selected the options, click on “Create”. In the next window, connect to the source server as shown below.
Connect to Source Server
Once you click on “Connect”, you will be able to see the list of databases on the source server.
In this demo, we will migrate the sample database “a” to the Azure SQL database. Select that database and click on “Next”. Once done, in the next window, you need to enter the details of the target server. You would have collected the Azure server details while creating the Azure SQL database. Enter the details as shown in this screenshot.
Connect to Target Azure server which hosts the Azure SQL database
Once you click on “Connect”, you will be able to see the list of Azure SQL databases on that Azure server. In this demo, you can see the sample Azure database that I created for migrating.
Select the Azure database and click on “Next”. Once done, you will see this screen which displays the progress. The time taken to progress depends on the size of your source database.
Once this process completes, you will able to view the schema objects in your source database.
Select the schema objects that you would like to migrate and click on “Generate SQL script”. Once done, the process starts scripting the schema objects. In this demo, as only one schema was selected, the process completed in seconds.
Once this process completes, the scripts get generated for the schema objects. We have the option to review the scripts and modify if required.
Deploy schema on to the Azure SQL database
You can now deploy the schema by clicking on the “Deploy Schema” option. This process takes time depending on the number of objects you choose to deploy. In my case, as it was a simple schema, it completed in seconds. Once the process completes, you can view the status of the deployment in the right pane of the window as shown below.
With this, the schema would have deployment successfully on the Azure SQL database too.
Check schema deployment on Azure SQL database
You can then connect to the Azure SQL database to check if the new schema is available. Connect to the Azure SQL database using SQL Server Management Studio (SSMS) and check to see if the schema is deployed.
Here, you can see that the schema was deployed successfully. We had just used the Data Migration Assistant (DMA) tool to perform the deployment. You will notice that the data is not yet migrated.
In the next step, we will perform the migration of the actual data.
Migrate data to Azure SQL Database
After the schema validation on the Azure SQL database, click on the option– “Migrate data” as shown below.
This will take you to this screen where you can view the table details and have the option to select the tables for the data migration.
As only one table with 2 rows was selected for data migration, the process completed in seconds. In your case, for bigger tables with millions of rows, you may need to plan accordingly and size the Azure DB appropriately on the correct performance tier. Once the data migration process completes, you will see this window.
Here you see that the data migration was successful with no issues.
Check for migrated data on Azure SQL database
After schema deployment, you can see that the schema was deployed. Now, let us confirm that the data is migrated after the data migration. Connect to the Azure SQL database and check the table where the data was migrated to.
Here you can see the data is successfully migrated. All of the migration steps were performed by just using the Data Migration Assistant.
As described earlier, you could use the Data Migration Assistant tool to only migrate the schema or data. In our demo, we used it for migrating both schema and data.
Next Steps
- With this tip, you were able to use the Data Migration Assistant (DMA) tool to migrate the schema and data to Azure SQL DB
- Try this tip using your own sample on-premises database
- Refer this tip to migrate only the schema to an Azure SQL database
- Refer this tip to migrate only the data to an Azure SQL database
- In the next tip, we will see how to migrate a database from SQL server to a SQL Server on Azure VM
- To get familiar with Azure, refer the numerous tips available at this link
