Click here to Skip to main content
15,867,771 members
Articles / Hosted Services / Azure

How to Migrate SQL Database to Azure SQL Database using SSMS Deploy

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
25 Nov 2018CPOL4 min read 7.7K   2   2
This post discusses how to migrate SQL database to Azure SQL database using SSMS deploy.

Image 1

Image 2

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:

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

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.

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.., the below Introduction screen comes up.

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

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 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, the 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 is clicked, it starts 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 am using the SQL Server management Studio 2016. It is a 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 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.

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

Image 16

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Database Administrator
India India
I am currently working as a Senior DBA and have around 11 years of experience in database.

Degree:-
Master Degree in Computer(MCA)

Work experience:-
Designing of the database.
Database Optimization.
Writing Complex Stored Procedures,Functions,Triggers etc.
Designing and developing SSIS & DTS packages.
Designing SQL Reports using SSRS.
Database Server Maintenance.

Certification:-
Microsoft certified Sql DBA in Sql server 2008 (MCTS).
Microsoft certified BI professional in Sql server 2008 (MCTS).
Oracle certified profession DBA in ORACLE 10g (OCP)
certified profession DBA in ORACLE 9i (OCP)

My other publication
Technical Blog:- Technologies with Vivek Johari

Moderator and Blogger at BeyondRelational.com

Guest Author and Blogger at sqlservercentral.com

Comments and Discussions

 
PraiseSimple yet powerful Pin
Member 292978725-Nov-18 20:38
Member 292978725-Nov-18 20:38 
GeneralRe: Simple yet powerful Pin
Vivek Johari27-Nov-18 21:58
Vivek Johari27-Nov-18 21:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.