SQL Server Migration Limitations

Some major limitations due to which partial data loss or structural disparity can happen are listed below.

• The SQL Server endpoint does not support the use of sparse tables.
• Temporal tables are not supported.
• Partition Switching is not supported.
• Column-level encryption is not supported.
• Column descriptions (remarks) are not migrated.
• Replicating data from indexed views isn't supported.
• Collection objects (ex: xml collections) aren’t migrated.
• Fields with the following data types aren’t supported:
• CURSOR
• SQL_VARIANT
• TABLE (user-defined)
• Fields with the following data types are deprecated and are partially supported for backward compatibility reasons. (Suggested to avoid using these data types)
• NTEXT
• IMAGE
• TEXT
• For tables having the below mentioned data types and no primary key, only staging migration can be done, cdc (live-migration) is not possible.
• NTEXT
• IMAGE
• TEXT
• XML
• All MAX data types (like varchar(max), varbinary (max) etc.)
• Presence of schema with the name “cdc” in the source DB will be ignored and not migrated.
• Identities on NUMERIC(p, 0)  columns are only supported for p <= 18.
• If p > 18, the pre-migration validation would fail, and the migration task cannot move further.
• For p <= 18, they are converted to BigInt data types.
• Fields with the following data-types are migrated with different data-type on target:

  Source Field Data Type	Target Field Data Type
  DATETIMEOFFSET	WSTRING

General references

• Limitations for source DB can be found here.
• Limitations for target DB can be found here.
• Troubleshooting issues while migrating SQL Server purely with DMS can be found here.