Azure Data Studio - Can we do online migratation of same db on prem sql to two different target(sql manged insance)

Nitya V 45

Azure Data Studio - Can we do online migratation same db (on prem sql) to two different target(sql manged instance) wiht azure migration extension in azure data studio

I am wondering what does the tool do as part of cutover.What activities are done as part of the cutover

We plan to run this where source is prod and the destination is dev sql mi instancs. We want to ensure it doesnt perform any action on the Prod

Thanks

ShaktiSingh-MSFT 15,841 Reputation points

2024-05-23T05:54:21.2066667+00:00

Hi Nitya V •

Welcome to Microsoft Q&A forum.

As I understand, you have a Migration scenario on Azure Data Studio using extension from On-Premises to Azure SQL MI instances.

I am checking on it and will get back to you.

Thanks

1 answer

ShaktiSingh-MSFT 15,841 Reputation points

2024-05-24T03:46:28.7+00:00

Hi Nitya V •,

Please refer to the below reply from the internal team:

Azure Data Studio extension – Azure SQL Migration is powered by Azure Database Migration Service (DMS). For SQL MI Target, DMS uses backup of the source database, whether they are placed on On-prem local share or on Azure Blob container, copy and restore them to Target SQL MI instance. Hence, DMS does not perform any action on the prod but rather copy from backup location.

In case of Online migration (manual cutover), once customer stops the application / write traffic on source DB, and start the cutover, DMS ensures that all the discovered required backup in backup location (On-prem local share or Azure blob container) are successfully restored and there is no more backup left. Only then it goes for the cutover, complete the recovery of the target database and bring it online.

DMS can be used to migrate same DB to different MI targets, it is possible, but it has to be run as two different migrations.

Thank you.
Please sign in to rate this answer.
Nitya V 45 Reputation points

2024-05-24T12:56:41.4666667+00:00

Thanks @ShaktiSingh-MSFT how about the differential backup. How does DMS help in restoring them

How is it different from offline data migration with dms or traditional migration

ShaktiSingh-MSFT 15,841 Reputation points

2024-05-27T08:31:04.7133333+00:00

Hi Nitya V •,

You can write each backup to either a separate backup file or to multiple backup files. Appending multiple backups such as full and transaction logs into a single backup media isn't supported.

Read here for Offline: https://learn.microsoft.com/en-us/azure/dms/tutorial-sql-server-managed-instance-online-ads

For Azure SQL VM and Azure SQL MI targets, DMS uses physical migration i.e., using backup and restore. As described below, the migration mode chosen determines how the data is kept consistent between source and target.

Offline migration: During offline migration to Azure SQL VM and Azure SQL MI targets, application downtime starts when the migration starts. DMS will restore all the backup files to the Target, as long as the latest backup file/s from source have been transferred to SMB network storage or to Azure blob container (as per the migration configuration). If the backup is taken with the CHECKSUM option, DMS restore operation automatically performs the validation. In the absence of checksum, the integrity of the backup is explicitly checked before restoring. This ensures that the restore file is identical to the backup file and hence have the same data. You can verify all the backup files including the last backup file name from source with the backup file applied/restore on target shown on DMS migration monitoring page and validate their respective checksum.

Online migration: During online migration to Azure SQL VM and Azure SQL MI targets, downtime starts once you initiate the migration cutover along with stopping the application. DMS will restore all the backup files to the Target, as long as the latest backup file/s from source have been transferred to SMB network storage or to Azure blob container (as per the migration configuration). After you press the cutover button, DMS shows the count for pending backup file/s (if any) which are present on the SMB network storage or Azure blob container and yet to be applied/restore on target. If the backup is taken with the CHECKSUM option, DMS restore operation automatically performs the validation. In the absence of checksum, the integrity of the backup is explicitly checked before restoring. This ensures that the restore file is identical to the backup file and hence have the same data. You can verify all the backup files including the last backup file name from source with the backup file applied/restore on target shown on DMS migration monitoring page and validate their respective checksum.

Backup requirements

Backup media Make sure to take every backup on a separate backup media (backup files). Azure Database Migration Service doesn't support backups that are appended to a single backup file. Take full, differential and log backups to separate backup files.

Refer: https://learn.microsoft.com/en-us/azure/dms/known-issues-azure-sql-db-managed-instance-online

For Restore: https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=azuresqldb-mi-current

Thanks

Nitya V 45 Reputation points

2024-06-03T13:37:31.2866667+00:00

Where can I find logs on the steps it took or the Tsql statement it did to perform validation
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure Data Studio - Can we do online migratation of same db on prem sql to two different target(sql manged insance)

1 answer

Your answer