Copying Databases Across SQL Managed Instances

It only seems like yesterday since it was my turn to write a blog. And yet, here we are again, still staring at the M&M dispenser full of dark chocolate peanut M&Ms. (In my last blog I called it a carousel, when in reality, it is more like a Ferris wheel.)

Anyway, in this blog post I want to cover a technology for copying databases across SQL Managed Instances that saved my posterior recently. Well, it didn’t really save it, but it sure made my life easier. We have a client who uses Azure SQL Managed Instance for both their production and test environments. I think this is awesome.

As a quick background, Azure SQL Managed Instance is a full SQL Server instance served up as a PaaS service. Unlike Azure SQL Database where you are only working at the database level, Managed Instance gives you near 100% instance compatibility level capabilities with the latest version of SQL Server Enterprise Edition SQL Server database engine. SQL Managed Instance provides the lift and shift capabilities of on-premises databases with minimal database changes.

So, back to our customer. They essentially lifted and shifted their on-premises databases to Azure SQL Managed Instance and have been using it successfully for nearly two years. Again, this is awesome.

Last week they came to us and asked about reporting with Managed Instance. They were looking at data marts and data warehouses, but we needed more information. It turns out they have some people that just want the ability to query the databases, and potentially hook up Excel to these databases for data analysis.

The caveat is that the people I was talking to didn’t want to give the other group direct access to the production environment. Toootally get that. Yeah, like 100% get it. So, what are the options?

After some discussion we decided to give the group wanting to run reports access to the test Managed Instance environment. We’ll just refresh the test environment data with the production environment data on a schedule. For them, refreshing data nightly is OK for now.

So, the question then becomes, how do you get data, or copy a database, from one SQL Managed Instance to another SQL Managed Instance? There are two ways, and I’ll discuss both here.

PowerShell cmdlets

Microsoft released some Powershell cmdlets that make copying data across SQL Managed Instances simple.

There are essentially 3 cmdlets:

• Copy-AzSqlInstanceDatabase – Starts an online copy operation.
• Get-AzSqlInstanceDatabaseCopyOperation – Returns copy operation details.
• Complete-AzSqlInstanceDatabaseCopy – Completes the copy operation.

The Copy-AzSqlInstancesDatabase cmdlet starts an online copy operation of a database across managed instances using Always On availability group technology. Copy-AzSqlInstanceDatabase (Az.Sql) | Microsoft Learn

The Get-AzSqlInstanceDatabaseCopyOperation cmdlet returns database copy operation details. You can call this cmdlet in a loop so you can track the status of the copy. Get-AzSqlInstanceDatabaseCopyOperation (Az.Sql) | Microsoft Learn

The Complete-AzSqlInstanceDAtabaseCopy cmdlet completes the copy operation of the database and makes it available for read/write workloads. Complete-AzSqlInstanceDatabaseCopy (Az.Sql) | Microsoft Learn

You don’t have to call the Get-AzSqlInstanceDatabaseCopyOperation cmdlet as you could just call the Copy and Complete, but it is HIGHLY recommended you call it so you know when to call the complete.

The way this works is you start by calling the Copy-AzSqlInstancesDatabase cmdlet. This starts an online copy operation of one or more databases. Once the copy option starts, data is being seeded from the source to the target. During the process, you can call the Get-AzSqlInstanceDatabaseCopyOperation cmdlet to check the copy operation status, either in-progress or completed.

Once the copy operation has completed, the status will be copy ready for completion. Within the next 24 hours the user can complete the database copy operation by calling the Get-AzSqlInstanceDatabaseCopyOperation cmdlet. Note that until the Get-AzSqlInstanceDatabaseCopyOperation cmdlet is called, all the changes from the source are being replicated to the target database.

When Get-AzSqlInstanceDatabaseCopyOperation is called, the link between the source and target databases is broken, the target database becomes online and ready.

A very simple example would be something like this:

Source code

$dbname = "db1"
$sourceMI = "sqlmi1"
$targetMI = "sqlmi2"
$sourceRG = "rg1"
$targetMI = "rg2"
 
