Haroon Ashraf
On-Premises SQL Database SQLDevArticles2021

Migrating on-premises databases to Azure SQL Database in Azure Data Studio

June 23, 2021 by

This article teaches how to migrate a lightweight on-premises SQL database to an Azure SQL database using Azure Data Studio.

Additionally, the readers of this article are going to get some professional life tips regarding the lightweight migration to the Azure SQL database.

This article shows the importance of using the specialized tools/utilities to migrate on-premises databases to Azure as compared to trying out whatever looks suitable.

On-Premises Database to Azure Migration

Migrating an on-premises database to Azure is one of the widest database migration operations taking place today at least by those individuals and organizations who are happy with the Microsoft- based technologies and tools.

Let us get some more understanding of the concepts before we get our hands on the implementation steps.

What is an on-premises SQL database?

An on-premises database means a SQL database hosted on an in-house SQL database server that is kept, managed, and maintained by the organization itself.

Why do on-premises SQL databases need to be migrated to Azure?

There can be many good reasons to migrate an on-premises SQL database to an Azure SQL database including the following:

  1. Saving the upfront cost of buying and upgrading a full-time server
  2. Scalability to scale up and down your database without buying new hardware (servers)
  3. Ease of use by not worrying about patches and updates to be applied to the server since it is managed by Microsoft
  4. Immense flexibility to easily integrate with many other Azure products and tools
  5. No need to pay any maintenance fees for your server including freeing yourself from worrying about disaster recovery plans

What is a lightweight SQL database?

We are using the term lightweight SQL database to highlight the fact that we are not talking about a complicated or sophisticated highly transactional database with inherited complexity rather we are referring to a relatively simpler database with a fair amount of database objects and data.

Is Azure Data Studio capable of migrating a database to Azure?

Yes, with the relevant extensions intact, we can easily migrate a lightweight SQL database to an Azure SQL database without needing to go through any other complicated process.

Which Extensions are required for database migration?

The following extensions are required to be able to migrate to Azure:

  1. SQL Database Projects extension
  2. SQL Schema Compare extension

How safe is this database migration?

Well, you are using the industry-standard tools behind it so if the migration does not fall over or terminate in the middle and completes successfully there is no reason not to say it is safe and reliable.

Migrating On-Premises Databases to Azure SQL database

Let us go through the steps that will help to migrate a sample on-premises SQL database to an Azure SQL database.

Prerequisites

In order to implement all the steps of the walkthrough please ensure that the following requirements are met:

  1. Local SQL Server has been installed on your machine
  2. You have a basic know-how of Azure Data Studio and it is installed with SQL Server Projects and SQL Schema Compare Extension
  3. Azure account has been signed up and a SQL Server resource is provisioned to create Azure SQL databases
  4. You are familiar with databases and can comfortably write and run SQL scripts comfortably

Mimicking (assuming) Business Requirement

Let us assume that your organization has just sent a new business requirement to migrate a lightweight SQL database to Azure in a move to analyse the results (of migration).

Propose Solution

You have proposed to do this migration using Azure Data Studio.

About On-Premises SQL Server Version

This walkthrough is based on a specific scenario where an on-premises database is hosted on a SQL Server 2017 server and must be migrated to Azure SQL Database.

Now SQL Server 2017 has a compatibility level of 140 so after the migration, the Azure SQL Database will also have the same compatibility level.

Tip

Although the database migration walkthrough in this article is based on on-premises SQL Server 2017 it should work for other versions provided there is no major clash.

Setup a lightweight on-premises SQL database

First, we need to set up a lightweight SQL database to represent an active on-premises database that needs to be migrated now.

Open Azure Data Studio and connect to your local SQL instance and create a new database called SQLDevArticles2021 by running the following script against the master database:

Check the on-premises database

Please switch to the Connections and view the newly created on-premises database by navigating it under the Databases folder in your local SQL (instance) connection:

On-Premises SQL Database SQLDevArticles2021

View the table contents by running the following T-SQL script against the database SQLDevArticles2021:

The output is as follows:

Article table data coming from on-premises SQL database

Export the on-premises database to BACPAC file

In the next step, the on-premises database will be exported to a BACPAC file that contains both data and the structure of the database.

Right-click on the database SQLDevArticles2021 and run Data-tier Application wizard by clicking it:

Starting Data-tier Application wizard

Select the last option that asks you to Export the schema and data from the database to a logical BACPAC file format as shown below:

Exporting the schema and data to .bacpac file

Select a suitable location to store the BACPAC file of the on-premises database and click Next:

Selecting Source Server, Source Database and Location of the BACPAC file

Finally, click the Export button to initiate the process of creating the BACPAC file:

Exporting BACPAC file

A successfully created BACPAC file status is shown below:

BACPAC file created successfully

Deploy on-premises database (data and schema) into Azure SQL Database using BACPAC:

Now is the time to migrate the database to the Azure SQL database.

Under Connections sign in to your Azure account and connect to your SQL Server resource in Azure.

Right-click on the SQL Server (Azure) and initiate once again Data-tier Application wizard:

Running Data-tier Application wizard in Azure

This time, you have to select the third option that says Create a database from .bacpac file followed by clicking Next:

Selecting the option to create database from a .bacpac file

Select the location of the BACPAC file and your target server that is the address of the Azure SQL Server (resource) and rename the Target Database as SQLDevArcticles2021:

Selecting File Location, Target Server and Target Database (name)

Then click the Import button and after a couple of minutes you are going to be notified about the successful import (deployment) as shown below:

Import BACPAC successful

Check the migrated Azure SQL Database

Right-click on the newly created Azure SQL database and expand the Tables folder:

Checking migrated Azure SQL Database tables

Please run the same query to view the rows of the Article table against Azure SQL Database:

The output is as follows:

Viewing Article table data in Azure SQL Database

You can also view your recently migrated database in Azure Portal.

The quickest way while staying in Azure Data Studio is to right-click on the Azure SQL Database and click Open in Azure Portal:

Opening the Azure database in Azure Portal

The results are as follows:

Viewing the migrated database in Azure Portal

Congratulations! You have successfully migrated a lightweight on-premises SQL database into Azure SQL database in Azure Data Studio.

A Word of Advice

Migrating a database from an on-premises server to an Azure SQL database is a very common task in Azure database migrations which can be easily handled by using the correct method and approach and if it a relatively simple migration scenario where there is nearly no possibility of conflicts and clashes between the source and the target then the above method is a good candidate but the final decision lies with the individual or the organization responsible for choosing the most optimum approach.

The BACPAC approach is encouraged by the product experts at Microsoft for database migration with some caveats (warnings).

Summary

In this article, we learned to create and deploy the BACPAC file format in Azure Data Studio to migrate a sample on-premises SQL database to Azure.

Table of contents

Two ways to build SQL Database Projects in Azure Data Studio
How to use SQL Server DACPAC extensions in Azure Data Studio
Declarative Database Development in Azure Data Studio
Using the DB Snapshot Creator Extension in Azure Data Studio
Migrating on-premises databases to Azure SQL Database in Azure Data Studio

Haroon Ashraf
Azure Data Studio, Migration

About Haroon Ashraf

Haroon’s deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional. He holds BSc and MSc Degrees in Computer Science and also received the OPF merit award. He began his professional life as a computer programmer more than a decade ago, working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data. His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM). He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential. View all posts by Haroon Ashraf

414 Views