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

Posted by Barac in Azure, SQL Server on Oct 30th, 2019 | 0 comments

Before we migrate our database to the cloud we need to assess our on-premises SQL Server instance and Data Migration Assistant (DMA) tool can help us with that.

Using Data Migration Assistant (DMA) tool we can detect potential compatibility issues which may impact our database functionality after migration to Azure SQL managed Instance.

DMA main functionalities:

• Discover issues that can affect an upgrade to an on-premises SQL Server
• Discover new features in the target SQL Server platform
• Migrate an on-premises SQL Server instance to a modern SQL Server instance

If you want to find more details about DMA and how to download and install you can find in this MS article.

As well you can download PowerPoint presentation from one of our Auckland SQL User Group Meetup session about database migration from on-premises SQL Server to the cloud.

Migrating to Azure, and why should you backup LOG files

Monday, Sep 17, 2018, 12:00 PM

Microsoft New Zealand Ltd
Level 5, 22 Viaduct Harbour Ave Auckland, NZ

18 database professionals Went

We found a spot at Microsoft. Come join us for our first Meetup at Microsoft in 2018. We are having a free lunch time session where you can listen to Zoran Barac talk about migrating SQL to Azure, and Adrian Sullivan talk about LOG files. We’ll even give some drinks and pizza to make it worth your while.

Check out this Meetup →

SQL Migration Road map, phase by phase.

One of the important phase is “Assess”.

We will use DMA to assess our SQL Server Instance databases (SQL Server 2017) before we migrate them to Azure SQL Managed Instance.

Run the DMA and create new project. For this example we will use Data Migration Assistant (DMA) just for the Assessment, Database Experimentation Assistant (DEA) for capturing and replaying the workload and Azure Database Migration Service (DMS) for moving our SQL Server databases to the cloud (Azure SQL MI). Although you can use DMA for schema and data migration as well I wouldn’t recommend this tool for the big databases migration, just for the assessment. 

Choose the project type (Assessment), source and target server type. In this example I used SQL Server as a source and Azure SQL Managed Instance as a target server type.

Next step is to select report type.

In this example we will check possible compatibility issues that may block our migrating to the cloud. DMA as well provides recommendations to help you address those issues.

Connect to the server and select source databases

And finally start assessment. 

As you can see from the picture bellow possible compatibility issues may be:

• Breaking changes,
• Behavior changes,
• Deprecated features

Issue details
Impact and recommendation:
Those data types are checked as deprecated. Deprecated data types are marked to be discontinued on next versions of SQL Server. We should avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

In some cases, using TEXT, IMAGE or NTEXT might harm performance as well.

I will not go too detailed into explaining all possible compatibility issues or feature recommendations DMA can provide us. This DMA post was meant to be just a part of the migration sequence

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

If you would like to read more about different compatibility issues identified under breaking changes, behavior changes, and deprecated features, as well different feature recommendation across performance, storage, and security areas read this Microsoft Article, there you may find all necessary information about DMA usage.

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

Azure SQL Managed Instance / 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 / Database Migration Service (DMS) / Online Data Migration / Migrate your database to Azure SQL Managed Instance
• Azure SQL Managed Instance / Migration / Database Experimentation Assistant (DEA) / Evaluate your workload before you migrate your database to Azure SQL Managed Instance
• PowerShell / SQL Server / Cannot find type Microsoft.SqlServer.Management.Smo.Server
• Auckland SQL User Group Meetup Event / Online Event / Kevin Kline, Microsoft MVP / 5 Critical Considerations When Moving to the Cloud