Infrastructure at your Service

Stéphane Savorgnano

How to migrate from on-premise to an Azure SQL Database

As a SQL Server DBA we have the habit to migrate databases from an instance to another one or to a new environment.
But migrate a database from on-premise to the Azure Cloud is an operation that we don’t deal with every day.

As a reminder, Azure SQL Database is a relational database-as-a-service (DBaaS) which gives the ability to manage your databases directly from the Cloud with the performance of your choose, reliability, security and in the same time get rid of relying infrastructure.

In this blog, I will show you some possibilities of database migration from on-premise to an Azure SQL Database.

 

Via SSMS

The first one is directly from your SQL environment via SQL Server Management Studio.
Click on a database and select “Deploy Database to Microsoft Azure SQL Database”:

Once done we have to specify the Microsoft Azure SQL Database Server where our database will be migrated:

Click on Connect and enter the server name you have already created in the Azure Cloud with the server admin login:

Once connected we have to select the Azure SQL Database settings. It means the service tiers we want to select for our database. This is in fact the business continuity, storage, and performance requirements we want for our database. I will choose a small one with a Standard Edition of Microsoft Azure SQL Database, a maximum database size of 1 GB and a service objective S0 (10 DTUs).
Click Next:

Review the specified settings and click Finish to start the deployment of our on-premise database to an Azure SQL Database.

Exporting tasks are running:

Export is finished:

The database is Online in our Azure dashboard:

And also connectable via SSMS:

 

Import via a BACPAC file

On Management Studio select the database you want to migrate, right click on it and select “Export Data-tier application”:

Save the bacpac file locally and click Next:

Verify the export settings and click Finish to create the bacpac file:

Process is running:

Exporting database in a bacpac file has succeeded:

Now to import the bacpac file in Azure and create an Azure SQL Database, we have to connect to our Azure server via SSMS. In the Object Explorer pane, right-click on the Databases folder and select “Import Data-tier Application”:

Once done, browse the local disk to find the bacpac file and click Next:

Select a new name for the database and give settings for our new Azure SQL Database (we keep the same database settings):

Review the settings and click Finish to start the import process:

Once finished I have a succeed message:

I can see my new database in SSMS and in my Azure dashboard:

 

Database Migration Assistant

Database migration assistant can create an assessment to know if a specific database can be easily exported to an Azure SQL Database and during a second step migrates the database.
Let see how it’s working.

Open Database Migration Assistant, click on the “New” Menu and select Assessment as Project type. Enter a project name and click the “Create” button:

Keep the selected report type, this will check the database compatibility and check feature parity and click Next:

Connect to a server, select one or multiple databases and add it/them to the project:

Start the assessment:

Once done we have a list of Unsupported and partially-supported features for SQL Server parity.
We have for Compatibility issues: migration blockers, behavior changes, deprecated features and information issues.
All those issues have to be taken into consideration before the migration.
Migration can be done without any changes but the database will not be 100% usable.

Despite the assessment which shows that our database is not 100% compatible with Azure SQL Database let’s continue and try a migration project.
Select Migration as Project type and add a project name.
Click the “Create” button:

Connect to the source server and select the database to migrate. Click the “Next” button:

Connect to the target server located in the Cloud (the one we created here) and select the Azure SQL Database we created empty during my previous blog:

In this screen we can select schema objects that we want to migrate to Azure SQL Database. We can see that we have some validation issues as shown before by the assessment.
Click on the “Generate SQL Script” button:
As written I will migrate all objects as blockers can be fixed later.

The script for the select schema objects has been generated, click the “Deploy schema” button to deploy the schema to the Azure SQL Database:

We can now migrate the data by clicking the “Migrate Data” button:

Let all tables selected and click “Start data migration”:

Data for the 3 tables have been migrated successfully:

I can now browse my Azure SQL database in my Management Studio on-premise. All my objects are available and my tables are filled with my data:

 

It was 3 possibilities to migrate an on-premise database to an Azure SQL Database.
I hope it can help 😉

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Savorgnano
Stéphane Savorgnano

Consultant