How to migrate SQL Database to Azure SQL Database using SSMS deploy

By | November 1, 2018

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.

Figure 1- On Premises AdventureWorks2012 Database for Migrating to Azure

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

Choose Option Deploy Database to Microsoft Azure SQL Database...

Figure 2- Choose Option Deploy Database to Microsoft Azure SQL Database…

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

Introduction page for deploy database to Microsoft Azure SQL database

Figure 3 – Introduction page for deploy database to Microsoft Azure SQL database

Click on the Next Button to reach the below screen.

Choose Azure Target Connection, New Database name & backup file location

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.

Login into the Azure Target Connection

Figure 5 – Login into the Target Azure Connection

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).

Choose New database Name & choose Service Tier for New database

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 going to be saved.  After that click on the Next Button.

Choose backup file folder location

Figure 7 – Choose backup file folder location

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

Verify the specified setting and click Finish

Figure 8 – Verify the specified setting and click Finish

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

Deploying database on Azure Platform

Figure 9 – Deploying database on Azure Platform

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

Oops Got an Error

Figure – 10. Oops Got an Error

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

 Information about my current SSMS 2016

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.

SQL Server Management Studio V17.6

Figure 12 – SQL Server Management Studio V17.6

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

Migration to Azure portal is finally Successful

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 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

Understanding Azure SQL Database- Introduction

Azure Pricing Models and Service Tiers

Azure – Creating an Azure SQL Database

How to connect with Azure SQL Database

 

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.

Leave a Reply