Overview

Scenarios

This practice describes how to synchronize data from Azure SQL DB to RDS for SQL Server. It covers the entire workflow, from environment preparation and configuration optimization to monitoring and maintenance, ensuring the stability and reliability of data replication.

• In the environment preparation phase, you need to ensure network connectivity between the source and destination databases and configure the necessary permissions. You are advised to test the compatibility of the source and destination systems to prevent potential compatibility issues.
• In the configuration optimization phase, choose an appropriate replication mode based on the data volume and service requirements. For large-scale data replication, incremental replication is recommended to minimize the impact on workloads. Additionally, you can set priorities for the replication tasks to ensure that critical data is replicated first.
• Monitoring and maintenance are crucial for ensuring the stable operation of DRS. You can periodically check the status of replication tasks and address any exceptions promptly. You are advised to enable the logging function to facilitate fault locating and performance analysis.

Solution Architecture

This solution offers an efficient and reliable technical architecture to meet service requirements while ensuring system scalability and security.

With the architecture design involving extracting source Azure SQL data layer using SSMS, importing the dacpac file exported from the source to the destination, disabling all triggers and foreign keys on the destination, creating a full+incremental task on DRS, and restoring all triggers and foreign keys while performing random sampling comparison during migration, this solution can effectively support rapid service development while ensuring high system performance and availability.

Figure 1 Architecture

Advantages

• High reliability: The native dacpac file is used to migrate structures, ensuring precise database object definitions. DRS supports full+incremental synchronization, maintaining data integrity and a high task success rate through pre-check and alarm monitoring.
• Minimal impact on workloads: DRS incremental synchronization allows data changes to be synchronized in real time without interrupting workloads. Service interruption during workload cutover is minimized to just a few minutes.
• Ease of use: SSMS and DRS provide GUIs for configuring structure migration and data synchronization tasks, significantly reducing the complexity and risk associated with manual operations.

Notes on Usage

DRS cannot synchronize the SQL_VARIANT, GEOMETRY, GEOGRAPHY, HIERARCHYID, and custom data types on SQL Server.

For details, see From Microsoft SQL Server to Microsoft SQL Server.

In Azure SQL Database, change data capture is available for Standard S3 and later, Premium, Business Critical, and General-purpose. Basic, Standard S0, Standard S1, and Standard S2 do not support change data capture. If you want to perform an incremental synchronization task and the version does not meet requirements, upgrade the version to the required one. For details, see Change Data Capture on the Azure official website.