How to copy Azure SQL database to a different subscription and different tenant

[tarashee]

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:



SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID;







3. On source server and on master database, create login and user:



CREATE LOGIN testcopyuser WITH PASSWORD = 'T@stCopyUser1'

GO

CREATE USER testcopyuser FOR LOGIN testcopyuser WITH DEFAULT_SCHEMA=[dbo];

GO

ALTER ROLE dbmanager ADD MEMBER testcopyuser;







4. On source Server and user database, create the user in the source database and grant dbowner permission to the database:



CREATE USER testcopyuser FOR LOGIN testcopyuser WITH DEFAULT_SCHEMA=[dbo];

GO

ALTER ROLE db_owner ADD MEMBER testcopyuser;







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:



SELECT [sid] FROM sysusers WHERE [name] = 'testcopyuser';









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:



CREATE LOGIN testcopyuser WITH PASSWORD = 'T@stCopyUser1', SID = 0x01060000000000640000000000000000BBBA2D6017A7404C8E3ABD4DF3F00935;

GO

CREATE USER testcopyuser FOR LOGIN testcopyuser WITH DEFAULT_SCHEMA=[dbo];

GO

ALTER ROLE dbmanager ADD MEMBER testcopyuser;









7. On destination server and on master database: Execute the copy of database script from the destination server using the created user:



CREATE DATABASE testdestination1
AS COPY OF trdba.test;





8. You can modify the service objective and backup redundancy of the destination database using the same command CREATE DATABASE..AS COPY OF:

CREATE DATABASE (Transact-SQL) - SQL Server



9. Monitor the operation using sys.dm_operation_status - SQL Server | Microsoft Learn and check database existence and it is state:



select * from sys.dm_operation_status











select name,create_date,state_desc from sys.databases where name = 'testdestination1'







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.

As per the URL: Troubleshooting issues and performance with SqlPackage - SQL Server

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: DacFx and SqlPackage release notes - SQL Server

Continue reading...