This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
In this article, I will share with you the steps needed to copy your Azure SQL database from one of servers to another on a different subscription and different tenant (Microsoft Entra). Usually, it is the best approach when you have different subscriptions and tenants for different environments, like when you want to refresh your development or preprod with a new and up to date copy of your production database.
1. Both servers' firewalls must be configured to allow inbound connection from the IP of my machine:
Make sure to enable public access and add my IP to both source and destination:
2. To find my IP using SSMS and my connection:
3. On source server and on master database, create login and user:
4. On source Server and user database, create the user in the source database and grant dbowner permission to the database:
5. On source server and on master database: Capture the SID of the user "testcopyuser", copy the result as it will used on the destination server:
6. Connect to Destination server, create a login and a user in the master database, be sure to have the same SID of the user on the source server:
7. On destination server and on master database: Execute the copy of database script from the destination server using the created user:
8. You can modify the service objective and backup redundancy of the destination database using the same command CREATE DATABASE..AS COPY OF:
9. Monitor the operation using sys.dm_operation_status - SQL Server | Microsoft Learn and check database existence and it is state:
Other ways to clone your database:
Second option:
You can create a geo-secondary in a subscription different from the subscription of the primary under a different Azure Active Directory tenant, follow the steps in this documentaion “Cross-subscription geo replication”: https://docs.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview#cross-subscription-geo-replication
With Private Endpoint, please review this Article: Azure SQL Database - GEO Replication across subscription with private endpoints: Azure SQL Database - GEO Replication across subscription with private endpoints (microsoft.com)
The following tutorial is for how to create a virtual network peering - between different subscriptions and Azure Active Directory tenants:
Create a VNet peering - different subscriptions | Microsoft Docs
Please note that as per the URL: Active geo-replication - Azure SQL Database | Microsoft Learn
Adding a geo-secondary using T-SQL is not supported when connecting to the primary server over a private endpoint. If a private endpoint is configured but public network access is allowed, adding a geo-secondary is supported when connected to the primary server from a public IP address. Once a geo-secondary is added, public network access can be denied.
Also, creating a geo-secondary on a logical server in a different Microsoft Entra tenant is not supported when Microsoft Entra-only authentication is enabled on either primary or secondary logical server.”
Third option:
Using SQLpackage tool on Azure VM to Export/Import bacpac file.
The following tips are specific to running import or export against Azure SQL Database from an Azure virtual machine (VM):
- Use Business Critical or Premium tier database for best performance.
- Use SSD storage on the VM and ensure there is enough room to unzip the bacpac.
- Execute SqlPackage from a VM in the same region as the database.
- Enable accelerated networking in the VM.
For more information about SQLpackage.exe utility:
SqlPackage.exe - SQL Server | Microsoft Docs
For installing the last released versions of SqlPackage.exe check the link: https://docs.microsoft.com/en-us/sql/tools/release-notes-sqlpackage?view=sql-server-ver15