SQL Managed Instance – Cross Subscription Restores and Migration

azure-sql-managed-instancemigrationrestore

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.

Best Answer

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.

Related Question