Known issues/migration limitations with online migrations to Azure SQL DB

Known issues and limitations associated with online migrations from SQL Server to Azure SQL Database are described below.

Migration of temporal tables not supported

Symptom

If your source database consists of one or more temporal tables, your database migration fails during the “Full data load” operation and you may see the following message:

{ "resourceId":"/subscriptions//resourceGroups/migrateready/providers/Microsoft.DataMigration/services/", "errorType":"Database migration error", "errorEvents":"["Capture functionalities could not be set. RetCode: SQL_ERROR SqlState: 42000 NativeError: 13570 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]The use of replication is not supported with system-versioned temporal table '[Application. Cities]' Line: 1 Column: -1 "]" }

Temporal table errors example

Workaround

  1. Find the temporal tables in your source schema using the query below. select name,temporal_type,temporal_type_desc,* from sys.tables where temporal_type <>0
  2. Exclude these tables from the Configure migration settings blade, on which you specify tables for migration.

  3. Rerun the migration activity.

Resources

For more information, see the article Temporal Tables.

Migration of tables includes one or more columns with the hierarchyid data type

Symptom

You may see a SQL Exception suggesting “ntext is incompatible with hierarchyid” during the “Full data load” operation:

hierarchyid errors example

Workaround

  1. Find the user tables that include columns with the hierarchyid data type using the query below.

    select object_name(object_id) 'Table name' from sys.columns where system_type_id =240 and object_id in (select object_id from sys.objects where type='U')
    
    1. Exclude these tables from the Configure migration settings blade, on which you specify tables for migration.

    2. Rerun the migration activity.

Migration failures with various integrity violations with active triggers in the schema during “Full data load” or “Incremental data sync”

Workaround

  1. Find the triggers that are currently active in the source database using the query below: select * from sys.triggers where is_disabled =0
  2. Disable the triggers on your source database using the steps provided in the article DISABLE TRIGGER (Transact-SQL).

  3. Re-Run the migration activity.

Support for LOB data types

Symptom

If the length of Large Object (LOB) column is bigger than 32 KB, data might get truncated at the target. You can check the length of LOB column using the query below:

SELECT max(len(ColumnName)) as LEN from TableName

Workaround

If you have an LOB column that is bigger than 32 KB, contact the engineering team at dmsfeedback@microsoft.com.

Issues with timestamp columns

Symptom

DMS doesn't migrate the source timestamp value; instead, DMS generates a new timestamp value in the target table.

Workaround

If you need DMS to migrate the exact timestamp value stored in the source table, contact the engineering team at dmsfeedback@microsoft.com.

Data migration errors do not provide additional details on the Database detailed status blade.

Symptom

When you encounter the migration failures in the Databases details status view, selecting the Data migration errors link on the top ribbon may not provide additional details specific to the migration failures.

data migration errors no details example

Workaround

To get to specific failure details, follow the steps below.

  1. Close the Database detailed status blade to display the Migration activity screen.

    migration activity screen

  2. Select See error details to view specific error messages that help you to troubleshoot migration errors.