Solving the Data Residency Puzzle: The Azure SQL "Join Engine" Pattern

Designing a Compliant, Cross-Region Data Access Architecture on Azure

Balancing GDPR, HIPAA, PCI DSS with Performance and Scalability

In modern enterprise platforms, data compliance and global reporting requirements often pull architecture in opposite directions.

In this blog, I’ll share how we designed a Global Compliance System that enables cross-region data access and reporting while strictly honoring data residency, security, and regulatory constraints such as GDPR, HIPAA, and PCI DSS.

The Challenge

Our platform manages two very different categories of data:

🔒 1. Sensitive Client Data (Regional)

• Stored only within its originating geographic region
• Subject to strict compliance and data residency laws
• No replication or movement across regions is allowed

🌍 2. Non-Sensitive / Generic Client Data (Global)

• Stored in a global database
• Replicated across multiple regions
• Supports global operational and reporting use cases

The challenge was clear:

How do we enable cross-region queries and reporting that join sensitive regional data with global data—without violating compliance, security, or performance requirements?

Initial Approaches & Why They Fell Short

❌ Approach 1: API-First Aggregation

We initially tried an API-first orchestration approach, where:

• Each regional database exposed APIs
• The application layer aggregated data across regions

Challenges encountered:

• Pagination across multiple datasets became extremely complex
• Sorting and keyword search required loading large datasets into memory
• Lazy loading still led to high memory usage and poor performance
• Increased application complexity and operational overhead

👉 Conclusion: Not scalable for enterprise-grade reporting.

❌ Approach 2: Azure SQL Elastic Queries

Next, we evaluated Azure SQL Elastic Queries to query multiple databases directly using a single SQL statement.

Limitations:

• Feature was not GA (still in preview)
• Required databases to be publicly accessible within Azure
• Introduced significant security concerns
• Not suitable for regulated workloads

👉 Conclusion: Powerful concept, but not enterprise-ready for compliance-sensitive systems.

❌ Approach 3: All-in on Azure SQL Managed Instance

We then explored consolidating both global and sensitive data into Azure SQL Managed Instance (MI).

Findings during POC:

• We encountered multiple challenges while managing global data replication across different Azure regions
• Auto-failover capabilities were not as mature as Azure SQL Database
• Introduced unnecessary coupling between global and regional datasets

👉 Conclusion: Useful, but not optimal as a single unified solution.

The Final Architecture: A Hybrid, Compliance-First Model

After multiple iterations, we arrived at a hybrid architecture that cleanly separates responsibilities while enabling secure cross-region access.

✅ Architectural Principles

• Sensitive data never leaves its region
• Global reporting remains scalable and highly available
• Queries execute as close to the data as possible
• No public endpoints or cross-internet traffic

Proposed Architecture Overview

🔹 Azure SQL Database

• Hosts Global and Reporting databases
• Supports:

🔹 Azure SQL Managed Instance (Regional)

• Hosts Sensitive regional databases
• Acts as a secure gateway for cross-database access
• Uses private networking only
• No cross-region replication of sensitive data

Why Azure SQL Managed Instance?

The key enabler here is Linked Server support.

🔗 Linked Servers: The Game Changer

Linked Servers allow:

• Distributed queries across databases
• Secure access to heterogeneous data sources
• Cross-database joins without custom application logic
• Centralized governance at the database layer

Advantages include:

• SQL-level joins instead of in-memory application joins
• Reduced application complexity
• Better performance and pagination support
• Enterprise-grade security controls

Read-Only Access Flow (Reporting & Queries)

1. A user accesses the platform from any geo-region
2. The request is routed to the nearest regional application instance
3. The application connects to the regional Managed Instance
4. Managed Instance uses Linked Servers to:
5. SQL executes entirely within private networks and the same geo-region

🔹 Result: Minimal latency, high security, and compliant cross-database queries.

Write Scenarios (Controlled & Audited)

While most cross-region scenarios are read-heavy, certain workflows require writes.

✍️ Global Writes

• Regional Managed Instance acts as a gateway
• Linked Server connects to the Global/Reporting database (e.g., West Europe)
• Writes occur over Azure’s private backbone
• Latency is low and predictable

✍️ Cross-Region Sensitive Writes (Exceptional Cases)

• Same Linked Server approach can be used
• Subject to:

Key Benefits of This Hybrid Approach

1. Compliance-first by design
2. No sensitive data replication
3. SQL-native joins and pagination
4. Reduced application complexity
5. Strong performance characteristics
6. Enterprise-grade security posture

Final Thoughts

There is no single Azure service that solves cross-region, compliance-heavy reporting out of the box.

But with:

• Clear data classification
• Thoughtful service separation
• Azure SQL Managed Instance acting as a secure gateway

…it is possible to build a globally scalable, compliant, and performant architecture without compromising on regulatory requirements.