संपादन करना

के माध्यम से साझा करें

Troubleshoot issues when migrating to Azure SQL Managed Instance

This article helps you troubleshoot common issues you might encounter when migrating SQL Server databases to Azure SQL Managed Instance by using SQL Server migration in Azure Arc.

Note

You can provide feedback about your migration experience directly to the product group.

Arc agent version

When you use SQL Server migration in Azure Arc, certain features require a minimum version of the Arc agent. The Arc agent is an executable that runs alongside your SQL Server instance to provide connectivity to Azure. Always keep your Arc agent version up to date to get the latest fixes and updates.

With automatic updates enabled, the Arc agent stays up to date automatically. However, when a new version of the Arc agent rolls out, it can take a few days for the update to reach all servers. You can speed up the process by manually executing an on-demand Arc agent update through either the Azure portal or command line interfaces.

If you see the following error when accessing the Database migration pane in the Azure portal, you need to upgrade your Arc agent to a supported version:

To enable migration and monitoring capabilities, 
please update your Azure Arc agent extension "WindowsAgentSQLServer" to the latest version.

Arc agent issues

If you encounter issues with the Arc agent, such as an unhealthy extension state or a disconnected SQL Server instance, use the following extension troubleshooting guide: Troubleshoot Azure extension for SQL Server.

Migration readiness assessment issues

The system runs migration readiness assessments every Sunday at 11 PM (23:00) local to the server. Assessments for SQL Server instances newly enabled by Azure Arc can take several days to appear in the Azure portal. Use Run assessment to trigger an on-demand assessment. The assessment appears after a few minutes.

If the database migration readiness assessments page is blank in the Azure portal, the scheduled assessment likely didn't run, or there was a problem running the assessment on the SQL Server instance. Disabling the Arc agent prevents assessments from running. Ensure the Arc agent is enabled. For more information, see Change assessment settings.

Consider the following known issue:

  • When xp_cmdshell is enabled and used, the assessment records a warning for SQL Managed Instance because you can still migrate the database. However, it disrupts the functionality of the object that specifically uses xp_cmdshell. Use the remediation guidance provided in the assessment to mitigate the issue.

Contact Microsoft Support if you run into any of the following issues:

  • The assessment reports don't appear in the portal even after the scheduled time.
  • Performance data availability doesn't increase after one week of gathering data.

View Azure activity log for migration issues

The activity log in the Azure portal, when accessed from a resource, provides insight into resource-level events that occur in Azure. This insight includes information about when you modify or delete resources, as well as details about service health and other important events.

When troubleshooting migration issues to Azure SQL Managed Instance by using SQL Server migration in Azure Arc, the Activity log is a valuable resource to identify problems and understand the sequence of events that led up to an issue.

To access the activity log in the Azure portal for your SQL Server instance enabled by Azure Arc resource, follow these steps:

  1. Go to your SQL Server instance enabled by Azure Arc resource in the Azure portal.

  2. Select Activity log from the resource menu:

    Screenshot of the activity log highlighted for a SQL Server instance resource in the Azure portal.

You can also access the subscription-level activity log for a broader view of events across all resources in your subscription by selecting the notification bell icon of the top navigation bar and then selecting More events in the activity log:

Screenshot of the notification bell icon highlighted in the Azure portal.

Select an event from the activity log to open a pane of event details. Use the Summary and JSON tabs to view detailed information about the event, including particular error messages. If you create a support request, communicate this information with as much detail as possible.

This section describes some of the common issues with the Managed Instance link feature when migrating to Azure SQL Managed Instance through SQL Server migration in Azure Arc:

Incorrect service pack installed

Make sure you have the appropriate SQL Server service pack (SP) or cumulative update (CU) installed.

You can check for the correct version by running the following T-SQL command on your SQL Server instance:

EXEC sp_certificate_add_issuer @CERTID, N'*.database.windows.net'

If you get the error that SQL Server can't find the stored procedure sp_certificate_add_issuer, you likely don't have the proper servicing pack installed (such as the Azure Connect Feature pack). Install the necessary servicing updates and try again.

Always On availability group feature disabled

Ensure that the Always On availability group feature is enabled on your SQL Server instance. The Managed Instance link requires the Always On availability group feature to be enabled for proper functionality.

Using SQL Server 2016

For SQL Server 2016 (13.x), you must complete the extra steps documented in Prepare SQL Server 2016 prerequisites for the link. These extra steps aren't required for SQL Server 2017 (14.x) and later versions supported by the link.

Network connectivity issues

Successful connectivity between your SQL Server environment and Azure SQL Managed Instance is essential for the Managed Instance link feature to work. If you're having networking connectivity issues, consider the following points:

  • The Managed Instance link doesn't work over a public network so the connection between your SQL Server instance and Azure SQL Managed Instance must be private, such as by using a VPN.
  • You can test connectivity directly from the Azure portal as part of the migration process. If the connection test succeeds in the portal but the link can't be created, check the Activity log for details of the failure. You can also test connectivity manually by using Transact-SQL and the SQL Server Agent.
  • Check for any corporate firewalls on your network. Although network connectivity can appear to work, it's possible for firewalls to block specific type of packets that SQL Server uses for distributed availability groups. Verify that firewalls aren't blocking or filtering packet types.

The following warnings can appear when starting the Managed Instance link migration job. You can proceed with the migration despite these warnings:

  • Warning: Database Mirroring Endpoint does not exist
  • Warning: Database Mirroring Endpoint is not secure with a certificate
  • Warning: Database Mirroring Endpoint is not enabled
  • Warning: Database Mirroring Endpoint encryption algorithm is not set to AES

