Azure: Azure SQL Database migration using Data Migration Service

by | Jan 1, 2024 | App Service, Azure, Azure SQL Database, Compute, Databases

In this post, I will show how we can migrate a local sql server database to Azure SQL Database service using the Azure Migrate Service.

Prerequisites:

  • An active Azure subscription
  • SQL Server (On-Premises)
  • An Azure SQL Database

 

A few words about Microsoft Azure Migrate Service

Microsoft Azure Migrate service streamlines migration resources from an on-premises environment to Azure, i.e. databases, servers, web apps etc. What Azure Migrate brings to organizations is a comprehensive framework that includes all the features which can assist in implementing migrations.

Azure Migrate Stages

  • Discovery & Assessment
    • Discovers and assess VM’s, databases, barebone servers that hosted on-premises
  • Migration
    • This step migrates the resources that have been discovered and assessed in the previous step.

Azure Migrate service also supports more complex scenarios with layered stages, but in this post I will focus on the stages mentioned above.

In the Azure Migrate service left-hand side menu, select  Migration goals – Databases (only), and click the button, Create project.

Fill in the required information, such as Subscription, Resource group, Project, Geography, Connectivity method and select Create.

Then from the Assessment tools select + Assess

To assess a database, you will need to download and install Data Migration Assistant (DMA )

 

Demo time!

For the purposes of this demo, I used a local instance of SQL Server 2022 Express and, of course, the Azure service I mentioned in the prerequisites section.

Assess database(s)

In order to assess an on-premises databaseI will need the Data Migration Assistant (DMA) from this link.

Create New Assessment

From the left-hand side menu, click the + button,

 

 

 

 

 

 

 

 

 

 

 

select Project type “Assessment“, type a Project name, select “Database Engine” as Assessment type, Source server type, “SQL Server”, Target server type “Azure SQL Database”, and press Create.

 

On the [1 Options] section, select Check database compatibility, Check feature parity and click Next

On the [2 Select sources] section, connect to the On-premises SQL Server instance, fill in the required values, i.e. Server name, Authentication type and press Connect.

Select the source, i.e. database, and click Add.

Encrypting the connection through the Connection Properties is recommended.

In the next step, just click on the Start Assessment button to initiate the assessment process.

Once the assessment completes, the Migration Assistant will display a list of Unsupported and Partially-supported features, as shown in the screenshot below. By clicking the “Upload to Azure Migrate” button, you can upload the results to the Azure Migrate service.

On the Connect to Azure form, select “Azure” from the Azure Environment drop-down

Using your Microsoft Account, sign in to the Azure Migrate Project you created earlier, and then select Upload.

Once the upload is complete press ok to close the information message.

The assessment results can now be found in the Azure Portal, as shown in the screenshot below.

Migrate Database

In the next part of my article I will cover the migration of a database that contains demo data.

New Migration

To begin with Migration process on the Project type select Migration, type a Project name, select as “Source server type”, SQL Server , Target server type “Azure SQL Database”, “Migration scope”, Schema and data and press Create.

Choose your server name, e.g., localhost, your authentication type, and click Connect.

Encrypting the connection through the Connection Properties is recommended.

Select the database to migrate to Azure SQL from your source server and click Next

Connect to the target server. Select the server name and authentication credentials, type them in, click Connect, and choose the target (empty) database and select Next.

Determine which schema objects from the source database should be migrated to the Azure SQL target database and click the Generate SQL script button.

Very shortly, the script that will run and execute Migration has been created. To initiate the migration process, press the Deploy schema button.

A few minutes later, the source db schema has been created to the target db, as the screenshot below shows.

Once the deployment of the schema is successfully completed, the deployment of the data takes over. To get start, choose the Migrate data button.

Now I will select the tables whose data I want to transfer to the target database and the process will start as soon as I press the Start data migration button.

Migration is running..

Depending on the amount of data contained in each table, the process is completed

Useful Links

468
Share This