Mar 23, 20223 min read

Migrate SQL Server databases to SQLMI through Log Replay Service (In Preview)

MS official document about LRS : https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/log-replay-service-migrate

Step 1: Backup the source database directly to Blob Storage

Notes:

LRS requires databases on SQL Server to be backed up with the CHECKSUM option enabled.
LRS supports Full, log, and differential backups and it's recommended to split full and differential backups into multiple files for better restoration performance,
Backup files containing % and $ characters in the file name cannot be consumed by LRS.
Backup files for different databases must be placed in separate folders on Blob Storage in a flat-file structure. Nested folders inside individual database folders are not supported.
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.

-- Example of how to make a full database backup to a URL
BACKUP DATABASE [TPCC1000]
TO URL = 'https://mytestsa.blob.core.windows.net/tempsqlbkp/tpcc1000/Full_tpcc1000_20220127_135300.bak'
WITH INIT, COMPRESSION, CHECKSUM
GO
-- Example of how to make a differential database backup to a URL
BACKUP DATABASE [TPCC1000]
TO URL = 'https://mytestsa.blob.core.windows.net/tempsqlbkp/tpcc1000/diff_tpcc1000_backup_2022_02_11_010419.bak'  
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM
GO

-- Example of how to make a transactional log backup to a URL, Need a SQL Agent job to run this repeatedly 
BACKUP LOG [TPCC1000]
TO URL = 'https://mytestsa.blob.core.windows.net/tempsqlbkp/tpcc1000/Log_tpcc1000_2022-02-11:00:50:30.trn'  
WITH COMPRESSION, CHECKSUM

Step 2: Generate a Blob Storage SAS authentication token for LRS

The SAS token that LRS will use must be generated for the entire Azure Blob Storage container, and it must have Read and List permissions only. For example, if you grant Read, List and Write permissions, LRS will not be able to start because of the extra Write permission.

$SAKey = (Get-AzStorageAccount -ResourceGroupName mytestrg -StorageAccountName mytestsa | Get-AzStorageAccountKey | Select-Object Value -First 1).Value

$SAContext = New-AzStorageContext -StorageAccountName mytestsa -StorageAccountKey $SAKey

$StartTime = (Get-Date).AddDays(-1)
$EndTime = $startTime.AddDays(7)

$SAContainerSasToken = Get-AzStorageContainer -Context $SAContext -Name tempsqlbkp | New-AzStorageContainerSASToken -Permission rl -StartTime $StartTime -ExpiryTime $EndTime

# Run commands below in a Bash shell

SAKey=$(az storage account keys list -g mytestrg -n mytestsa --query [0].value -o tsv)

EndTime=`date -u -d "7 days" '+%Y-%m-%dT%H:%MZ'`

SAContainerSasToken=$(az storage container generate-sas --account-key $SAKey --account-name mytestsa --expiry $EndTime --name tempsqlbkp --permissions lr)

Step 3: Start LRS in continuous mode

Start-AzSqlInstanceDatabaseLogReplay -ResourceGroupName "mytestrg" `
	-InstanceName "mytest-sqlmi" `
	-Name "LRS-Tpcc1000" `
	-Collation "SQL_Latin1_General_CP1_CI_AS" `
	-StorageContainerUri "https://mytestsa.blob.core.windows.net/tempsqlbkp/tpcc1000" `
	-StorageContainerSasToken $SAContainerSasToken

StorageUri="https://mytestsa.blob.core.windows.net/tempsqlbkp/tpcc1000"
az sql midb log-replay start -g mytestrg --mi mytest-sqlmi -n LRS-Tpcc1000 --storage-uri $StorageUri  --storage-sas $SAContainerSasToken

Note:

The command above won't return control to the command prompt.
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.
The SAS token that LRS will use must be generated for the entire Azure Blob Storage container, and it must have Read and List permissions only. For example, if you grant Read, List and Write permissions, LRS will not be able to start because of the extra Write permission.
LRS can support up to 100 simultaneous restore processes per single managed instance.
System-managed software patches are blocked for 36 hours once the LRS has been started. After this time window expires, the next software maintenance update will stop LRS. You will need to restart LRS from scratch.

Step 4: Monitor the migration progress

Get-AzSqlInstanceDatabaseLogReplay -ResourceGroupName "mytestrg" `
	-InstanceName "mytest-sqlmi" `
	-Name "LRS-Tpcc1000"

az sql midb log-replay show -g mytestrg --mi mytest-sqlmi -n DMS-Tpcc1000

How to stop the migration (optional)

Use command below to stop the LRS in case you need to interrupt the migration. The command fails if the "DoNotDelete" lock enabled on the target SQLMI

Stop-AzSqlInstanceDatabaseLogReplay -ResourceGroupName "mytestrg" `
	-InstanceName "mytest-sqlmi" `
	-Name "LRS-Tpcc1000" `

az midb log-replay stop -g mytestrg --mi mytest-sqlmi -n LRS-Tpcc1000

Step 5: Complete the migration (cutover)

Stop the application and the workload. Take the last log-tail backup and ensure that all backups have been restored. Initiating the cutover will stop LRS and cause the database to come online for read and write use on SQL Managed Instance.

Complete-AzSqlInstanceDatabaseLogReplay -ResourceGroupName "mytestrg" `
	-InstanceName "mytest-sqlmi" `
	-Name "LRS-Tpcc1000" `
	-LastBackupName "Log_tpcc1000_2022-02-11:17:30:00.trn"

az midb log-replay complete -g mytestrg --mi mytest-sqlmi -n LRS-Tpcc1000 --last-backup-name "Log_tpcc1000_2022-02-11:17:30:00.trn"