These warnings are currently a known issue, and the migration process addresses them automatically so you can proceed with the migration.

Investigate other warnings. Some warnings might require resolution on your part before you can start the migration, while some can be addressed after the migration completes.

Configuring a link through the Azure portal for migration isn't compatible with existing links that you create manually, either through SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL). If a link already exists, you can't create a new link through the Azure portal.

If a link already exists on your either the SQL Server source or Azure SQL Managed Instance target, you need to perform the following steps before creating a new link between that source and target through the Azure portal:

  1. Drop the link manually from SQL Managed Instance by using Remove-AzSqlInstanceLink or az sql mi link delete from Azure Cloud Shell or a machine signed in with an Azure Context.
  2. Drop the link manually from SQL Server by using DROP AVAILABILITY GROUP with the name of the distributed availability group associated with the link.
  3. Drop all link-related certificates from the SQL Server instance by using DROP CERTIFICATE. The certificates that you need to drop typically contain the following values: DigiKey PKI, Microsoft PKI, endpoint, and database.windows.net. You can use SELECT * FROM sys.certificates to list all certificates on SQL Server.
  4. Drop all link-related certificates from SQL Managed Instance by using Remove-AzSqlInstanceServerTrustCertificate or az sql mi partner-cert delete from Azure Cloud Shell or a machine signed in with an Azure Context. You can use Get-AzSqlInstanceServerTrustCertificate or az sql mi partner-cert show to list the existing authentication certificates on SQL Managed Instance.
  5. The previous steps clear all link-related authentication certificates generated for a link created manually. If you're not using an existing availability group locally, consider dropping the existing database mirroring endpoint by using DROP ENDPOINT. You can use SELECT * FROM sys.endpoints to list all existing endpoints on SQL Server. You must drop the certificate associated with the endpoint before you can drop the endpoint.

Detailed troubleshooting with XE Profiler

For detailed troubleshooting of link issues, use XE Profiler.

Log Replay Service migration issues

This section lists common issues you might encounter when migrating by using Log Replay Service (LRS):

Unable to list directories in Azure Blob Storage

If you see the error message that you're Unable to list directories when selecting a directory on the New Data Migration page in the Azure portal, then the user currently logged into the portal doesn't have the Storage Blob Data Reader role assigned to the storage account. Grant user access to the storage account to resolve the issue.

No results were found in directory

If you see the message that No results were found in directory when selecting a directory on the New Data Migration page in the Azure portal, there's no database backup available within the Azure Blob storage container. To resolve this issue, upload a full database backup to Azure Blob storage.

Check file restoration status

To check how many files were detected, queued, skipped, or were unrestorable during the migration, use the Monitor and cutover page in the Azure portal. After a migration starts, go to the Monitor and cutover page, and then select the database you're migrating to open migration details for that database.

Error 2009 - Managed identity is not set up properly

If your migration starts successfully but then fails with the error 2009 - managed identity is not set up properly, the primary identity for the managed instance doesn't have the Storage Blob data Reader permission assigned to the Azure Blob storage account. Grant managed identity access to the storage account to resolve the issue. Verify that the appropriate identity (either the default managed identity or a user-defined custom identity) has the required permissions, or that the identity didn't change since permissions were granted originally. If the identity changed, grant appropriate permissions to the new identity to resolve the issue.

Troubleshoot migration with DMS

Starting an LRS migration job in Azure Arc automatically creates a Database Migration Service (DMS) migration job so you can use Azure Database Migration Service in the Azure portal to see additional details about the migration job.

To view LRS migration job details in DMS, follow these steps:

  1. Go to Azure Database Migration Service in the Azure portal.

  2. Select All resources and then select the DMS migration job associated with your LRS migration to open the migration details page:

    Screenshot of the DMS all resources page in the Azure portal.

  3. On the migration details page, select Monitor migrations to see the status of the databases migrated for a particular instance using LRS. Select Successful, Canceled, or Error to see more details on the status.

    Another way to access this page is through the Azure Database Migration Service resource added to the resource group that contains the target SQL Managed Instance after a migration is started through Azure Arc.

Delete migration jobs

LRS migration jobs stay on the Monitor and cutover page for 28 days after they finish (either successfully or failed). You can manually delete the jobs if you want to clear them from the monitoring page sooner.

To manually delete the jobs, go to the DMS migration job associated with your LRS migration as described in the previous section. Select the migration job you want to delete, then use the Delete trash can button to delete the job. Confirm by selecting "Check this box to confirm deleting". This action clears the jobs from the Monitor and cutover page in Azure Arc.

Contact Microsoft

You can contact Microsoft to open a support ticket with an issue you're having or to provide feedback directly to the product group.

Contact support

Use https://aka.ms/azure-support to go to the Help + support page in the Azure portal, and then follow these steps to open a migration-related support ticket:

  1. Select Create a support request to open the Support + troubleshooting pane.
  2. Type migration into the text field, then select None of the above under Which service are you having an issue with?
  3. From the Select a service dropdown list, select SQL Server enabled by Azure Arc and then use Next to proceed.
  4. Select your subscription from the dropdown list.
  5. Select your SQL Server instance enabled by Azure Arc resource from the Resource dropdown list and then select Next.
  6. Select Migration Issues in the Are you having one of the following issues? tile and then select Next.
  7. Select Create a support request from the top navigation bar within the Support + troubleshooting pane to open the support ticket form.
  8. Use the following Problem subtype values to route your issue to the the appropriate support queue:
  9. Use Next to proceed through the remaining steps of the support request form, then select Create to submit your support request.

Provide feedback to the product group

You can provide feedback to the product group to help improve the migration experience. Use the following link to submit your feedback:

Related content

  • Last updated on