大型SQL Server关系数据库迁移至Azure并搭建跨多区域主主复制架构的可行性咨询

Absolutely, your proposed architecture is not only feasible but also a proven approach to tackle global low-latency access challenges for write-heavy workloads concentrated in specific regions. Let’s break this down for you, including key implementation details and self-hosted infrastructure considerations:

Your plan to deploy primary nodes in high-write regions (US and China) plus global read-only replicas aligns perfectly with common strategies for optimizing distributed database performance. Here’s why it works:

• Active-Active Dual Primaries for Critical Write Workloads: By hosting primary nodes in the two regions where most writes originate, you eliminate cross-continental write latency for your core user base. For SQL Server, this can be implemented via distributed availability groups (AGs) or bidirectional replication, allowing both nodes to accept writes while syncing data between regions.
• Global Read Replicas for Distributed Reads: Offloading read traffic to region-local replicas drastically reduces latency for users outside the US and China, while also reducing load on your primary nodes. SQL Server supports read-only replicas in availability groups, making this straightforward to set up.

Since you’re building your own private infrastructure, here are critical factors to address:

• Cross-Region Network Connectivity: Ensure low-latency, high-reliability links between your US and China data centers (e.g., MPLS专线 or dedicated VPN tunnels). SQL Server replication/sync relies on consistent network performance—aim for round-trip latency under 100ms if using synchronous sync, or adjust to asynchronous mode if latency is higher but you can tolerate minor data lag.
• Subnet & Security Configuration: Configure private subnet peering between regions to enable secure communication between database instances. Restrict traffic via firewall rules to only allow database-to-database replication traffic and application access, and enforce TLS encryption for all data in transit.
• VM Resource Sizing: Size primary node VMs to handle peak write loads—prioritize fast SSD storage (RAID 10 recommended) for transaction logs, sufficient CPU cores for query processing, and enough memory to cache frequently accessed data. Read replicas can be sized based on regional read traffic volumes.

• Distributed Availability Groups (DAGs) Setup:
  1. First, create local availability groups in your US and China data centers, each with their own primary node.
  2. Establish a distributed AG to connect the two local AGs, choosing between synchronous commit (for strict consistency) or asynchronous commit (for better performance with minor lag) based on your business requirements.
• Read-Only Routing:
  • Configure read-only intent routing for your AGs, so applications can automatically redirect read requests to the nearest replica. Set up a listener for each region to simplify application connection strings.
• Conflict Resolution:
  • Since you’ll have two writeable primary nodes, implement business-level safeguards to avoid conflicts (e.g., partition data by region using user location or a dedicated key). For edge cases, use SQL Server’s change data capture (CDC) or merge replication to detect and resolve conflicts.

• SQL Server AG Documentation: Follow official steps to set up distributed AGs, including configuring endpoints, adding replicas, and setting sync modes. Use SSMS to monitor replication latency and node health.
• Infrastructure Optimization:
  • Optimize VM storage for SQL Server by enabling write caching on SSD volumes and aligning disk partitions with database block sizes.
  • Design a monitoring pipeline to track cross-region sync latency, VM resource utilization, and database query performance (use tools like SQL Server Profiler or Extended Events).
• Disaster Recovery: Implement automated backups for each primary node, and test failover procedures between US and China primaries to ensure high availability.

内容的提问来源于stack exchange，提问作者sai-sreenath