Migration from On-Premise DB to Azure DB

Migrate Using SQL Server Management Studio

SQL Server Management Studio (SSMS) offers two direct ways to transfer a database to an Azure SQL Database. 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.

The primary difference between the two options is that the “Deploy Database to Windows Azure SQL Database” 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 Data-tier Application” 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.

Migration Steps Using the Deployment Wizard

Following are the steps to Deploy SQL Database to Microsoft Azure SQL:

  1. Right-click the database and select the Deploy Database to Microsoft Azure SQL

  2. 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.
  3. Click Next.

  1. Review the settings and click next.

  1. 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.

  1. 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

  1. Right-click the database and select the Export Data-tier Application.

  1. Save the file in an Azure Blob Storage Account. You will need the account name and access key.

  2. Select the container and click next.

  3. 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.


  1. 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.


  1. 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.
  1. Migrate Using SQL Server Data Tools

Since we don’t have the needed flexibility to properly detect and fix the found issues using DACPAC. SQL Server Data Tools – Business Intelligence is a better option to analyze the database objects. Here it allows us to configure single SSIS package manually using OLEDB source (On-premise DB) that will point to Azure DB table i.e., OLEDB destination of Azure SQL DB for migration of related schemas and data.

To proceed with SQL Server Data Tools option, follow the steps below.

 Creating the Main Project

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

  1. Right-click the database to be migrated to Azure, and then click Create New Project.

  2. Add a name to the project and select a path to save the project files.

  3. Click next and wait for the processing to complete.

  1. 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.

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

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

Data Migration Using SSIS

Once the schema is deployed. We can move the data. To do this, use the Import and Export Wizard, from the SQL Server Management Studio.

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

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

Now, do the same for the Azure SQL Database.

  1. 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.

  1. Make sure that all the tables are highlighted and click Edit Mappings.

  2. Select Enable Identity Insert and then click Ok.
  1. Then, in the main Wizard window click next.

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

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

  1. Monitor and the data transfer and close the wizard.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s