This article is contributed. See the original author and article here.

This article explains how to manually configure database migration from SQL Server 2008-2019 to SQL Managed Instance using Log Replay Service (LRS). This is a cloud service enabled for managed instance based on the SQL Server log shipping technology in no recovery mode. LRS should be used in cases when Azure Data Migration Service (DMS) cannot be used, when more control is needed or when there exists little tolerance for downtime.


 


When to use Log Replay Service


 


In cases that Azure DMS cannot be used for migration, LRS cloud service can be used directly with PowerShell, CLI cmdlets, or API, to manually build and orchestrate database migrations to SQL managed instance.


 



  • You might want to consider using LRS cloud service in some of the following cases:

  • More control is needed for your database migration project

  • There exists a little tolerance for downtime on migration cutover

  • DMS executable cannot be installed in your environment

  • DMS executable does not have file access to database backups

  • No access to host OS is available, or no Administrator privileges








Note: Recommended automated way to migrate databases from SQL Server to SQL Managed Instance is using Azure DMS. This service is using the same LRS cloud service at the back end with log shipping in no-recovery mode. You should consider manually using LRS to orchestrate migrations in cases when Azure DMS does not fully support your scenarios.


How does it work



Building a custom solution using LRS to migrate a database to the cloud requires several orchestration steps shown in the diagram and outlined in the table below.


 


The migration entails making full database backups on SQL Server and copying backup files to Azure Blob storage. LRS is used to restore backup files from Azure Blob storage to SQL managed instance. Azure Blob storage is used as an intermediary storage between SQL Server and SQL Managed Instance.


 


LRS will monitor Azure Blob storage for any new differential, or log backups added after the full backup has been restored, and will automatically restore any new files added. The progress of backup files being restored on SQL managed instance can be monitored using the service, and the process can also be aborted if necessary. Databases being restored during the migration process will be in a restoring mode and cannot be used to read or write until the process has been completed.


 


LRS can be started in autocomplete, or continuous mode. When started in autocomplete mode, the migration will complete automatically when the last backup file specified has been restored. When started in continuous mode, the service will continuously restore any new backup files added, and the migration will complete on the manual cutover only. The final cutover step will make databases available for read and write use on SQL Managed Instance.


 


log-replay-service-conceptual.png


 





























Operation Details
1. Copy database backups from SQL Server to Azure Blob storage. – Copy full, differential, and log backups from SQL Server to Azure Blob storage using Azcopy or Azure Storage Explorer.
– In migrating several databases, a separate folder is required for each database.
2. Start the LRS service in the cloud.

– Service can be started with a choice of cmdlets:
PowerShell start-azsqlinstancedatabaselogreplay
CLI az_sql_midb_log_replay_start cmdlets.


– Once started, the service will take backups from the Azure Blob storage and start restoring them on SQL Managed Instance.
– Once all initially uploaded backups are restored, the service will watch for any new files uploaded to the folder and will continuously apply logs based on the LSN chain, until the service is stopped.


2.1. Monitor the operation progress. – Progress of the restore operation can be monitored with a choice of or cmdlets:
PowerShell get-azsqlinstancedatabaselogreplay
CLI az_sql_midb_log_replay_show cmdlets.
2.2. Stopabort the operation if needed.

– In case that migration process needs to be aborted, the operation can be stopped with a choice of cmdlets:
PowerShell stop-azsqlinstancedatabaselogreplay
CLI az_sql_midb_log_replay_stop cmdlets.


– This will result in deletion of the being database restored on SQL Managed Instance.
– Once stopped, LRS cannot be continued for a database. Migration process needs to be restarted from scratch.


3. Cutover to the cloud when ready.

– Once all backups have been restored to SQL Managed Instance, complete the cutover by initiating LRS complete operation with a choice of API call, or cmdlets:
PowerShell complete-azsqlinstancedatabaselogreplay
CLI az_sql_midb_log_replay_complete cmdlets.


