Data Migration - Collation mismatch error during an offline migration

Symptoms

During an offline migration from a SQL Server Express instance to Azure SQL Database using Azure Database Migration Service (DMS), customers may encounter a collation mismatch error. In the Migration Wizard (Step 5), validation fails because the source database collation appears as 'undefined', even though both the source and target databases are configured with the same collation setting. This discrepancy can be confusing, especially when the collation appears correctly in SQL Server Management Studio (SSMS) but shows as 'NULL' when queried via the sys.databases system view.

Migration Wizard at Step 5 in the Azure portal

Troubleshooting Analysis

When validating the migration in Step 5 of the Azure DMS wizard, you may notice that the source database collation appears as 'undefined', even though it displays correctly in SQL Server Management Studio (SSMS). 

Checking the source database property from SSMS

Upon further inspection using the sys.databases system view, the collation_name field for the source database may return NULL.

Query on sys.databases system view

This behavior is typically caused by the Auto Close option being enabled (is_auto_close_on = 1) on the source SQL Server Express database. When Auto Close is enabled, the SQL Server engine shuts down the database when it's not in use, which can result in metadata like collation being unavailable during validation.

Included is_auto_close_on column querying from sys.databases system view

Workaround

To resolve this issue and ensure the source database remains online during migration:

1. Disable Auto Close by running the following T-SQL command:

   ALTER DATABASE [YourDatabaseName] SET AUTO_CLOSE OFF

2. Re-run the query against sys.databases to confirm that the collation_name now appears correctly.
   
3. Return to the Azure DMS wizard and proceed with the migration. The validation step should now succeed without collation-related errors.