Simple Steps to Migrate to Azure SQL Database

Planning for Azure SQL Migration? Here are the simple steps to migrate to Azure SQL database. 

Almost every business is recognizing the importance of moving data to the cloud platform. However, many just assume that the cloud is suitable only for saving files and documents. The functionalities of the cloud are far-reaching as you can also use the cloud for storing database files.

Microsoft SQL Server is presently one of the best and popular on-premises database management tools used by organizations for saving their business data. On the other hand, many enterprises still have doubts to migrate SQL database to Azure cloud. However, they fail to realize the different facilities they can leverage for the migration of their SQL database to the cloud.

Also Read: How to Create a Database Using Azure Portal and SSMS?

Steps to Migrate to Azure SQL Database

If you’re all ready to move your SQL database to Azure, then you are in the right place. The following discussion dives into details of important steps for the migration of SQL server to Azure. Let’s begin with the prerequisites for the Azure SQL migration and then we’ll move to the steps of Azure SQL database migration.

Prerequisites for the Azure SQL Migration

The first thing that you should ensure before you migrate SQL database to Azure SQL is the list of prerequisites. You should have SQL Server 2016 or later versions for the migration process. Then, users should download and install the Data Migration Assistant (DMA) version 3.3 or later. Another important prerequisite is the creation of a Microsoft Azure Virtual Network for Azure Database Migration Service.

You can utilize the Azure Resource Manager deployment model for site-to-site connectivity to on-premises source servers. Users should ensure that Network Security Group rules of their virtual network do not restrict inbound communication ports to Azure Database Migration Service (DMS).

It is also important for users to ensure the ideal configuration of Windows Firewall for database engine access. In addition, it is also important to create server-level IP firewall rules for the Azure SQL Database server to ensure the flexible accessibility of target databases by Azure Database Migration Service. You have to provide the subnet range of the virtual network applied for Azure DMS.

Other critical prerequisites include ensuring CONTROL SERVER permissions for credentials used for connecting to source SQL Server instance. You also have to make sure that credentials for connecting to target Azure SQL Database instance have CONTROL DATABASE permissions on target Azure SQL databases.

migrate SQL server to Azure

Step 1: Assessment of SQL Server Database

Before you can start to migrate SQL database to Azure cloud, it is important to evaluate the SQL Server database. This can help in finding out any issues that can possibly create setbacks for migration. You can use the Data Migration Assistant version 3.3 or later versions for evaluating the on-premises SQL database. Here are the steps to evaluate your SQL database before starting Azure data migration.

  • Select the “New” icon in the Data Migration Assistant and then the “Assessment” project type. Download the latest version of Microsoft Data Migration Assistant here:
  • Enter a project name in the “Source server type” text box and then select “SQL Server”. Now, select “Azure SQL Database” in the “Target server type” text box. Then, click on “Create” for creating the project.
  • Click on “Next” in the “Options” screen of Data Migration Assistant.
  • Open the “Select sources” screen and then the “Connect to a server” dialog box. Here, you have to provide connection details to the SQL Server followed by selecting “Connect”.
  • Select “AdventureWorks2012” in the “Add Sources” dialog box and then choose “Add” followed by selecting “Start Assessment”.
  • Upon completion of the assessment, you can find the result like the following example.

Step 2: Migration of Sample Schema

The next important step to migrate SQL server to Azure involves the use of DMA for migrating sample schema to Azure SQL. Here are the steps for migration of the AdventureWorks2012 schema to Azure SQL database.

  • Click on “New” icon in Data Migration Assistant and then choose “Migration” in the “Project type.”
  • Enter a project name and then select “SQL Server” in the “Source server type” text box. Also, select “Azure SQL Database” in the “Target server type” text box.
  • Select “Schema only” under the “Migration Scope”. Now, you can find the following interface.

welcome to data migration assistant

  • Click on “Create” for creating the project.
  • Specify the source connect details for SQL server by clicking on “Connect” and then selecting the “AdventureWorks2012” database.

migration of sample schema - select source

  • Click on “Next” and then specify the target connection details for Azure SQL Database followed by selecting “Connect” and then the “AdventureWorksAzure” database that is pre-provisioned in the Azure SQL database.

Azure SQL Migration - select target

  • Click on “Next” for moving to the “Select objects” screen where you can specify the schema objects of “AdventureWorks2012” database that you want deployed on Azure SQL database.

data migration assistant - select objects

  • Select the “Generate SQL scripts” option and then review the SQL scripts for errors.

select script and deploy schema

  • Finally, click on “Deploy schema” for deploying the schema to Azure SQL Database. After deployment of the schema, check the target server for identifying any anomalies.

script and deploy schema

Step 3: Registration of Microsoft Data Migration Resource Provider

  • Sign in to the Azure Portal and then select “Subscriptions.”
  • Choose the subscription that you want to use for creating instances of Azure Database Migration Service. Click on “Resource providers.”
  • Find the option for migration and then click on “Register” for “Microsoft.DataMigration.”

Step 4: Creation of an Instance

The following steps are essential to migrate SQL database to Azure.

  • Select “Create a resource” on the “Home” page and in the Azure portal menu followed by searching and clicking on “Azure Database Migration Service.”
  • Click on “Create” on the “Azure Database Migration Service.”
  • In the “Create Migration Service” screen, enter the service name, subscription and name of new or existing resource groups.
  • Choose the location for creating instance of Azure DMS.
  • Select an existing virtual network or develop a new one.
  • Choose the pricing tier.
  • Click on “Create” for creating the service.

Step 5: Creation of the Migration Project

The highlight of this discussion is the creation of a migration project using Azure database migration services. After creation of Azure DMS, you have to find it in the Azure portal and follow the steps mentioned below.

  • Select “All services” in the Azure portal menu and then search and click on “Azure Database Migration Services.”
  • In the next screen, choose the Database Migration Service instance you have created
  • Choose the option of “New Migration Project.”

creation of migration project

  • Specify the name for the project and then select SQL Server as the Source server type and Azure SQL Database as the Target server type.
  • Then select “Offline data migration” in the “Choose type of activity” checkbox.

new migration project

  • Finally, click on “Create and run activity” to start your database migration project and run it successfully.

Also Read: Factors Affecting Disk Space Requirements for SQL Server

Are You Ready to Move Your SQL Database to Azure Now?

You can clearly notice the potential ease to migrate SQL database to Azure from the above-mentioned information. In addition to the steps mentioned here, readers have to dive into further research on a granular level.

It is highly essential for readers to understand the intricacies of database migration to Azure cloud. You can access reliable and highly efficient information for better results with your migration project from official Microsoft documentation.

The learning resources such as whitepapers and hands-on labs related to the migration of SQL databases to Azure cloud. In addition, readers should also explore the various Microsoft and third-party services and tools that can support them in different scenarios of database management and migration. Start preparing now for a successful migration project.

Leave a Comment