Known issues, limitations, and troubleshooting

Known issues and troubleshooting steps associated with the Azure SQL Migration extension for Azure Data Studio.

Note

When checking migration details using the Azure Portal, Azure Data Studio or PowerShell / Azure CLI you might see the following error: Operation Id {your operation id} was not found. This can either be because you provided an operationId as part of an api parameter in your get call that does not exist, or the migration details of your migration were deleted as part of a cleanup operation.

Error code: 2007 - CutoverFailedOrCancelled

  • Message: Cutover failed or cancelled for database <DatabaseName>. Error details: The restore plan is broken because firstLsn <First LSN> of log backup <URL of backup in Azure Storage container>' is not <= lastLsn <last LSN> of Full backup <URL of backup in Azure Storage container>'. Restore to point in time.

  • Cause: The error might occur due to the backups being placed incorrectly in the Azure Storage container. If the backups are placed in the network file share, this error could also occur due to network connectivity issues.

  • Recommendation: Ensure the database backups in your Azure Storage container are correct. If you're using network file share, there might be network-related issues and lags that are causing this error. Wait for the process to be completed.

Error code: 2009 - MigrationRestoreFailed

  • Message: Migration for Database 'DatabaseName' failed with error cannot find server certificate with thumbprint.

  • Cause: The source SQL Server instance certificate from a database protected by Transparent Data Encryption (TDE) hasn't been migrated to the target Azure SQL Managed Instance or SQL Server on Azure Virtual Machine before migrating data.

  • Recommendation: Migrate the TDE certificate to the target instance and retry the process. For more information about this article, see Migrate a certificate of a TDE-protected database to Azure SQL Managed Instance and Move a TDE Protected Database to Another SQL Server.

  • Message: Migration for Database <DatabaseName> failed with error 'Non retriable error occurred while restoring backup with index 1 - 3169 The database was backed up on a server running version %ls. That version is incompatible with this server, which is running version %ls. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

  • Cause: Unable to restore a SQL Server backup to an earlier version of SQL Server than the version at which the backup was created.

  • Recommendation: See Issues that affect database restoration between different SQL Server versions for troubleshooting steps.

  • Message: Migration for Database <DatabaseName> failed with error 'The managed instance has reached its storage limit. The storage usage for the managed instance can't exceed 32768 MBs.

  • Cause: The Azure SQL Managed Instance has reached its resource limits.

  • Recommendation: For more information about storage limits, see Overview of Azure SQL Managed Instance resource limits.

  • Message: Migration for Database <DatabaseName> failed with error 'Non retriable error occurred while restoring backup with index 1 - 3634 The operating system returned the error '1450(Insufficient system resources exist to complete the requested service.)

  • Cause: One of the symptoms listed in OS errors 1450 and 665 are reported for database files during DBCC CHECKDB or Database Snapshot Creation can be the cause.

  • Recommendation: See OS errors 1450 and 665 are reported for database files during DBCC CHECKDB or Database Snapshot Creation for troubleshooting steps.

  • Message: The restore plan is broken because firstLsn <First LSN> of log backup <URL of backup in Azure Storage container>' isn't <= lastLsn <last LSN> of Full backup <URL of backup in Azure Storage container>'. Restore to point in time.

  • Cause: The error might occur due to the backups being placed incorrectly in the Azure Storage container. If the backups are placed in the network file share, this error could also occur due to network connectivity issues.

  • Recommendation: Ensure the database backups in your Azure Storage container are correct. If you're using network file share, there might be network related issues and lags that are causing this error. Wait for the process to complete.

  • Message: Migration for Database <Database Name> failed with error 'Non retriable error occurred while restoring backup with index 1 - 3234 Logical file <Name> isn't part of database <Database GUID>. Use RESTORE FILELISTONLY to list the logical file names. RESTORE DATABASE is terminating abnormally.'.

  • Cause: You've specified a logical file name that isn't in the database backup. Another potential cause of this error is an incorrect storage account container name.

  • Recommendation: Run RESTORE FILELISTONLY to check the logical file names in your backup. For more information about RESTORE FILELISTONLY, see RESTORE Statements - FILELISTONLY (Transact-SQL).

  • Message: Migration for Database <Database Name> failed with error 'Azure SQL target resource failed to connect to storage account. Make sure the target SQL VNet is allowed under the Azure Storage firewall rules.'

  • Cause: Azure Storage firewall isn't configured to allow access to Azure SQL target.

  • Recommendation: For more information about Azure Storage firewall setup, see Configure Azure Storage firewalls and virtual networks.

  • Message: Migration for Database <Database Name> failed with error 'There are backups from multiple databases in the container folder. Please make sure the container folder has backups from a single database.

  • Cause: Backups of multiple databases are in the same container folder.

  • Recommendation: If migrating multiple databases to Azure SQL Managed Instance using the same Azure Blob Storage container, you must place backup files for different databases in separate folders inside the container. For more information about LRS, see Migrate databases from SQL Server to SQL Managed Instance by using Log Replay Service (Preview).

    Note

    For more information on general troubleshooting steps for Azure SQL Managed Instance errors, see Known issues with Azure SQL Managed Instance

