Daily Refresh of Non-Prod Azure SQL Managed Instances in different Subscriptions

Lonely Rogue 30 Reputation points
2023-07-19T06:08:48.21+00:00

Hello,

We have Azure SQL Managed Instances for each Env - Prod, Dev etc as in below table . We have to do a refresh of Dev everyday from Prod so that development activities can use latest data.

We used Backup-Restore in our on-prem setup and planning to use same strategy but found no success till now.

Env Prod Dev
Location West Europe West Europe
Resource group Resrc_Prod Resrc_Dev
Subscription Sub_Prod Sub_Dev
  1. Cross-instance point-in-time restore in Azure SQL Database Managed Instance : says Note that you could restore database to another instance within the same subscription and region. Cross-region restores are still not supported.
  2. Manual BACKUP to AZ Blob container failing with error because of Encryption. Disabling encryption isn't permissible.

Error 41922, Level 16, State 1, Line 9 The backup operation for a database with service-managed transparent data encryption is not supported on SQL Database Managed Instance.

  1. Copy or move a database (preview) - Azure SQL Managed Instance : This appears promising but limitations are -
  • Both the source instance and destination instance need to be in the same Azure subscription and same region.
  • The source instance can run up to eight copy or move operations at a time. You can start more than eight operations, but some are queued and processed later, as managed by the service. - There are 30+ DBs to be refresh and yet his can be survived considering the benefit.

Looking at all these options and limitations - unable to assess if daily refresh would be possible. Though my MIs are in same Region but are different RGs, Subscriptions & VNets.

-- In 'thoughts'...

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,484 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 117.1K Reputation points MVP
    2023-07-19T09:37:09.1033333+00:00

    Manual BACKUP to AZ Blob container failing with error because of Encryption. Disabling encryption isn't permissible.

    But what if you use BYOK, bring-your-own-key? That is, you supply your own key for the encryption? Unfortunately, I can't give the exact steps for this, but I would expect this to be possibly in Azure MI.

    You would use the same encryption key both in test and dev.


  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more

  3. StrahinjaRodic-MSFT 0 Reputation points Microsoft Employee
    2023-07-21T14:13:24.09+00:00

    There's a cross-subscription PITR feature available in MI today. That means you can restore from one managed instance to another in the same region. However, there're certain limitations:

    • Both subscriptions must be in the same region.
    • Both subscriptions must be in the same tenant.
    • The subscription type must be either Enterprise Agreement, Cloud Solution Provider, Microsoft Certified Partner, or pay-as-you-go.
    • You can use the restore action only on the primary instance.
    • Geo-replicated backups currently aren't supported for cross-subscription point-in-time restore.
    • The user who takes the restore action must either have the SQL Managed Instance Contributor role assignment or have these explicit permissions:
      • Microsoft.Sql/managedInstances/databases/readBackups/action on the source SQL managed instance.
        • Microsoft.Sql/managedInstances/crossSubscriptionPITR/action on the target SQL managed instance.
    • If you bring your own key (BYOK), the key must be present in both subscriptions.

    I hope this helps.

    0 comments No comments

  4. ShaktiSingh-MSFT 16,241 Reputation points
    2023-07-24T06:40:06.7466667+00:00

    Hi Lonely Rogue •,

    Thanks for your patience.

    I have checked with the internal team and Cross-subscription PITR is supported:

    https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/point-in-time-restore?view=azuresql&tabs=azure-portal#restore-to-a-different-subscription

    The link referred in the question is old blog post from 2018 when cross-subscription PITR still wasn’t available.

    Hope this helps.

    If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

    Thank you.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.