Aws Amazon Rds

How to migrate from on-premises to Azure SQL database

17 min read

Jan 15, 2016

The Azure SQL Database is improving its capabilities day-by-day. The “Cloud-first” strategy used by Microsoft is also an incentive to start using the Azure’s SQL Database as a Service (DaaS) offer. In this article I’ll explain all the steps to move your database from on-premises to Azure, using three different approaches. You will need to choose the right one based on your migration strategy and on the database that you are migrating. Don't forget that not all the features supported on-premises are supported on Azure, so some additional work may be needed prior to the migration. I’ll show how to migrate a database to Azure SQL Database by using two general methods:

Using the SQL Server Management Studio - Recommended when there are no code compatibility issues blocking the cloud migration.
Using the SQL Server Data Tools - This approach is highly recommended when there are migration barriers, as the process of detecting and fixing the issues is simpler and more direct.

If you are in doubt about which one to use, the recommendation is to start by using the SQL Server Management Studio approach and, in case of failures, proceed with the SQL Server Data Tools.

Migrate Using SQL Server Management Studio

SQL Server Management Studio (SSMS) offers two direct ways to transfer a database to an Azure SQL Database. To proceed, connect to the SQL Server instance and run either the “SQL Database Deployment Wizard” or the “Export data-tier application” option from SQL Server Management Studio.

If you cannot find the preferred option, you will need to update your SQL Server Management Studio (SSMS), which is now a free standalone product. You can do this by downloading the latest version. The primary difference between the two options is that the “Deploy“ option requires an existing Database server in Azure and will directly deploy the on-premises database to that location. The difference is that afterwards the “Export” option will create a file to be imported from the Azure portal. The exported file can be loaded straight to an Azure Blob Storage account, which will help avoid an extra step to copy the file (recommended). NOTE: For both options, an Azure Blob Storage account with a container and an Azure SQL server are needed.

Migration Steps Using the Deployment Wizard

Right-click the database and select the Deploy Database to Microsoft Azure SQL Database
Fill in the required fields. The server information is for the target (Azure SQL Database server). The settings to define the price tier are also configured at this stage. The bacpac file will be created locally and then applied on the Azure SQL Server, and because of this, we will need to store the bacpac file in a temporary place in the server.
Click Next.

Review the settings and click Finish.
Wait for the process to complete. At this stage the wizard will validate the database, create the DACPAC file, and apply the Azure SQL Server to create the database.

The database is now ready to use the server admin account to access the Azure SQL Server.

Migration Steps using the Export Data-Tier Application Process

Right-click the database and select the Export Data-tier Application.
Save the file in an Azure Blob Storage Account. You will need the account name and access key.
Select the container and click Next.
Click Finish, and wait for the processing to complete.
Once the process completes a "Success" message is seen as shown in the screen below. Otherwise, there are items needing to be resolved to make the database capable of being converted into an Azure SQL Database.
Connect to the Azure portal and choose the SQL Servers.
Select the SQL Server location where the database should be created, and then click the Import Database icon as shown below.
Complete the required settings, including the BACPAC file location, price tier, and server administrator’s password, and then click Create.
Once the process completes, the database will be seen in the list.

Migrate Using SQL Server Data Tools

By using the SSMS to migrate the database using a DACPAC, we don’t have the needed flexibility to properly detect and fix the found issues. For this purpose, the SQL Server Data Tools - Business Intelligence is a better option to analyze the database objects. To proceed with this option, follow the steps below.

Creating the Main Project

Using the SQL Server Data Tools BI, click the SQL Server Object Explorer tab and connect to the on-premises instance:

Right-click the database to be migrated to Azure, and then click Create New Project.
Add a name to the project and select a path to save the project files.
Click next and wait for the processing to complete.

After the project is created, right-click the project root, go to properties and change the Target Platform to Azure SQL Database. Save and close.

Right-click the project and click Rebuild. If problems are detected, all the errors will be shown in the Error List.

Go to File->New->Project, give a project name (I will name it AWAzure) and in the Solution option, click Add to solution:

Creating the New Schema

In order to filter the non-supported features and find the code to be corrected, the next step is a Schema Comparison creation. Follow the steps shown:

Now, select the options. Click the icon shown.

In the Schema Compare Options window, click to clear the following known non-supported items:

Aggregates
Application Roles
Assemblies
Asymmetric Keys
Broker Providers
Certificates
Contracts
Defaults
Extended Properties
Filegroups
FIleTables
Full-Text Stoplists
Full-Text Catalogs
Full-Text Indexes
Message Types
Partition Functions
Partition Schemes
Queues
Remote Service Bindings
Rules
Sequences
Services
Symmetric Keys
Used-Defined Types (CLR)
XML Indexes
XML Schemas Collections

Click Ok and save the Schema Comparison, as it can be useful later.
Select the source: The On-premises database.

Select the Target: The empty SQL Server create project.

We will have the following:

Now, click Compare. Wait for the process to complete and then click Update (click YES in the confirmation pop-up), to update the selected target.

Next, go to the AWAzure (the target) project, right-click on the root, go to properties, and change the Target Platform to Azure SQL Database.

Click Save and Close the screen.

Resolving Problems

Now it’s time to resolve the problems. Check the errors tab and double click on each found item to open the code. Resolve the issue and save the file.

Use the filter to ensure you are dealing with the right project.

Deploying the Schema

After the schema revision, we can publish the database.

To publish the database, right click the AWAzure project, and click Publish.

Edit the target instance and connect to the Azure SQL Server:

Fill in the database name and click Publish.

Moving the Data

The schema is deployed. Now it is time to move the data. To do this, use the Import and Export Wizard, from the SQL Server Management Studio.

Connect to the on-premises instance, right click the database used as the data source and follow the steps shown:

In the wizard, confirm the Server name and the source database, and then click Next.

Now, do the same for the Azure SQL Database.

In the Destination field, select SQL Server Native Client 11.0, fill in the server name, and select the target database.
Click Next.
For this step, keep the first option selected, and then click Next.

Select all the tables and views from the source. Notice that SQL Server will automatically map the target tables on Azure. About data hierarchy: If foreign key constraints are being used in the database, the data migration should be made in phases to avoid failure. This needs to be analyzed prior to the final migration.

Make sure that all the tables are highlighted and click Edit Mappings.
Select Enable Identity Insert and then click Ok.
Then, in the main Wizard window click Next.

Make sure the Run immediately check box is selected and click Next.

In the following screen, review the options, and then click Finish.

Monitor and the data transfer and close the wizard.

That’s it. I hope that the steps were clear and this article was useful. If you have questions, do not hesitate in post your comment or contact me using twitter ( @murilocmiranda). “See” you in another article. Discover more about our expertise in SQL Server.