Error code: 2012 - TestConnectionFailed

  • Message: Failed to test connections using provided Integration Runtime. Error details: 'Remote name could not be resolved.'

  • Cause: The Self-Hosted Integration Runtime can't connect to the service back end. This issue is caused by network settings in the firewall.

  • Recommendation: There's a Domain Name System (DNS) issue. Contact your network team to fix the issue. For more information, see Troubleshoot Self-Hosted Integration Runtime.

  • Message: Failed to test connections using provided Integration Runtime. 'Cannot connect to <File share>. Detail Message: The system could not find the environment option that was entered

  • Cause: The Self-Hosted Integration Runtime can't connect to the network file share where the database backups are placed.

  • Recommendation: Make sure your network file share name is entered correctly.

  • Message: Failed to test connections using provided Integration Runtime. The file name does not conform to the naming rules by the data store. Illegal characters in path.

  • Cause: The Self-Hosted Integration Runtime can't connect to the network file share where the database backups are placed.

  • Recommendation: Make sure your network file share name is entered correctly.

  • Message: Failed to test connections using provided Integration Runtime.

  • Cause: Connection to the Self-Hosted Integration Runtime has failed.

  • Recommendation: See Troubleshoot Self-Hosted Integration Runtime for general troubleshooting steps for Integration Runtime connectivity errors.

Error code: 2014 - IntegrationRuntimeIsNotOnline

Error code: 2030 - AzureSQLManagedInstanceNotReady

  • Message: Azure SQL Managed Instance <Instance Name> isn't ready.

  • Cause: Azure SQL Managed Instance not in ready state.

  • Recommendation: Wait until the Azure SQL Managed Instance has finished deploying and is ready, then retry the process.

Error code: 2033 - SqlDataCopyFailed

  • Message: Migration for Database <Database> failed in state <state>.

  • Cause: ADF pipeline for data movement failed.

  • Recommendation: Check the MigrationStatusDetails page for more detailed error information.

Error code: 2038 - MigrationCompletedDuringCancel

  • Message: Migration cannot be canceled as Migration was completed during the cancel process. Target server: <Target server> Target database: <Target database>.

  • Cause: A cancellation request was received, but the migration was completed successfully before the cancellation was completed.

  • Recommendation: No action required migration succeeded.

Error code: 2039 - MigrationRetryNotAllowed

  • Message: Migration isn't in a retriable state. Migration must be in state WaitForRetry. Current state: <State>, Target server: <Target Server>, Target database: <Target database>.

  • Cause: A retry request was received when the migration wasn't in a state allowing retrying.

  • Recommendation: No action required migration is ongoing or completed.

Error code: 2040 - MigrationTimeoutWaitingForRetry

  • Message: Migration retry timeout limit of 8 hours reached. Target server: <Target Server>, Target database: <Target Database>.

  • Cause: Migration was idle in a failed, but retrievable state for 8 hours and was automatically canceled.

  • Recommendation: No action is required; the migration was canceled.

