Gauri Mahajan
Migration Service Instance Dashboard

Migrating homogeneous and heterogeneous databases to Azure SQL Database

March 29, 2021 by

In this article, we will look at how we can use the Azure Database Migration Service to migrate to Azure SQL Database.

Introduction

Databases come in different varieties like relational, document, NoSQL databases, etc. Some of these databases are commercial and some are open-source. Enterprises host data on-premises as well as in the cloud. In a scenario where one has a large data landscape and a variety of options, it’s very obvious that there would be situations where one would want to migrate data from one database to another to exploit the specific features offered by any given database on any given hosting environment. Due to the varying types of databases, database migration can be categorized into two types – homogeneous and heterogeneous.

When the source and target database for data migration are of the identical nature or similar category, for example, relational databases or say SQL server on-premises to Azure SQL Database on Azure cloud, it can be termed as homogeneous database migration. When the source and target database for data migration are of different categories, for example, MongoDB to Azure SQL Data, it can be termed as heterogeneous data migration. Azure offers different tools like SQL Server Migration Assistant, Data Migration Assistant, and Database Experimentation Assistant to assess both types of database and data migration. Once the assessment is complete, the Azure Database Migration Service is used to carry out actual data migration for schema as well as data. In this article, we will learn how to use this service with a focus on migrating to SQL Database on Azure.

Creating Azure Database Migration Service Instance

It is assumed that one already has an Azure account with privileges to operate and administer the database migration service. Assuming that the assessment phase is already completed, and we are into the actual implementation phase where we need to start migrating the schema or data, the first step is to create an Azure Database Migration Service instance. For now, let’s assume that we intend to migrate AWS RDS SQL Server to Azure SQL Database or data from an on-premises SQL Server to SQL Database on Azure.

Navigate to the Database Migration Service on Azure to reach the dashboard page as shown below. This is the point where we can create a new instance.

Azure Database Migration Service Dashboard

Click on the Create azure database migration service, which will pop-up a new screen as shown below. We need to fill up the basic details. One key setting is the service mode – which can be Hybrid or Azure-based. The default value is Azure. Hybrid mode allows you to use an on-premises machine to carry out the migration, while the Azure option uses Azure-based resources.

Migration Service Configuration

The next important configuration to keep in view is the pricing tier. The default selection is 1 vcore. If we click on the Configure tier, it will show the options as shown below. The standard tier is free of cost, while the premium tier is free of cost for six months. Select the right pricing tier and click on the Apply button.

Pricing Tier

The next step is to create a virtual network in which the service is going to operate. We can use an existing virtual network or create a new one as shown below.

Create Migration Service

Tags is an optional step, so if we desire to add tags, click on the Tags button. If not, we can directly click on the Review + create button to deploy an instance of the database migration service. Once the instance is ready, it will look as shown below on the dashboard page.

Migration Service Instance Dashboard

Now that the service is created, we can create a number of migration projects using this service. As we intend to migrate different homogeneous or heterogeneous databases to Azure SQL Database, we will create a new migration project by clicking on the New migration project button. This would pop-up a new wizard as shown below. The first detail to provide in this wizard is the project name. Then we need to select the type of source server from which we intend to migrate data. As shown below, these are the different supported data sources as of the draft of this article. From this list, an example of homogeneous database migration would be SQL Server to SQL Database and heterogeneous database migration would be MongoDB to SQL Database. Select the desired database from the below list.

Source Server Type

Based on the type of selected source database, the target list would show the databases which are supported by the service for data and/or schema migration. Shown below is the case of a homogeneous data migration, where data from SQL Server can be migrated to SQL Server on a virtual machine or Azure SQL Database Managed Instance or Azure SQL Database.

Target Server Type

After selecting the source and target databases, we need to select the type of migration activity we intend to perform. In this case, the supported activities are schema-only migration, offline data migration, online data migration, and an option to just create a migration project for now without defining the type of migration activity. Once done we can click on the Save button to save the configuration.

Homogeneous data migration

One key point to note is that in the case of heterogeneous database migrations, the type of supported migration activities will differ. As shown in the below case, while migrating from AWS RDS SQL Server to Azure SQL Database, it supports online data migration only. This will differ case-to-case depending on the combination of source and target databases.

Heterogeneous data migration

In the case of online data migration from different data sources to SQL Database, one may need to ensure that the source meets certain criteria as shown below. If this configuration is not in place, ensure to update the config before starting the migration.

Online data migration requirements

Finally, once the activity is defined, we would reach the last part of the configuration where we need to provide the connection credentials and mapping information. Show below is the configuration wizard for a schema migration activity, where we need to provide source credentials, target credentials, desired schema from the respective database and then start the activity, which would effectively migrate the schema to Azure SQL Database.

Database Schema Migration Wizard

In this way, by using the Azure Database Migration Service, we can perform different types of migration tasks from homogeneous and heterogeneous data sources and migrate schema as well as data to Azure SQL Database as well as other support data repositories.

Conclusion

In this article, we learned the different types of database migrations, the role of Azure Database Migration Services in the migration process, the configurations required to set up an instance of this service, as well as different types of migrations tasks that are supported for migration data from homogeneous and heterogeneous data sources into Azure SQL Database.

Gauri Mahajan
Azure, SQL Azure

About Gauri Mahajan

Gauri is a SQL Server Professional and has 6+ years experience of working with global multinational consulting and technology organizations. She is very passionate about working on SQL Server topics like Azure SQL Database, SQL Server Reporting Services, R, Python, Power BI, Database engine, etc. She has years of experience in technical documentation and is fond of technology authoring. She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server. View all posts by Gauri Mahajan

394 Views