Avoid problems in your SQL Server Migration with Data Migration Assistant (DMA)
Welcome to a new post on the best SQL Server blog. In today’s post we will talk about how to do an analysis before migrating SQL Server to new version and detect possible incompatibility issues.
Before starting any migration, we must be aware of the minimum requirements that we must take into account when preparing the databases to start the migration process.
Prerequisites to keep in mind:
- Supported operating systems:
Windows Server 2016, Windows 10, Windows Server 2012, Windows 7, Windows 8, Windows 8.1
- Framework version required:
.NET Framework 4.7.2 64-bit or higher.
.NET Core 3.0 64-bit or higher.
Recommended Resolution (1024×756)
Now that we know the necessary prerequisites for migration, it’s time to learn how the Data Migration Assistant or DMA works and how to use it to prepare for a SQL Server migration.
What is Data Migration Assistant?
It is a tool provided by Microsoft to make an assessment that can detect compatibility issues that may affect the functionality of the database when migrating to a new version of SQL Server, also recommends performance improvements that we can apply in our new environment.
Here is the link to download the tool:
Project Creation
Once downloaded, we can start using it by first enabling these simple steps:
- Create a new migration project.
- Check the Assessment option
- Project name, name of our migration project.
- Assessment type select Database Engine.
- Source server type, is the source server we want to migrate to
- Target server type, type of target server to which we want to migrate, it can be SQL Server, azure or a SQL Server in a virtual machine in Azure.
Once the fields are informed we create the project.
In the next window we choose the new SQL Server version we want to migrate to.
These are the versions of SQL server that provides:
Connection to the Origin Server
In the following window we are asked to connect to the source server.
To connect we have two options:
- Authentication with Windows user
- Authentication with SQL Server user
Note: that if we choose authentication by SQL Server user, the user must be sysadmin.
Database Migration
Once connected to the origin server we will be able to see all the databases that we have in our instance.
We select the databases we want to migrate and click on add.
Then we will go to the next window where we can see a summary of the databases we have selected.
Confirm that everything is OK and click on start evaluation, the time it takes to finish depends on the size of the database. Once it finishes it will show us all the incompatibilities it has found.
This report shows three types of incompatibilities:
- Blocking changes: changes that need to be made to avoid problems once migrated.
- Behavior changes: an example of this are the data types in SQL Server 2008 there is the text type but instead in SQL Server 2016 is varchar, what SQL Server 2016 will do is to respect the original data type but if in the future we want to migrate from 2016 to SQL Server 2022 this change will be blocking.
- Deprecated functions: they can be HASH algorithms, e.g. HASHBYTES function.
As we can see, it is a very useful tool to avoid surprises in our new version of SQL Server. I hope it will help you when carrying out checks before making your migration.
If you If you work with Microsoft technology and SQL Server, you are interested in these posts:
Know the Waiting Stadistics or Waits for SQL Server
Discover the main functionalities of SQL Server Always On Cluster
If you need help with the migration of workloads to the cloud or if you want to improve the performance of your SQL Server contact us.
Data Analyst Associate.