Error code: 2041 - DataCopyCompletedDuringCancel

  • Message: Data copy finished successfully before canceling completed. Target schema is in bad state. Target server: <Target Server>, Target database: <Target Database>.

  • Cause: Cancel request was received, and the data copy was completed successfully, but the target database schema hasn't been returned to its original state.

  • Recommendation: If desired, the target database can be returned to its original state by running the first query and all of the returned queries, then running the second query and doing the same.

SELECT [ROLLBACK] FROM [dbo].[__migration_status] 
WHERE STEP in (3,4,6);

SELECT [ROLLBACK] FROM [dbo].[__migration_status]  
WHERE STEP in (5,7,8) ORDER BY STEP DESC;

Error code: 2042 - PreCopyStepsCompletedDuringCancel

  • Message: Pre Copy steps finished successfully before canceling completed. Target database Foreign keys and temporal tables have been altered. Schema migration may be required again for future migrations. Target server: <Target Server>, Target database: <Target Database>.

  • Cause: Cancel request was received and the steps to prepare the target database for copy were completed successfully. The target database schema hasn't been returned to its original state.

  • Recommendation: If desired, target database can be returned to its original state by running the following query and all of the returned queries.

SELECT [ROLLBACK] FROM [dbo].[__migration_status]  
WHERE STEP in (3,4,6);

Error code: 2043 - CreateContainerFailed

  • Message: Create container <ContainerName> failed with error Error calling the endpoint '<URL>'. Response status code: 'NA - Unknown'. More details: Exception message: 'NA - Unknown [ClientSideException] Invalid Url:<URL>.

  • Cause: The request failed due to an underlying issue such as network connectivity, a DNS failure, a server certificate validation, or a timeout.

  • Recommendation: For more troubleshooting steps, see Troubleshoot Azure Data Factory and Synapse pipelines.

Error code: 2056 - SqlInfoValidationFailed

  • Message: CollationMismatch: Source database collation <CollationOptionSource> is not the same as the target database <CollationOptionTarget>. Source database: <SourceDatabaseName> Target database: <TargetDatabaseName>.

  • Cause: The source database collation isn't the same as the target database's collation.

  • Recommendation: Make sure to change the target Azure SQL Database collation to the same as the source SQL Server database. Azure SQL Database uses SQL_Latin1_General_CP1_CI_AS collation by default, in case your source SQL Server database uses a different collation you might need to re-create or select a different target database whose collation matches. For more information, see Collation and Unicode support

  • Message: DatabaseSizeMoreThanMax: No tables were found in the target Azure SQL Database. Check if schema migration was completed beforehand.

  • Cause: The selected tables for the migration don't exist in the target Azure SQL Database.

  • Recommendation: Make sure the target database schema was created before starting the migration. For more information on how to deploy the target database schema, see SQL Database Projects extension

  • Message: NoTablesFound: Some of the source tables don't exist in the target database. Missing tables: <TableList>.

  • Cause: The selected tables for the migration don't exist in the target Azure SQL Database.

  • Recommendation: Check if the selected tables exist in the target Azure SQL Database. If this migration is called from a PowerShell script, check if the table list parameter includes the correct table names and is passed into the migration.

  • Message: SqlVersionOutOfRange: Source instance version is lower than 2008, which is not supported to migrate. Source instance: <InstanceName>.

  • Cause: Azure Database Migration Service doesn't support migrating from SQL Server instances lower than 2008.

  • Recommendation: Upgrade your source SQL Server instance to a newer version of SQL Server. For more information, see Upgrade SQL Server

  • Message: TableMappingMismatch: Some of the source tables don't exist in the target database. Missing tables: <TableList>.

  • Cause: The selected tables for the migration don't exist in the target Azure SQL Database.

  • Recommendation: Check if the selected tables exist in the target Azure SQL Database. If this migration is called from a PowerShell script, check if the table list parameter includes the correct table names and is passed into the migration.

Azure SQL Database limitations

Migrating to Azure SQL Database by using the Azure SQL extension for Azure Data Studio has the following limitations:

