How to use Log Replay Service to migrate your large on-prem databases to Azure SQL Managed Instance.

 

Scenario: Customer has a larger ( > 3TB ) database and it's continuously operating, however for better manageability/performance/security and governance perspective they want to migrate to SQL MI.

Challenge :
1. How to transfer larger backups file ( typically customers are using Azure Databox to store the large backup files and ship them to Microsoft data centers) or used ExpressRoute connectivity to upload files. however, both options will take time.

2. During the backup files transfer, the operational database keeps growing ( app+db continuously functioning) and generating new (a few hundred MB or GB ) records. How to sync those to SQL MI


Solution : Use Log Replay Service (LRS) to migrate your large on-prem databases to Azure SQL Managed Instance. It can be operated in two mode
1.LRS in autocomplete mode
When you use autocomplete mode, the migration finishes automatically when the last of the specified backup files have been restored. This option requires the entire backup chain to be available in advance and uploaded to your Blob Storage account. It doesn't allow adding new backup files while migration is in progress
2.LRS in continuous mode ( for missition critical database migration)
When you use continuous mode, the service continuously scans the Azure Blob Storage folder and restores any new backup files that get added while migration is in progress. You need to use continuous mode migration when you don't have the entire backup chain in advance, and when you plan to add new backup files( transaction log file backups to capture last channges) after the migration is in progress. We recommend this mode for active workloads for which data catch-up is required.

Prerequisites:
Azure Blob storage account to store the backup files, SAS key, or Managed Identity credentials to connect on-prem SQL and Azure SQL MI.
Back up your databases directly to the storage account using BACKUP TO URL or BACKUP TO DISK commands. For this step, you need to create credentials.

Steps :
Open the Azure portal and start the Cloud PowerShell, then set the AzSubscription and Context

>Select-AzSubscription -SubscriptionId <your subscription id>
>set-AzContext -Subscription <your subscription id> -Tenant <your tenant id>

----------- LRS in autocomplete mode

Start-AzSqlInstanceDatabaseLogReplay -ResourceGroupName "test-sqlinstances" `
-InstanceName "test-mi-telus" `
-Name "MIDBautocomplete" `
-Collation "SQL_Latin1_General_CP1_CI_AS" `
-StorageContainerUri "https://generaladls2.blob.core.windows.net/db-backups/sample-sales-db-2019" `
-StorageContainerSasToken "sv=2021-06-08&ss=bfqt&srt=sco&sp=rwdlacupyx&se=2023-02-17T02:47:24Z&st=2023-02-16T18:47:24Z&spr=https&sig=ksSEVoMTsuzVxZ%2FUKIbAR296pm6yzGVWXhm86xYQLcY%3D" `
-AutoCompleteRestore `
-LastBackupName "sample_sales_db_2019_full.bak"

NOTE: Only one .bak file should be in the folder

----------- Start LRS in continuous mode

Start-AzSqlInstanceDatabaseLogReplay -ResourceGroupName "test-sqlinstances" `
-InstanceName "test-mi-telus" `
-Name "MIDBcontinuous" `
-Collation "SQL_Latin1_General_CP1_CI_AS" `
-StorageContainerUri "https://generaladls2.blob.core.windows.net/db-backups/sample-sales-db-2019" `
-StorageContainerSasToken "sv=2021-06-08&ss=bfqt&srt=sco&sp=rwdlacupyx&se=2023-02-17T02:47:24Z&st=2023-02-16T18:47:24Z&spr=https&sig=ksSEVoMTsuzVxZ%2FUKIbAR296pm6yzGVWXhm86xYQLcY%3D"

NOTE: The storage folder can contain full backup/ differntial back or log file backps. also you can take trasaction log file backps from your on-prem database and put in the same folder while LRS continuous command is executing. it will pickup all new files and restore to the new SQL MI.

----------- Monitor the progress
Get-AzSqlInstanceDatabaseLogReplay -ResourceGroupName "test-sqlinstances" `
-InstanceName "test-mi-telus" `
-Name "MItestContinu"


----------- Complete the restore process once the cut over defined
Once you decide the cutover of on-prem transaction then generate the last log file backup and put in to the Azure blob store folder. then execute below command to complete the restore process.

Complete-AzSqlInstanceDatabaseLogReplay -ResourceGroupName "test-sqlinstances" `
-InstanceName "test-mi-telus" `
-Name "MItestContinu" `
-LastBackupName "sample_sales_db_2019_full_log.trn"

 

Follow the complete guide https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/log-replay-service-migrate?view=azuresql&tabs=sas-token