Introduction
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.
- Part 1: Introduction and requirements (This article)
- Part 2: Preparing for migration
- Part 3: Migration procedure
- Part 4: Additional steps
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:
- We need more control on migration project.
- Least tolerance towards downtime on migration cutover.
- DMS binaries cant be installed on on-premis environment.
- DMS binaries doesn’t have file access to the database backups.
- No access to the host OS is available, or there are no administrator privileges.
- We can’t open network ports from our environment to Azure.
- Network throttling, or proxy blocking issues exist in your environment.
- Backups are stored directly to Azure Blob Storage through the
TO URLoption. - We need to use differential backups.(In this case I haven’t used differential backups)
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 inNORECOVERYmode. Consider manually using LRS to orchestrate migrations when Database Migration Service doesn’t fully support our scenarios.
How does LRS operates
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:
- Place backup files for each database in a separate folder on Azure Blob Storage in a flat-file structure. For example, use separate database folders:
bolbcontainer/database1/files,blobcontainer/database2/files, etc. - Do not use nested folders inside database folders as this structure is not supported. For example, do not use subfolders:
blobcontainer/database1/subfolder/files. - Start LRS separately for each database.
- Specify different URI path to separate database folders on Azure Blob Storage.
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.
Requirements for getting started
SQL Server side
- SQL Server 2008-2019
- Full backup of databases (one or multiple files)
- Differential backup (one or multiple files)
- Log backup (not split for a transaction log file)
CHECKSUMenabled for backups (mandatory)
Azure side
- PowerShell Az.SQL module version 2.16.0 or later (installed or accessed through Azure Cloud Shell)
- Azure CLI version 2.19.0 or later (installed)
- Azure Blob Storage container provisioned
- Shared access signature (SAS) security token with read and list permissions generated for the Blob Storage container
Azure RBAC permissions
Running LRS through the provided clients requires one of the following Azure roles:
- Subscription Owner role
- Managed Instance Contributor role
- Custom role with the following permission:
Microsoft.Sql/managedInstances/databases/*
Best practices
- Run Data Migration Assistant to validate that your databases are ready to be migrated to SQL Managed Instance.
- Split full and differential backups into multiple files, instead of using a single file.
- Enable backup compression.
- Use Cloud Shell to run scripts, because it will always be updated to the latest cmdlets released.
- Plan to complete the migration within 36 hours after you start LRS. This is a grace period that prevents the installation of system-managed software patches.
- Place all backup files for an individual database to a single folder. Do not use subfolders for the same database.
Important
- You can’t use the database that’s being restored through LRS until the migration process finishes.
- LRS doesn’t support read-only access to databases during the migration.
After migration finishes, the migration process is finalized because LRS doesn’t support resuming the restore process.