The Log Replay Service (LRS) is a new Azure service that allows you to migrate your databases from SQL Server on-premises, SQL Server on Azure Virtual Machines, Amazon EC2, Amazon RDS for SQL Server, or Google Compute Engine to Azure SQL Managed Instance. LRS is a free cloud service that uses log shipping technology to enable custom migrations of databases from SQL Server 2008 through 2022.
What are the benefits of using LRS?
Azure Database Migration Service (DMS), the Azure SQL migration extension for Azure Data Studio, and LRS all use the same underlying migration technology and APIs, so why should you specifically use LRS? LRS has the benefit of enabling complex custom migrations and supporting migrations from hybrid architectures to SQL Managed Instance. You can use LRS directly with PowerShell, Azure CLI cmdlets, or APIs to manually build and orchestrate database migrations to SQL Managed Instance, giving much greater control over the migration process than the other options provide. LRS currently only supports migration to SQL Managed Instance.
LRS offers several benefits:
– Minimal downtime: You can migrate your databases at your own pace with minimal downtime by using LRS in combination with database cutover. Database cutover is a process that involves switching your applications from using the source database to using the target database after ensuring data consistency. With LRS, you can perform database cutover at any time without waiting for a full backup and restore cycle. Unlike the DMS or Azure SQL migration extension, or native backup/restore, LRS also supports differential backups, which can further reduce the downtime window required during a migration.
– Customise your migration: You can migrate your databases at your own pace and schedule by using LRS. You can also choose which databases you want to migrate and which ones you want to keep on-premises or in the cloud. You can also use LRS for hybrid architectures where you have databases on-premises and on Azure SQL VMs. Using PowerShell enables you to build your own solutions to simultaneously migrate up to 100 databases per Azure SQL Managed Instance.
– Free service: You don’t have to pay anything extra for using LRS as it is included in your SQL Managed Instance subscription. The only cost associated with LRS is the storage account that you need to create for storing the database and transaction log backups.
– Environmental restrictions: LRS is also a good choice if you have restrictions in your environment that prevent the DMS executable or Azure SQL migration extension from being installed, network ports from being opened, or security restrictions preventing access to the host operating system or database backups.
How does LRS work?
LRS allows you to build a custom solution to migrate your databases as shown in the diagram below.
The first step is to take your database backups on the source SQL Server and copy them to an Azure Blob Storage account. The databases should be in the Full recovery model and LRS supports full, log and differential backups, so is ideal for large databases if you already have a weekly full and daily differential backup schedule. For SQL Server versions 2008 to 2016, the backups should be copied manually (using AzCopy or Azure Storage Explorer) to the Azure Blob Storage account, but for SQL Server 2016 and later the backups can be saved directly to the storage account using the BACKUP TO URL feature.
The next step is to start the LRS service. LRS should be started separately for each database and point to the relevant backup folder on the Blob Storage account. The LRS service is started using either PowerShell (Start-AzSqlInstanceDatabaseLogReplay) or the Azure CLI (az sql midb log-replay start) commands. There are 2 modes:
Autocomplete – LRS will restore all the backups up to the specified last backup file (the -LastBackupName must be specified in the start command). All the backup files must be uploaded in advance and no new backup files can be added once the migration has started.
Continuous – LRS will restore all the backups that have been uploaded and then monitors the folder on the Blob Storage account for any new files uploaded. The logs are applied based on the log sequence number (LSN) and continue until LRS is completed or stopped manually.
Continuous mode is recommended when there are active workloads occurring during the migration and data catchup is required. Progress can be monitored during continuous mode using PowerShell (Get-AzSqlInstanceDatabaseLogReplay) or Azure CLI (az sql midb log-replay show) commands.
The final step is to perform the cutover to the Azure SQL Managed Instance. This is done automatically once the last backup file has been restored if LRS was started in autocomplete mode. If LRS was started in continuous mode, there are some extra steps to follow:
- Stop the application workload.
- Take a final tail-log backup and copy this to the Azure Blob Storage account.
- Once this has been restored on the SQL Managed Instance, initiate the complete process using PowerShell (Complete-AzSqlInstanceDatabaseLogReplay) or the Azure CLI (az sql midb log-replay complete).
- Repoint your application connection strings to the databases on the Azure Managed Instance and restart your application workloads.
Best practices
As with any migration to Azure, the Data Migration Assistant (DMA) tool should be run first to validate that the databases are ready to be migrated to Azure SQL Managed Instance, and any migration blockers or breaking changes should be addressed prior to migration.
To speed up backup, transfer, and restore times from on-premises to Azure, full backups should be striped across multiple files and compressed. Enable CHECKSUM on your backups to avoid SQL Managed Instance from having to perform an integrity check, which slows down restores.
Schedule maintenance windows on your managed instances to avoid interrupting database migrations. System maintenance will suspend any LRS migrations and resume after the updates have been applied, which can slow down large database migrations.
Aim to complete the migration process within 30 days. After this time the LRS job will automatically be cancelled.
If multiple databases are being migrated simultaneously, place the backup files for each database in a separate folder inside your storage container. Store all full, differential, and log backup files within the same folder as nesting folders aren’t supported.
Next steps
Azure Log Replay Service is a new way to migrate your databases from SQL Server on-premises or in the cloud to Azure SQL Managed Instance with minimal downtime and custom migration options. It’s based on log shipping technology and it is free of charge as part of your SQL Managed Instance subscription. If you want to learn more about Log Replay Service, check out the resources below.
You can also join me at SQLBits on the 18th of March when I’ll be showing you how to automate the migration of multi-terabyte databases to Azure SQL Managed Instance using LRS and PowerShell.
Take Control of your Azure SQL Managed Instance Migration using the Log Replay Service
https://events.sqlbits.com/2023/agenda
Overview of Log Replay Service – Azure SQL Managed Instance. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/log-replay-service-overview?view=azuresql
Log Replay Service for Azure SQL Managed Instance in public preview. https://azure.microsoft.com/en-us/updates/log-replay-service-for-azure-sql-managed-instance-in-public-preview/
Log replay service for Azure SQL Managed Instance in public preview. https://azure.microsoft.com/en-gb/updates/log-replay-service-for-azure-sql-managed-instance-in-public-preview/
Migrate databases by using Log Replay Service – Azure SQL Managed Instance https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/log-replay-service-migrate?view=azuresql