Migrating SQL Database to Azure SQL Database using SSMS Deploy

In the previous articles, we had 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 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

1. Using SQL Server Management Studio (SSMS) Deploy method
2. Using SQL Server Management Studio (SSMS) Export Data – tier Application
3. Using Transaction Replication
4. 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 going to 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 server 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.

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 below screenshot.

Once you click on the option Deploy Database to Microsoft Azure SQL Database.., below Introduction screen is comes.

Click on the Next Button to reach the below screen.

In this screen, you need to connect to the destination Azure Server as shown in the below screenshot.

Once you connected with the destination Azure server, mentioned 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, base on the database size and other requirement, you can choose the service tier (as shown in below screen).

I have chosen the Basic Service Tier for this article. Now choose the folder location in which the backup file (.bacpac) will going to be saved.  After that click on the Next Button.

After clicking on the Next button, Summary screen comes where you can verify your specified settings. If setting is fine, click on Finish button.

Once Finish button it clicked, it start deploying the SQL Database to the Azure SQL Server as shown in the below screen.

Oops. We got an error in the deployment process as shown in the below screenshot.

When I check for the error message online, I found that it comes since I using the SQL Server management Studio 2016. It is known issue and the resolution is to use the latest SSMS V17.6

So to resolve that issue, I download the SQL Server management Studio SSMS v17.6 and use it for the database migration process.

I perform the all the steps from the beginning (choosing On Premises database) till Summary page and click on the Finish Button. It again start the deployment process but this time completed without any error. Finally click on the Close Button

Since the Migration process of SQL database from On Premise SQL Server to Azure SQL server got completed successfully, let 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 next step, this article tell 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 gives your valuable comments for the article or you can send me the direct queries to my Email id askvivekjohari@gmail.com

Read More

How to migrate SQL Database to Azure SQL Database using SSMS Export/Import

Migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool

Understanding Azure SQL Database- Introduction

Azure Pricing Models and Service Tiers

Azure – Creating an Azure SQL Database

How to connect with Azure SQL Database

Read more from Tutorials

SQL Tutorial

Azure Tutorial