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:
- Saving the upfront cost of buying and upgrading a full-time server
- Scalability to scale up and down your database without buying new hardware (servers)
- Ease of use by not worrying about patches and updates to be applied to the server since it is managed by Microsoft
- Immense flexibility to easily integrate with many other Azure products and tools
- 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:
- SQL Database Projects extension
- 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:
- Local SQL Server has been installed on your machine
- You have a basic know-how of Azure Data Studio and it is installed with SQL Server Projects and SQL Schema Compare Extension
- Azure account has been signed up and a SQL Server resource is provisioned to create Azure SQL databases
- 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
-- 1 Create SQLDevArticles2021 database CREATE DATABASE SQLDevArticles2021; GO USE SQLDevArticles2021; GO -- 2 Create author table CREATE TABLE [dbo].[Author] ( [AuthorId] INT IDENTITY (1, 1) NOT NULL, [Name] VARCHAR (40) NOT NULL, [RegistrationDate] DATETIME2 (7) NULL ); -- 3 Create article tables CREATE TABLE [dbo].[Article] ( [ArticleId] INT IDENTITY (1, 1) NOT NULL, [Title] VARCHAR (300) NOT NULL, [Published_Date] DATETIME2 (7) NOT NULL ); -- 4 Populate author table SET IDENTITY_INSERT [dbo].[Author] ON INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (1, N'Asif', N'2018-01-01 00:00:00') INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (2, N'Peter', N'2018-02-01 00:00:00') INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (3, N'Sarah', N'2018-03-02 00:00:00') INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (4, N'Adil', N'2018-04-02 00:00:00') INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (5, N'Sam', N'2019-01-01 00:00:00') SET IDENTITY_INSERT [dbo].[Author] OFF -- 5 Populate article table SET IDENTITY_INSERT [dbo].[Article] ON INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (1, N'Fundamentals of Database Programming', N'2018-01-02 00:00:00') INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (2, N'Advanced Database Programming', N'2018-01-03 00:00:00') INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (3, N'Understanding SQL Stored Procedures ', N'2018-02-02 00:00:00') INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (4, N'Database Design Concepts', N'2018-03-02 00:00:00') INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (5, N'Power BI Desktop Fundamentals', N'2019-01-02 00:00:00') SET IDENTITY_INSERT [dbo].[Article] OFF; GO |
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:
View the table contents by running the following T-SQL script against the database SQLDevArticles2021:
|
1 2 |
-- View Article table (data) SELECT a.ArticleId,a.Published_Date,a.Title FROM dbo.Article a |
The output is as follows:
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:
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:
Select a suitable location to store the BACPAC file of the on-premises database and click Next:
Finally, click the Export button to initiate the process of creating the BACPAC file:
A successfully created BACPAC file status is shown below:
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:
This time, you have to select the third option that says Create a database from .bacpac file followed by clicking Next:
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:
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:
Check the migrated Azure SQL Database
Right-click on the newly created Azure SQL database and expand the Tables folder:
Please run the same query to view the rows of the Article table against Azure SQL Database:
|
1 2 |
-- View Article table (data) in the migrated Azure SQL Database SELECT a.ArticleId,a.Published_Date,a.Title FROM dbo.Article a |
The output is as follows:
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:
The results are as follows:
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 |
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
- SQL Machine Learning in simple words - May 15, 2023
- MySQL Cluster in simple words - February 23, 2023
- Common use cases of SQL SELECT Distinct - February 2, 2023