Data Migration Assistant

Have you ever been in a situation where migration became an issue for you? I was in a fix whether how one should migrate only the schema part, In such cases one can use the tool known as Data Migration Assistant. The Data Migration Assistant (DMA) helps you upgrade to a modern data platform by detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database. DMA is usually used for one time migration. With the help of DMA one can migrate both schema and data or only schema part or data part.

For migration the source can be any one of these:

1. SQL server
2. AWS RDS for sql server

And the destination can be any of these:

1. Azure SQL database
2. SQL server
3. Azure SQL database managed instance
4. SQL server on virtual machines.

Following are the supported source and target versions:

Source

• SQL Server 2005
• SQL Server 2008
• SQL Server 2008 R2
• SQL Server 2012
• SQL Server 2014
• SQL Server 2016
• SQL Server 2017 on Windows

Targets

• SQL Server 2012
• SQL Server 2014
• SQL Server 2016
• SQL Server 2017 on Windows and Linux
• Azure SQL Database
• Azure SQL Database Managed Instance
• SQL server running on an Azure Virtual Machine

In DMA assessment and migration can be performed. Assessment is carried out in order to find the compatibility issues in a database. The migration blockers are solved for further migration process.

To run an assessment, you have to be a member of the SQL Server sysadmin role.

Migrating SQL Server on-premises to Azure SQL Database by using the Data Migration Assistant.

Create a new migration project

1. On the left pane, select New (+), and then select the Migration project type.
2. Set the source type to SQL Server and the target server type to Azure SQL Database.
3. Select Create.

Specify the source server and database

1. For the source, under Connect to source server, in the Server name text box, enter the name of the source SQL Server instance.
2. Select the Authentication type supported by the source SQL Server instance.

3. Select Connect.

4. Select a single source database to migrate to Azure SQL Database

5. Select next

Specify the target server and database

For the target, under Connect

1. For the target, under Connect to target server, in the Server name text box, enter the name of the Azure SQL Database instance.
2. Select the Authentication type supported by the target Azure SQL Database instance.

3. Select Connect.

4. Select a single target database to which to migrate.

5. Click next

Select schema objects

1. Select the schema objects from the source database that you want to migrate to Azure SQL Database.

2. Select General SQL script. Review the generated script

3. Review the generated script.

Deploy schema

1. Select Deploy schema
2. Review the results of the schema deployment.
3. Select Migrate data to initiate the data migration process
4. Select the tables with the data you want to migrate.

5. Select Start data migration
The final screen shows the overall status.

Interested in Microsoft Azure, Let’s CONNECT!