– This will cause LRS service to be stopped and database on Managed Instance will be recovered.
– Repoint the application connection string from SQL Server to SQL Managed Instance.
– On operation completion database is available for R/W operations in the cloud.



 


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 transaction log file)

  • CHECKSUM must be enabled as mandatory


Azure side



  • PowerShell Az.SQL module version 2.16.0, or above (install, or use Azure Cloud Shell)

  • CLI version 2.19.0, or above (install)

  • Azure Blob Storage provisioned

  • SAS security token with Read and List only permissions generated for the blob storage


Best practices


The following are highly recommended as best practices:


 



  • Run Data Migration Assistant to validate your databases will have no issues being migrated to SQL Managed Instance.

  • Split full and differential backups into multiple files, instead of a single file.

  • Enable backup compression.

  • Use Cloud Shell to execute scripts as it will always be updated to the latest cmdlets released.

  • Plan to complete the migration within 47 hours since LRS service has been started.









Important: Database being restored using LRS cannot be used until the migration process has been completed. This is because underlying technology is log shipping in no recovery mode. Standby mode for log shipping is not supported by LRS due to the version differences between SQL Managed Instance and latest in-market SQL Server version.



 


Steps to execute



Copy backups from SQL Server to Azure Blob storage


 


The following two approaches can be utilized to copy backups to the blob storage in migrating databases to Managed Instance using LRS:


 



Create Azure Blob container and SAS authentication token


 


Azure Blob storage is used as an intermediary storage for backup files between SQL Server and SQL Managed Instance. Follow these steps to create Azure Blob storage container:


 



  1. Create a storage account

  2. Crete a blob container inside the storage account


 


Once a blob container has been created, generate SAS authentication token with Read and List permissions only following these steps:


 



  1. Access storage account using Azure portal

  2. Navigate to Storage Explorer

  3. Expand Blob Containers

  4. Right click on the blob container

  5. Select Get Shared Access Signature

  6. Select the token expiry timeframe. Ensure the token is valid for duration of your migration.

  7. Ensure Read and List only permissions are selected

  8. Click create

  9. Copy-paste the token starting with “?sv=” in the URI









Important: Permissions for the SAS token for Azure Blob storage need to be Read and List only. In case of any other permissions granted for the SAS authentication token, starting LRS service will fail. This security requirements is by design.




Log in to Azure and select subscription



Use the following PowerShell cmdlet to log in to Azure:












PowerShell



Login-AzAccount



 


Select the appropriate subscription where your SQL Managed Instance resides using the following PowerShell cmdlet:












PowerShell



Select-AzSubscription -SubscriptionId <subscription ID>



 


Start the migration



The migration is started by starting the LRS service. The service can be started in autocomplete, or continuous mode. When started in autocomplete mode, the migration will complete automatically when the last backup file specified has been restored. This option requires the start command to specify the filename of the last backup file. When LRS is started in continuous mode, the service will continuously restore any new backup files added, and the migration will complete on the manual cutover only.


 


Start LRS in autocomplete mode


 


To start LRS service in autocomplete mode, use the following PowerShell, or CLI commands. Specify the last backup file name with -LastBackupName parameter. Upon restoring the last backup file name specified, the service will automatically initiate a cutover.


 


Start LRS in autocomplete mode – PowerShell example:












PowerShell



Start-AzSqlInstanceDatabaseLogReplay -ResourceGroupName “ResourceGroup01” `
-InstanceName “ManagedInstance01” `
-Name “ManagedDatabaseName” `
-Collation “SQL_Latin1_General_CP1_CI_AS” `


-StorageContainerUri “https://test.blob.core.windows.net/testing” `
-StorageContainerSasToken “sv=2019-02-02&ss=b&srt=sco&sp=rl&se=2023-12-02T00:09:14Z&st=2019-11-25T16:09:14Z&spr=https&sig=92kAe4QYmXaht%2Fgjocqwerqwer41s%3D” `
-AutoComplete `


-LastBackupName “last_backup.bak”



 


Start LRS in autocomplete mode – CLI example:












CLI



