This article explains how to configure database migration from SQL 2008 – 2019 to Azure SQL Managed Instance using Log Replay Service (LRS), currently in preview public preview phase. LRS uses SQL Server log-shipping technology.
LRS is opted when we cant use Azure Database Migration Service (DMS) for migration, we can use LRS with PowerShell, Azure CLI cmdlets or API to manually migrate to SQL Managed Instance. I have also included how to automate the migration for multiple databases as well.
We can consider using LRS in the following scenarios:
Note:Microsoft recommend automating the migration of databases from SQL Server to SQL Managed Instance by using Database Migration Service. This service uses the same LRS cloud service at the back end, with log shipping in
NORECOVERYmode. Consider manually using LRS to orchestrate migrations when Database Migration Service doesn’t fully support our scenarios.
The migration consists of making database backups on SQL Server with
CHECKSUM enabled, and copying backup files to Azure Blob Storage. Full, log, and differential backups are supported. LRS cloud service is used to restore backup files from Azure Blob Storage to SQL Managed Instance. Blob Storage is intermediary storage between SQL Server and SQL Managed Instance.
LRS monitors Blob Storage for any new differential or log backups added after the full backup has been restored. LRS then automatically restores these new files. We can use the service to monitor the progress of backup files being restored on SQL Managed Instance, and can stop the process if necessary.
LRS does not require a specific naming convention for backup files. It scans all files placed on Blob Storage and constructs the backup chain from reading the file headers only. Databases are in a “restoring” state during the migration process. Databases are restored in NORECOVERY mode, so they can’t be used for reading or writing until the migration process is completed.
If you’re migrating several databases, you need to:
We can start LRS in either autocomplete or continuous mode. When we start it in autocomplete mode, the migration will complete automatically when the last of the specified backup files has been restored. When we start LRS in continuous mode, the service will continuously restore any new backup files added, and the migration will complete on the manual cutover only.
The recommended way to cutover is to perform a manual cut over after the final log-tail backup has been taken and is shown as restored on SQL Managed Instance. The final cutover step will make the database come online and available for read and write use on SQL Managed Instance.
After LRS is stopped, either automatically through autocomplete, or manually through cutover, we can’t resume the restore process for a database that was brought online on SQL Managed Instance. For example, once migration has been completed we are no longer able to restore additional differential backups for an online database on Managed Instance. To restore more backup files after the migration completes through autocomplete or cutover, need to delete the database and perform the migration again from the scratch.
CHECKSUMenabled for backups (mandatory)
Running LRS through the provided clients requires one of the following Azure roles:
After migration finishes, the migration process is finalized because LRS doesn’t support resuming the restore process.
Disclaimer: The views expressed and the content shared are those of the author and do not reflect the views of the author’s employer or techbeatly platform.