Copy-AzSqlInstanceDatabase `
    -DatabaseName $dbName `
    -InstanceName $sourceMi `
    -ResourceGroupName $sourceRg `
    -TargetInstanceName $targetMi `
    -TargetResourceGroupName $targetRg 
 
While(true$)
{
 
    Start-Sleep -Seconds 10
    $operation = Get-AzSqlInstanceDatabaseCopyOperation `
    -DatabaseName $dbname `
    -InstanceName $sourceMI `
    -ResourceGroupName $sourceRG `
    -TargetInstanceName $targetMI `
    -TargetResourceGroupName $targetRG
 
    try
    {
        $copysucceeded = $true
        Write-Host "Status of copy for " $dbname " is " $operation.State
        if (!($operation.State -eq "Succeeded")) {$copysucceeded = $false;}
    }
    catch { $copysucceeded = $false; Write-Host "Didn't work" }
 
    if ($copysucceeded)
    {
        Write-Host "Copy completed"
        break
    }
    else { Write-Host "Still copying..." }
 
}
 
Complete-AzSqlInstanceDatabaseCopy `
    -DatabaseName $dbName `
    -InstanceName $sourceMI `
    -ResourceGroupName $sourceRG `
    -TargetInstanceName $targetMI `
    -TargetResourceGroupName $targetRG

There is a GREAT example here: Using and automating Azure SQL Managed Instance database copy and database move operations with Azure PowerShell (microsoft.com)

The example copies multiple databases, but if you are needing to copy only one, you can tweak it.

Oh, by the way, you can also do the copy via the Azure portal.

Backup / Restore

The other option is backup and restore, meaning, back up to Azure blob storage then restore from there. Since Managed Instance provides access to the SQL Agent, a job is created on both managed instances; one to back up, and one to restore.

This option gets a bit tricky because in the scenario for our client, and I would think in most environments, you want to restore the database in the target managed instance with the same name, and have the backup file the same as well, just to make it easy.

The backup job is simple: back up the database to Azure, ensuring the backup file name is always the same name. The restore job is just as simple: delete the database and then restore the database with the same name.

However, the issue in this scenario is that when you back up to the same file name, it will error because the file already exists. When we tested we thought, “no problem, we’ll just use PowerShell (Remove-AzureStorageBlob) to remove the file as the first step in the job, then the second step in the job will be to back up the database!”

Unfortunately, Managed Instance does not support PowerShell job steps. Sigh. So, we have an on-premises job (or it can even be a Windows Task Scheduler job) that runs the delete. At this point it is just a timing thing. Since the client only wants to back up and restore a single database, we run the backup job at 1 a.m., for example. Then 30 minutes later we run the restore, then another 30 minutes later, we run the delete blob job.

For this solution you’ll need to create a storage account in Azure, then create a CREDENTIAL in SQL Server on both Managed Instances:

Source code

CREATE CREDENTIAL [https://mystorageaccount.blob.core.windows.net/mycontainer]
WITH IDENTITY N'SHARED ACCESS SIGNATURE',
SECRET = N'<storageaccountkey>'

Once that is created, simply back up the database.

Source code

BACKUP DATABASE [MyDB] TO URL = N'https://mystorageaccount.blob.core.windows.net/mycontainer/MyDB.bak'
	WITH BLOCKSIZE = 65535, COPY_ONLY, NOFORMAT, NOINIT, 
	NAME = N'MyDB-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10

Or, if you are using Ola Hallengren’s solution (which we are a major fan of):

Source code

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'SentryOne',
@URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer',
@Credential = 'https://mystorageaccount.blob.core.windows.net/mycontainer',
@BackupType = 'FULL',
@Verify = 'N',
@CheckSum = 'Y',
@LogToTable = 'Y',
@Compress = 'Y',
@CopyOnly = 'Y'

Then restore onto the other Managed Instance:

Source code

RESTORE DATABASE [MyDB] FROM URL = N'https://mystorageaccount.blob.core.windows.net/mycontainer/MyDB.bak'

Yeah, a bit cumbersome, but it works and runs, and solves the client’s problem.

Which?

As in all things SQL, it depends. For our client, the simple backup and restore was the solution. They were OK with data being up to 24 hours old for their reporting in this scenario.

If using the PowerShell cmdlets, the target database is unavailable for connections until you issue the Complete cmdlet. But it lets you copy multiple databases, not just one.

It really just comes down to what you are attempting to do. Honestly, for the customer, we could have easily used the PowerShell cmdlets to copy the database each night (deleting the database on the target Managed Instance before starting the copy) and that would eliminate the need to delete the file from blob storage.

So if you looking for ways of copying databases across SQL Managed Instances, you have several options.