Migrating SQL Server Database to Azure SQL (PaaS)

In this article, we will highlight SQL Server Data migration to Azure SQL (PaaS). You can find different approaches for this migration process because SQL Server offers different types of tools and features for this migration. Some of these are:

Database Migration Assistant
SQL Server Management Studio Deployment Wizard
SQL Server to Azure SQL replication

Data Migration Assistant DMA :

The Data Migration Assistant (DMA) enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server and Azure SQL Database. It recommends performance and reliability improvements for your target environment. It allows you to not only move your schema and data, but also uncontained objects from your source server to your target server.

Features which are provided by Database Migration Assistant play a key role in the migration process.
These are:
· DMA explores compatibility problems which can block or stop migration.
· DMA explores the current database features which are unsupported or partially-supported by SQL Azure.

These two features are very useful for us because when we start to migrate an on-premises database to SQL Azure, some of its features or objects may not be supported by Azure SQL. In this case, we need to fix or find the solution to migration issues. At this point, we will take advantage of Database Migration Assistant.

For More : https://blogs.msdn.microsoft.com/datamigration/dma/

Login into the Azure Portal.

From All Services select : SQL databases

Click on +Add button to create new SQL Database. Specify the Name and resource group.

we will create a blank database in SQL PAAS platform which will absorb on premise data

Click Pricing tier to specify the service tier, the number of DTUs, and the amount of storage. Explore the options for the number of DTUs and storage that is available to you for each service tier. After selecting the server tier, the number of DTUs, and the amount of storage, click Apply.

Below is the snap of our On-premise database, that we will be migrating to SQL Azure.

Once the provisong has been completed successfully, you can download the Data Migration Assistant tool from the given link Click here . Install this tool in your on-premise machine.

Launch the Data Migration Assistant Tool.

Click (+), Select Assessment and provide other details as shown below.

Specify the on-premise Server Name and authenticate as shown below.

+Add Sources

Click on Start Assessment to begin assessing your on-premise database before migration.

As you can see, Assessment is completed successfully. You can export the report in Json format and save it in your local drive.

Also check for the compatibility issues as shown below.

Click (+) button to begin Migration process.

Select the source that is your on-premise database as shown below.

Select the database that you wish to migrate to SQL Azure.

Select the target, here you mention the server name that we created while provisioning ‘SQL Azure PaasDB’ as shown below. Then click on connect.

This will give you error. Note down the IP address from the error details.

Go to Azure Portal, click on set server firewall as shown below.

Here you mention the IP address that was shown in the Error details from Data Migration Assistant tool. Click on Save button and once saved go back to Data Migration Assistant tool.

Select the database as shown below. Click Next

Select the Schema Objects from your source database that you would like to migrate to Azure SQL Database and click Generate SQL Script.

Once the script is generated successfully. Click on Migrate Data as shown below.

Select the table and click Start data Migration as shown below.

As you can see below we have successfully migrated on-premise database to SQL Azure using Data Migration Assistant Tool.

You can access the SQL Azure using SSMS as shown below, note to provide username and password of the SQL server that we created while provisioning SQL Azure paasDB.