Share via

Migrate SQL database from On-Prem to Azure using SQL Database Migration Wizard tool

  • Article

Hello all,

In this blog, I am covering the scenario of migrating the SQL Server database from On premise environment to SQL Azure Virtual machine.

 

There are multiple ways to migrate the application database hosted in On premise to Azure Virtual machine. Few to mention are:

  • Use the Deploy a SQL Server Database to a Microsoft Azure VM wizard
  • Perform on-premises backup using compression and manually copy the backup file into the Azure virtual machine
  • Perform a backup to URL and restore into the Azure virtual machine from the URL
  • Detach and then copy the data and log files to Azure blob storage and then attach to SQL Server in Azure VM from URL
  • If you have an AlwaysOn deployment on-premises, use the Add Azure Replica Wizard to create a replica in Azure and then failover, pointing users to the Azure database instance
  • Use SQL Server transactional replication to configure the Azure SQL Server instance as a subscriber and then disable replication, pointing users to the Azure database instance

In this blog, I am demonstrating SQL Database Migration Wizard tool for database migration.

 

Existing On-premise Application database configuration:

To demonstrate: hosted web based application page Onprem.aspx hosted on Microsoft IIS Web Server. The application connects to On-premise SQL Server database hosted on the localhost server and query the database.

6

The execution of the query completed in 0.02 milliseconds.

Creating a SQL Server Virtual machine:

To build, manage, monitor the Virtual machine on Microsoft Azure cloud, using the unified console “Microsoft Azure Portal”

3

 

Next step is to create a new Virtual machine with Microsoft SQL Server 2016:

4

 

Creating a SQL Server Azure Virtual machine involves the below steps:

  1. Configure basic settings: specifying the name of VM and resource group
  2. Choose Virtual machine size: To specify the VM resources
  3. Configure Optional features: To specify the Storage account, network, high availability options
  4. Configure SQL Server Settings: To specify SQL Port, authentication details

 

Once the request is submitted, a new Virtual machine is deployed.

5

 

To connect to the Virtual machine, we need to use the DNS name mapped with the Virtual machine host name. To connect to the SQL Server instance hosted on Windows Azure, make sure that the port on which SQL Server is listening on is opened on Windows Firewall.

 

Connecting to SQL Server instance using Microsoft SQL Server management Studio tool (SSMS):.

6

 

Below screenshot indicates that, the connection was successful.

7

 

Migrating on Premise Application database to SQL Azure Virtual machine (IaaS):

To migrate the application database from On-premise to Azure VM, I am using SQL Database Migration Wizard tool. The tool can be downloaded from: https://sqlazuremw.codeplex.com/

Once the SQL Migration Wizard tool is installed, migrate the database to SQL Server hosted in Windows Azure Virtual machine:

Select the target Server as: SQL Server.

8

 

Source database is the database hosted in On-premise environment. For demonstration, I am using the database: AdventureWorks2014.

 

9

 

The tool gives an option to specify the objects which are to be migrated.

 

10

 

The tool, collects the script of all the objects in the database and prepare a Bulk Copy command to be executed on Target SQL instance.

11

 

The next step is to specify the Target SQL Server database which is hosted on Azure Virtual machine.

12

 

The tool executes the script generated on the source server and executes on the destination Azure Virtual machine SQL instance.

 

 

13

 

Testing the application

 

Now to test the SQL query execution on the database hosted on Azure VM, in the connection string of the application, pass the DNS name of the Azure Virtual machine which is hosting the SQL Server instance.

 

To test the database connectivity, I have designed a page named IAAS.aspx.

As per the below screenshot, the connectivity to the SQL database hosted on Azure VM is successful and the query execution completed in 0.52 seconds.

5

 

 

Please share your feedback, questions and/or suggestions.

 

Thanks,

Don Castelino | Premier Field Engineer | Microsoft

 

Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.