Azure SQL Database offline migration utilizes Azure Data Factory (ADF) pipelines for data movement and thus abides by ADF limitations. A corresponding ADF is created when a database migration service is also created. Thus factory limits apply per service.

  • The machine where the SHIR is installed acts as the compute for migration. Make sure this machine can handle the cpu and memory load of the data copy. To learn more, review SHIR recommendations.
  • 100,000 table per database limit.
  • 10,000 concurrent database migrations per service.
  • Migration speed heavily depends on the target Azure SQL Database SKU and the self-hosted Integration Runtime host.
  • Azure SQL Database migration scales poorly with table numbers due to ADF overhead in starting activities. If a database has thousands of tables, there will be a couple of seconds of startup time for each, even if they're composed of one row with 1 bit of data.
  • Azure SQL Database table names with double-byte characters currently aren't supported for migration. Mitigation is to rename tables before migration; they can be changed back to their original names after successful migration.
  • Tables with large blob columns may fail to migrate due to timeout.
  • Database names with SQL Server reserved are currently not supported.
  • Database names that include semicolons are currently not supported.
  • Computed columns do not get migrated.

Azure SQL Managed Instance limitations

Migrating to Azure SQL Managed Instance by using the Azure SQL extension for Azure Data Studio has the following limitations:

  • If migrating a single database, the database backups must be placed in a flat-file structure inside a database folder (including the container root folder), and the folders can't be nested, as it's not supported.
  • If migrating multiple databases using the same Azure Blob Storage container, you must place backup files for different databases in separate folders inside the container.
  • Overwriting existing databases using DMS in your target Azure SQL Managed Instance isn't supported.
  • Configuring high availability and disaster recovery on your target to match source topology isn't supported by DMS.
  • The following server objects aren't supported:
    • Logins
    • SQL Server Agent jobs
    • Credentials
    • SSIS packages
    • Server roles
    • Server audit
  • You can't use an existing self-hosted integration runtime created from Azure Data Factory for database migrations with DMS. Initially, the self-hosted integration runtime should be created using the Azure SQL migration extension in Azure Data Studio and can be reused for further database migrations.
  • A single LRS job (created by DMS) can run for a maximum of 30 days. When this period expires, the job is automatically canceled thus your target database gets automatically deleted.

SQL Server on Azure VMs limitations

Migrating to SQL Server on Azure VMs by using the Azure SQL extension for Azure Data Studio has the following limitations:

  • If migrating a single database, the database backups must be placed in a flat-file structure inside a database folder (including container root folder), and the folders can't be nested, as it's not supported.

  • If migrating multiple databases using the same Azure Blob Storage container, you must place backup files for different databases in separate folders inside the container.

  • Overwriting existing databases using DMS in your target SQL Server on Azure Virtual Machine isn't supported.

  • Configuring high availability and disaster recovery on your target to match source topology isn't supported by DMS.

  • The following server objects aren't supported:

    • Logins
    • SQL Server Agent jobs
    • Credentials
    • SSIS packages
    • Server roles
    • Server audit
  • You can't use an existing self-hosted integration runtime created from Azure Data Factory for database migrations with DMS. Initially, the self-hosted integration runtime should be created using the Azure SQL migration extension in Azure Data Studio and can be reused for further database migrations.

  • VM with SQL Server 2008 and below as target versions aren't supported when migrating to SQL Server on Azure Virtual Machines.

  • If you're using a VM with SQL Server 2012 or SQL Server 2014, you need to store your source database backup files on an Azure Storage Blob Container instead of using the network share option. Store the backup files as page blobs since block blobs are only supported in SQL 2016 and after.

  • You must make sure the SQL IaaS Agent Extension in the target Azure Virtual Machine is in Full mode instead of Lightweight mode.

  • SQL IaaS Agent Extension only supports management of Default Server Server Instance or Single Named Instance,

  • There is a temporary limit of 80 databases per target Azure Virtual Machine. A workaround to break the limit (reset the counter) is to Uninstall and Reinstall SQL IaaS Agent Extension in the target Azure Virtual Machine.

  • Apart from configuring the Networking/Firewall of your Storage Account to allow your VM to access backup files, you also need to configure the Networking/Firewall of your VM to allow outbound connection to your storage account.

Next steps