Azure SQL Managed Instance / Database Migration Service (DMS) / Online Data Migration / Migrate your database to Azure SQL Managed Instance

Posted by Barac in Azure, SQL Server, SQL Tips and Tricks on Dec 23rd, 2019 | 0 comments

Using Azure Database Migration Service (DMS) you can easily migrate your databases from multiple sources to Azure Data platforms with minimal downtime.

For this example we are going to use following:

Azure Database Migration Service (DMS), Premium tier (4 vCores)

DMS Project: Online Data Migration

Source: SQL Server 2017, 8CPU, 64GB Memory

Target: Azure SQL Managed Instance, 16 vCores, 80GB Memory

 

More details about DMS and how to download and install you can find in this MS article.

First step we will be to deploy Database Migration Service.

Specify a name for the database migration service, and choose/create your resource group. 

Configure your pricing tier. For this “Online type” of migration example I will use “Premium” pricing tier.

Select/create VNet. For this example I will the use the same VNet we used for our SQL Managed Instance (Target Server).

Review and create.

Once our service is deployed and online we can create our migration project.

Choose project name, source server type, target server type and type of activity.

The reason why we choose premium pricing tier for our DMS was that the online migration feature requires “Premium” SKU DMS.

Difference between offline and online migration activity type.

When you migrate databases to Azure by using Azure Database Migration Service, you can do an offline or an online migration. With an offline migration, application downtime starts when the migration starts. With an online migration, downtime is limited to the time to cut over at the end of migration. We suggest that you test an offline migration to determine whether the downtime is acceptable; if not, do an online migration.

https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-managed-instance

Once we have our Azure Database Migration Project we need to create new “Online data migration” activity.

Specify source details, connection details for the source SQL Server.

Specify target details, connection details for the target Azure SQL Managed Instance.

You will need application ID that Azure Database Migration Service will use to call restore service. DMS uses the “application ID” to access the target Azure SQL Database Managed Instance resource and Azure Storage Account to perform online migrations.

To get application ID you need to use App registrations page and create new app registration.

Register an application.

Once you have your app registered you should have Application ID 

Next step is to select “Certificates & Secrets” and create new client secret. 

Client secret is a secret string that application uses to prove its identity when requesting a token. Also can be referred to as application password.

You will use this as application key during migration target details configuration. 

After you successfully connected to your target Azure SQL Managed Instance you can select source database you want to migrate.

Now we need to configure migration settings of DMS project.

Choose network share location. Network share can be location on you source SQL Server. Make sure you use domain account which has read permissions on the network share location. 

Next step is to select subscription containing storage account that allows  Azure Database Migration Service to upload database backup files to, and use those files for migration database to our targeted Azure SQL Managed Instance.

Once we have all that we can run our newly created project migration activity.

DMS Project Activity.

Note.

Azure Database Migration Service uses the backup and restore method to migrate your on-premises databases to SQL Database managed instance. Azure Database Migration Service only supports backups created using checksum.

https://docs.microsoft.com/en-us/azure/dms/known-issues-azure-sql-db-managed-instance-online

This DMS post is final part of the migration sequence group of posts.

• DMA (Assessment)
• DEA (Capture the trace / Replay the trace)
• DMS (Migrate database)

More details about DMA with usage example you can see on the following post.

Azure SQL Managed Instances / Migration / Data Migration Assistant (DMA) / Assess on-premises SQL Server instance

More details about DEA with usage example you can see on the following post.

Azure SQL Managed Instances / Migration / Database Experimentation Assistant (DEA) / Evaluate your workload before you migrate your database to Azure SQL Managed Instance

Similar Posts:

• Azure SQL Managed Instance / Migration from on-premises SQL Server instance to Azure SQL Managed Instance
• Azure SQL Managed Instance / Migration / Data Migration Assistant (DMA) / Assess on-premises SQL Server instance
• Azure SQL Managed Instance / Migration / Database Experimentation Assistant (DEA) / Evaluate your workload before you migrate your database to Azure SQL Managed Instance
• Azure SQL Managed Instances / SSMS / Connect to SQL Managed Instance / Private Endpoint
• Azure SQL Managed Instances / SSMS / Connect to SQL Managed Instance / Public Endpoint