Cross-tenant Azure SQL Database schema sync and data copy using pipelines from a single tenant

Question

I have the following scenario and would like guidance on feasibility and best practices.

Scenario:

• I have one Azure SQL Database in my tenant.

I need to sync with another Azure SQL Database in a client’s tenant.

Both databases have the same schema (tables, views, etc.).

I want to:

Copy table schemas / views (schema sync).

  Copy **data from selected tables** to the same tables in the client database.
  
  All **pipelines (Azure DevOps / ADF)** are hosted and executed **only from my tenant**, not the client’s tenant.
  

Questions:

Is this cross-tenant Azure SQL schema and data synchronization possible?

If yes, what is the recommended approach to achieve this securely and reliably using Azure services?

Any guidance, architecture suggestions, or real-world approaches would be appreciated.

Answer 1

Hi Faisal Riaz,
Yes, cross-tenant Azure SQL schema and data synchronization is entirely feasible using Azure services like Azure Data Factory for data movement and Azure DevOps for schema deployments, all executed from your tenant. This keeps everything under your control while securely accessing the client's database through service principals. Since both databases share the same schema, mapping becomes straightforward, and you can selectively copy data from specific tables as needed.​

For schema synchronization, the best approach involves generating DACPAC files from your database project and deploying them via Azure DevOps Release Pipelines. Set up multi-stage pipelines where each stage targets the client's Azure SQL Database using a service principal created in their tenant for authentication. Use Azure CLI or PowerShell tasks to publish schema-only DACPACs, ensuring no data is altered during these deployments. This method supports automation and handles tables, views, and other objects reliably across tenants.​

Data synchronization works best with ADF pipelines hosted in your tenant, leveraging Copy Activities or Data Flows to move data from selected source tables to matching sink tables in the client database. Create linked services for both databases authenticated via service principals—the client's SP needs db_datareader on source tables and db_datawriter on sinks, with credentials securely stored in your Azure Key Vault. For ongoing incremental syncs, enable Change Tracking or CDC on your source tables and use ADF watermarks or change data capture to efficiently handle deltas, avoiding full reloads on large datasets.​

Security remains paramount: always store service principal secrets or certificates in Key Vault, grant least-privilege permissions like db_datareader/db_datawriter, and consider private endpoints for VNet isolation if networking policies require it. Monitor pipelines through ADF activity logs and Azure Monitor, and enable SQL Auditing on the client side for compliance. This setup mirrors real-world patterns used in multi-tenant SaaS scenarios, providing reliability without needing access to the client's tenant for pipeline execution.​

While SQL Data Sync offers an alternative via PowerShell for cross-tenant member registration, it falls short for selective table syncs and requires more manual schema handling, making ADF and DevOps the superior, scalable choice for your needs.

Answer 2

Yes, cross-tenant Azure SQL Database schema and data synchronization is possible. Here are some recommended approaches to achieve this securely and reliably using Azure services:

1. Schema Synchronization: To copy table schemas and views from your Azure SQL Database to the client's Azure SQL Database, you can use tools like SQL Server Management Studio (SSMS) or Azure Data Studio to generate scripts for the schema. You can then execute these scripts in the client's database to create the same schema.
2. Data Copy: For copying data from selected tables, Azure Data Factory (ADF) is a suitable choice. You can create a pipeline in ADF that connects to both databases. Here’s how you can set it up:
   • Linked Services: Define linked services in ADF for both your Azure SQL Database and the client's Azure SQL Database. Ensure that you have the necessary permissions to access the client's database.
   • Data Flows or Copy Activity: Use ADF's Copy Data activity to transfer data from your database to the client's database. You can configure the source and sink datasets to specify which tables to copy.
   • Security: Ensure that the connection strings and credentials are securely managed. You can use Azure Key Vault to store sensitive information like connection strings and access keys.
3. Execution from Your Tenant: Since your pipelines are hosted and executed only from your tenant, ensure that you have the necessary network configurations (like firewall rules) to allow ADF to connect to the client's Azure SQL Database.
4. Testing and Validation: Before executing the full data copy, test the pipeline with a small dataset to validate the process and ensure that data integrity is maintained.
5. Monitoring and Logging: Implement monitoring for your ADF pipelines to track the success or failure of data transfers and to log any errors that may occur during the synchronization process.

By following these steps, you can achieve a secure and reliable cross-tenant schema and data synchronization between Azure SQL Databases.

---

References:

• Copy and transform data in Azure SQL Database by using Azure Data Factory or Azure Synapse Analytics
• Architectural approaches for storage and data in multitenant solutions