3

Short Version:

I am trying to restore a backup from one SQL Managed Instance to another SQL Managed Instance. These are in different subscriptions. Using Example 3 from here I can at least get it to the point where I see the restore starting in sys.dm_operation_status, but the error message is "Subscription [target] does not contain [source SQL MI]".

Questions: How can I migrate the database from one SQL Managed Instance to another (in a different subscription)? My current workaround is to do copy_only to a blob storage and restore that way.

Once we enable TDE (in-progress) we won't be able to do copy_only backups to blob. What then?

Long Version:

Fairly recently our company made the jump to running our production workload in Azure on SQL Managed Instances (and other PaaS/IaaS) and this went fairly well. We had a large enough window identified that we were able to do the initial load with a full backup, although I did familiarize myself with the Database Migration Services (DMS).

But now we are expanding out our dev/test/qa environments and of course we now want to restore from production to these environments. For isolation purposes, each is getting it's own subscription.

These commands work, in the sense that I can at least see the restore operation start in sys.dm_operation_status but it fails with an error. The error is "Subscription [target] does not contain [source SQL MI]" and leads me to believe that cross subscription restores won't work for us (although cross region would).

DMS does not appear to support the scenario of using Database Migration Services as a source.

Select-AzSubscription -Subscription $SourceSubscriptionID

$GeoBackup = Get-AzSqlInstanceDatabaseGeoBackup -ResourceGroupName $SourceResourceGroupName `
     -InstanceName $SourceSQLMI `
     -Name $SourceDatabaseName

Select-AzSubscription -Subscription $TargetSubscriptionID

Restore-AzSqlInstanceDatabase -FromGeoBackup `
    -GeoBackupObject $GeoBackup `
    -TargetInstanceDatabaseName $TargetDatabaseName `
    -TargetInstanceName $TargetSQLMI `
    -TargetResourceGroupName $TargetResourceGroupName

Any advice, ideas? I'm willing to use preview versions of the various Azure PowerShell if necessary although I've not found anything to indicate that it's different there.

POSSIBLE WORKAROUND

I did get a link from one of our Microsoft contacts that seemed to indicate that you can stand up a managed instance in the same subscription, restore to it and then move the managed instance to another subscription. I have NOT attempted this so I don't know. The link they provided was here although I have my doubts about it's effectiveness.

2

copy only backups are currently the only solution. Cross subscription pitr and geo restore don't work because currently we cannot verify that initiator has all required permissions both on source and target subscriptions.

  • I'm marking this as the answer as it confirmed what I had already found. I did see a work-around posted (see my updated question) but I haven't directly attempted it, the copy to url method is what we are doing for now with a caution flag in our enable TDE project. – Jonathan Fite Sep 9 at 12:29
2

To add to Jovanpop's answer, you should still be able to take copy_only backups with TDE turned on if you are using AKV integration i.e. BYOK TDE, as long as your destination instance in other subscription has access to the same encryption key used on source instance.

  • That's a +1 to bringing our own key, although I liked the idea of letting Azure manage key rotation for us. I'll need to test that, the blog I read that on didn't specify one way or another. – Jonathan Fite Sep 5 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.