In the previous articles, we had an introduction about the Azure SQL Database, Azure Service Tiers and pricing models, Creation of Azure SQL Database & How to connect with the Azure SQL Databases. Now, in the next step, we will discuss about the SQL database migration from On Premise to Azure Portal. There are many ways for performing the migration. For example:
- Using SQL Server Management Studio (SSMS) Deploy method
- Using SQL Server Management Studio (SSMS) Export Data – tier Application
- Using Transaction Replication
- Using Microsoft Data Migration Assistant (DMA) Tool
This article will tell about migrating SQL Database to Azure SQL Database using SSMS deploy as a step by step process. This step by step knowledge will help you in migrating the database from your On premise database server to the Azure portal.
SQL Database Migration to Azure SQL Database
We are using the SQL Server Management Studio 2016 for the database migration. In the below mentioned screenshot, we see that there are two servers connected. Top Server is Azure SQL Server which we created in our previous article and the other Server is On Premise SQL Server. In this article, we are going to migrate SQL database AdventureWorks2012 from On premise to Azure Portal.
Figure 1: On Premises AdventureWorks2012 Database for Migrating to Azure
To start the process, select the On Premise database AdventureWorks2012 and right-click on it. From the different options list, select Tasks & then select Deploy Database to Microsoft Azure SQL Database.. as shown in the below screenshot.
Figure 2: Choose Option Deploy Database to Microsoft Azure SQL Database…
Once you click on the option, Deploy Database to Microsoft Azure SQL Database.., the below Introduction screen comes up.
Figure 3: Introduction page for deploy database to Microsoft Azure SQL database
Click on the Next Button to reach the below screen:
Figure 4: Connect to Azure Target Connection
In this screen, you need to connect to the destination Azure Server as shown in the below screenshot.
Figure 5: Login into the Target Azure Connection
Once you connected with the destination Azure server, mention the new database name for Azure SQL Server. You can choose the same database name as in the On Premise SQL Server or you can change it. Here, based on the database size and other requirements, you can choose the service tier (as shown in the below screen).
Figure 6: Choose New database Name & choose Service Tier for New database
I have chosen the Basic Service Tier for this article. Now, choose the folder location in which the backup file (.bacpac) will be saved. After that, click on the Next Button.
Figure 7: Choose backup file folder location
After clicking on the Next button, the Summary screen comes where you can verify your specified settings. If setting is fine, click on Finish button.
Figure 8: Verify the specified setting and click Finish
Once Finish button is clicked, it starts deploying the SQL Database to the Azure SQL Server as shown in the below screen:
Figure 9: Deploying database on Azure Platform
Oops! We got an error in the deployment process as shown in the below screenshot:
Figure 10: Oops Got an Error
When I check for the error message online, I found that it comes since I am using the SQL Server management Studio 2016. It is a known issue and the resolution is to use the latest SSMS V17.6.
Figure 11: Information about my current SSMS 2016
So to resolve that issue, I download the SQL Server management Studio SSMS v17.6 and use it for the database migration process.
Figure 12: SQL Server Management Studio V17.6
I perform all the steps from the beginning (choosing On Premises database) till the Summary page and click on the Finish Button. It again starts the deployment process, but this time completed without any error. Finally, click on the Close Button.
Figure 13: Migration to Azure portal is finally successful
Since the Migration process of SQL database from On Premise SQL Server to Azure SQL server got completed successfully, let's check on the Azure SQL Server. If we refresh the Azure SQL Server and expand the Databases node, we can see the Database on Azure SQL Server.
In the previous articles of Azure tutorial, we learn about Azure SQL database, various service tiers, Azure Database creation and how to connect with the Azure SQL Server. As a next step, this article tells about migrating SQL Database to Azure SQL Database using SQL Server Management Studio (SSMS) deploy. In the next few articles, we are going to discuss other methods of SQL database migration to Azure Portal.
Please don’t forget to give your valuable comments for the article.
