Azure Sql Managed Instance database copy from one subscription tp another

Question

Hi,

I have 2 subscription

1. DEV Environment Subscription - Under this we created SQL Managed Instance which contains multiple database.
2. PROD Environment Subscription - Created sql managed instance.

Now I want to copy the database schema/data from DEV subscription to PROD environment subscription.

E.g - DEV Subscription > Sql Managed Instance > DEV Database
Want to copy DEV database to another sql managed instance which is under different subscription.

Please suggest.

Answer 1

Hi, @Pardeep Singh Welcome to the Microsoft Q&A forum, Thanks for posting your question. As per the document with point in time restore you can restore to a different subscription however please follow the limitation.

Answer 2

Thank you for your question. You can copy the Database using "Copy or move a database - Azure SQL Managed Instance". You can copy or move a database to another managed instance by using the Azure portal. Here is a reference document which has all the steps to perform copy operation https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/database-copy-move-how-to?view=azuresql#copy-or-move-database Let us know if you need any additional information or help. Thank you!

Answer 3

Hi Pardeep Singh •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you want Azure SQL Managed Instance (Azure SQL MI) database copy from one subscription to another.

Currently, at this time there is no direct way to move a managed instance itself, but we can have a workaround by moving the Azure SQL managed databases across Azure SQL managed instances that are hosted\located in different subscriptions.

  You can find the below options that can be considered if you are looking to achieve this, and please make sure to take into consideration the limitations of each method as it will depend on your environment.

1) Point-In-Time-Restore (PITR) cross subscription -> This method will allow you to restore a copy of your Azure SQL-managed databases (at some time in the past) to another Azure SQL-managed instance that is hosted on another subscription. 

  Taking into consideration that this method is restoring the managed database to another SQL-managed instance and while using this method you need to consider the limitations (such as both should be on the same region and same Tenant ID), you can check all the limitations by accessing this document.

  2) Failover groups (FoG) cross subscriptions -> by creating a failover group between your source and destination Azure SQL managed instance, this will allow you to have a replicated copy of your SQL managed database on the other subscription and once the replication is completed you can failover to the second managed instance.  

This option is recommended in case you would like to have minimal downtime. but, also we need to check the limitations (document) as for example, the two Azure SQL-managed instances can not be on the same Azure region.    For more information:

• Cross-Subscription Failover Group - Azure SQL Managed Instance - Microsoft Community Hub
• Auto-failover groups overview & best practices - Azure SQL Managed Instance | Microsoft Learn
• 3) Export-Import -> you can as well use the export–import BACPAC to move your Azure SQL-managed database, taking into consideration that this will cause downtime depending on the size of your database.   For Azure SQL managed instance it's supported to perform this action either using SQL Server Management Studio (SSMS) or SQLPackage. Export : Export a database to a BACPAC file - Azure SQL Database & Azure SQL Managed Instance | Microsoft Lea... Import: Import a BACPAC file to create a database in Azure SQL Database - Azure SQL Database | Microsoft Lea...

  Note: the above options will be using different Azure SQL-managed instance names since we are moving the databases, which will require using a different Azure SQL-managed instance name.  Restore to a different subscription Restoring a point-in-time restore backup across subscriptions has the following 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.

Reference Doc Link: How to move Azure SQL managed instance cross subscriptions

Cross-Subscription Failover Group - Azure SQL Managed Instance

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.