Recently, I’ve started on a project where we are migrating a customer to Azure SQL Managed Instance, which now supports a few different migration paths. You can simply backup and restore from a database backup, but you can’t apply a log or differential backup to that database. You can also use the Database Migration Service, but that requires a lot of infrastructure and Azure configuration. The log replay service, or LRS, is the functional equivalent of log shipping to your new managed instance database. While log shipping is a well known methodology for both database migrations or disaster recovery. However, the implementation is a little different–let’s talk about how it works.
First, You Must backup to URL
In order to use the log replay service, you need to take a backup of your database to Azure Blob Storage. Alternatively, you can push on-premises backups using AzCopy or uploading files into the portal or Storage Explorer. But let’s focus on backup to URL. The first thing you need to do is create a credential in SQL Server. While there are other methods of creating a credential, I have had absolutely the best luck, using this PowerShell code which produces a CREATE CREDENTIAL statement to execute on your SQL instance.
CREATE CREDENTIAL [https://storageaccount.blob.core.windows.net/sqlbackups] WITH IDENTITY='Shared Access Signature', SECRET='sv=2020-08-04&si=sql1&sr=c&sig=Dw0uE13l2347%2FCNJgQbm5sHhM9ZtDts9F8SMi5Re1ASUw%3D'
The SAS token you create is created on the container level–you will also need to create a policy (that code from MS will create everything for you including the storage account). Next execute the backup.
EXECUTE dbo.DatabaseBackup@Databases = 'DB1',@URL = 'https://storageaccount.blob.core.windows.net/sqlbackups',@BackupType = 'LOG',@Compress = 'Y',@Checksum = 'Y',@Verify = 'Y',@DirectoryStructure = '{DatabaseName}',@NumberOfFiles = 8,@MinBackupSizeForMultipleFiles = 10240,@logtotable=y
I’m using Ola Hallengren’s code to do my backups, but you can also do this manually.
BACKUP LOG [db1] TO URL = N'https://storageaccount.blob.core.windows.net/sqlbackups/db1/687593983fe8_db1_LOG_20211216_162916.trn' WITH CHECKSUM, COMPRESSION
I highly recommend using Ola’s code to perform these backups and all of your backups. A couple of things you should note–you have to use the checksum option, and with Ola’s backups I’m using the Directory Structure of {DatabaseName}, The reason for this is that LRS does not support nested directories, like Ola’s code would default to, nor does it support back ups stored in the root of the container. (You should note the backup example on the LRS doc page does just this).
Docs also say you need a full, differential, and log backup to do a complete migration, however in my testing I’ve been successful with just a log backup in addition to a full. To complete your restore process, you will need to have the name of your last log backup. You can use this code to generate the powershell code to initiate the migration process.
DECLARE @DBName SYSNAME;
DECLARE @RGName VARCHAR(256) = 'ResourceGroupName'
DECLARE @MIName VARCHAR(256) = 'ManagedInstanceName'
DECLARE database_cursor CURSOR FOR
SELECT name FROM MASTER.sys.sysdatabases where name not in ('master','msdb','model','tempdb')
create table #migration (command nvarchar(max))
OPEN database_cursor FETCH NEXT FROM database_cursor INTO @DBName
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @lastlogbackup = REVERSE(SUBSTRING(REVERSE(physical_device_name ),1,CHARINDEX('/',REVERSE(physical_device_name )) - 1))FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]WHERE (bs.database_name = @DBName OR @DBName IS NULL) AND bs.type = 'L'ORDER BY bs.backup_start_date DESC;
insert into #migration select ‘Start-AzSqlInstanceDatabaseLogReplay -ResourceGroupName “'+@RgName+'" -InstanceName "'+@MiName'" ` -Name "'+@dbName+'" `-LastBackupName "'+@lastlogbackup+'" -Collation "SQL_Latin1_General_CP1_CI_AS" -StorageContainerUri "https://storageaccount.blob.core.windows.net/sqlbackups/'+@dbname+'/" -AutoCompleteRestore -StorageContainerSasToken "sp=rl&st=2021-12-14T21:25:07Z&se=2021-12-25T05:25:07Z&spr=https&sv=2020-08-04&sr=c&sig=h8%2FimcTh%2BnlCQ3WodgTShWERT7yA38HfBQFOLwvEoiqw%3D"'
FETCH NEXT FROM database_cursor INTO @DBName
END
CLOSE database_cursor DEALLOCATE database_cursor
select * from #migration
For a small database, this only takes a few minutes. If your PowerShell execution goes out to lunch, and your database is stuck in restoring, there is probably something wrong with the way your formatted your backup string. This also manifests itself in the form of http 204 error in the log of your managed instance.
Message
Sending the notification action: NotifyRestoreProgress
Date 12/9/2021 8:09:42 AM
Log SQL Server (Current – 12/9/2021 4:27:00 AM)
Source spid101
Message
Http code after sending the notification for action NotifyRestoreProgress: 204
What’s annoying is that the PowerShell doesn’t attempt to do any validation of requirements. Hoepfully, this will change in the future because this was a challenging problem to troubleshoot.
Message