az sql midb log-replay start -g mygroup –mi myinstance -n mymanageddb -a –last-bn “backup.bak”
–storage-uri “https://test.blob.core.windows.net/testing”
–storage-sas “sv=2019-02-02&ss=b&srt=sco&sp=rl&se=2023-12-02T00:09:14Z&st=2019-11-25T16:09:14Z&spr=https&sig=92kAe4QYmXaht%2Fgjocqwerqwer41s%3D”



 


Start LRS in continuous mode


 


Start LRS in continuous mode – PowerShell example:












PowerShell



Start-AzSqlInstanceDatabaseLogReplay -ResourceGroupName “ResourceGroup01” `
-InstanceName “ManagedInstance01” `
-Name “ManagedDatabaseName” `
-Collation “SQL_Latin1_General_CP1_CI_AS” `


-StorageContainerUri “https://test.blob.core.windows.net/testing” `
-StorageContainerSasToken “sv=2019-02-02&ss=b&srt=sco&sp=rl&se=2023-12-02T00:09:14Z&st=2019-11-25T16:09:14Z&spr=https&sig=92kAe4QYmXaht%2Fgjocqwerqwer41s%3D”



 


Start LRS in continuous mode – CLI example:


 












CLI



az sql midb log-replay start -g mygroup –mi myinstance -n mymanageddb
–storage-uri “https://test.blob.core.windows.net/testing”
–storage-sas “sv=2019-02-02&ss=b&srt=sco&sp=rl&se=2023-12-02T00:09:14Z&st=2019-11-25T16:09:14Z&spr=https&sig=92kAe4QYmXaht%2Fgjocqwerqwer41s%3D”



 









Important: Once LRS has been started, any system managed software patches will be halted for the next 47 hours. Upon passing of this window, the next automated software patch will automatically stop the ongoing LRS. In such case, migration cannot be resumed and it needs to be restarted from scratch.



 


Monitor the migration progress


 


To monitor the migration operation progress, use the following PowerShell command:












PowerShell



Get-AzSqlInstanceDatabaseLogReplay -ResourceGroupName “ResourceGroup01” `
-InstanceName “ManagedInstance01” `
-Name “ManagedDatabaseName”



 


To monitor the migration operation progress, use the following CLI command:












CLI



az sql midb log-replay show -g mygroup –mi myinstance -n mymanageddb




Stop the migration


In case you need to stop the migration, use the following cmdlets. Stopping the migration will delete the restoring database on SQL managed instance due to which it will not be possible to resume the migration.


 


To stopabort the migration process, use the following PowerShell command:












PowerShell



Stop-AzSqlInstanceDatabaseLogReplay -ResourceGroupName “ResourceGroup01” `
-InstanceName “ManagedInstance01” `
-Name “ManagedDatabaseName”



 


To stopabort the migration process, use the following CLI command:


 












CLI



az sql midb log-replay stop -g mygroup –mi myinstance -n mymanageddb



 


Complete the migration (continuous mode)


 


In case LRS is started in continuous mode, once you have ensured that all backups have been restored, initiating the cutover will complete the migration. Upon cutover completion, database will be migrated and ready for read and write access.


 


To complete the migration process in LRS continuous mode, use the following PowerShell command:


 












PowerShell



Complete-AzSqlInstanceDatabaseLogReplay -ResourceGroupName “ResourceGroup01” `
-InstanceName “ManagedInstance01” `
-Name “ManagedDatabaseName” `


-LastBackupName “last_backup.bak”



 


To complete the migration process in LRS continuous mode, use the following CLI command:


 












CLI



az sql midb log-replay complete -g mygroup –mi myinstance -n mymanageddb –last-backup-name “backup.bak”



 


Disclaimer


 


Please note that products and options presented in this article are subject to change. This article reflects the user-initiated manual failover option available for Azure SQL Managed Instance in February, 2021.


 


Closing remarks


 


If you find this article useful, please like it on this page and share through social media.


 


To share this article, you can use the Share button below, or this short link: https://aka.ms/mi-logshipping.

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.