During database migration in online mode using Azure data studio status is restoring only on sqlmi.

Question

During database migration in online mode using Azure data studio status is restoring only on sqlmi. it's not changing since long time. Though Azure data studio shows restored and Data migration service shows ready for cutover.

Data is around 24 GB only.

Answer 1

Hi Pratibha Agrawal,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you could not see completion of Migration of Azure SQL MI via ADS and shows stuck.

Ensure, if you had previously created DMS using the Azure Portal, you cannot reuse it in the migration wizard in Azure Data Studio. Only DMS created previously using Azure Data Studio can be reused.

If you're using the Azure Database Migration Service for the first time, ensure that Microsoft.DataMigration resource provider is registered in your subscription. You can follow the steps to register the resource provider

• The Azure SQL Migration extension for Azure Data Studio doesn't take database backups, or neither initiate any database backups on your behalf. Instead, the service uses existing database backup files for the migration.
• If your database backup files are provided in an SMB network share, Create an Azure storage account that allows the DMS service to upload the database backup files. Make sure to create the Azure Storage Account in the same region as the Azure Database Migration Service instance is created.
• Each backup can be written to either a separate backup file or multiple backup files. However, appending multiple backups (that is, full and t-log) into a single backup media isn't supported.
• Use compressed backups to reduce the likelihood of experiencing potential issues associated with migrating large backups.

If your database backups are provided in an on-premises network share, DMS will require you to set up a self-hosted integration runtime in the next step of the wizard. If a self-hosted integration runtime is required to access your source database backups, check the validity of the backup set and upload them to your Azure storage account.
If your database backups are already on an Azure storage blob container, you don't need to set up a self-hosted integration runtime.

To complete the cutover:

1. Stop all incoming transactions to the source database.
2. Make application configuration changes to point to the target database in Azure SQL Managed Instance.
3. Take a final log backup of the source database in the backup location specified.
4. Put the source database in read-only mode. Therefore, users can read data from the database but not modify it.
5. Ensure all database backups have the status Restored in the monitoring details page.
6. Select Complete cutover in the monitoring details page.

During the cutover process, the migration status changes from in progress to completing. When the cutover process is completed, the migration status changes to succeeded to indicate that the database migration is successful and that the migrated database is ready for use.

After the cutover, availability of SQL Managed Instance with Business Critical service tier only can take significantly longer than General Purpose as three secondary replicas have to be seeded for Always On High Availability group. This operation duration depends on the size of data, for more information, see Management operations duration.

Also note below 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.
• DMS doesn't support configuring high availability and disaster recovery on your target to match the source topology.
• The following server objects aren't supported:
• SQL Server Agent jobs
• Credentials
• SSIS packages
• 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.
• If you received the following error: Memory-optimized filegroup must be empty in order to be restored on General Purpose tier of SQL Database Managed Instance. This issue is by design, Hekaton (also known as SQL Server In-Memory OLTP) isn't supported on General Purpose tier of Azure SQL Managed Instance. To continue migration, one way is to upgrade to Business Critical tier, which supports Hekaton. Another way is to make sure the source database isn't using it while the Azure SQL Managed Instance is General Purpose.

Let us know if above pointers are satisfied and helpful.

If not, we will investigate further.

Thanks.