Data Transmission Service:Synchronize data from RDS for SQL Server to AnalyticDB for PostgreSQL

Considerations

• During a full data migration, DTS consumes read and write resources on the source and destination databases, increasing their load. If your databases have poor performance, low specifications, or high workloads (for example, if the source database has many slow SQL queries or tables without primary keys, or if deadlocks occur in the destination database), the increased load can strain your databases or even cause service interruptions. Perform the data migration during off-peak hours, such as when the CPU utilization of both databases is below 30%.

• DTS creates a heartbeat table named dts_log_heart_beat in the source database for latency monitoring.

• DTS supports initial schema synchronization for the following object types: Schema, Table, View, Function, and Procedure.

  Warning

  Because this is a heterogeneous synchronization task, data types may not map one-to-one. Evaluate the impact of data type mapping on your business before you proceed. For more information, see Data type mappings for initial schema synchronization.

• Synchronization is supported at the table level (append-optimized tables are not supported). You can modify column mappings. If you use column mapping for partial table synchronization, or if the source and destination table schemas differ, data from source columns that are not in the destination table is lost.

• The following data types cannot be synchronized: TIMESTAMP, CURSOR, ROWVERSION, HIERARCHYID, SQL_VARIANT, SPATIAL GEOMETRY, SPATIAL GEOGRAPHY, and TABLE.

Billing

Supported SQL operations

• DDL operations: ADD COLUMN

  Note

  Transactional DDL operations cannot be synchronized.

• DML operations: INSERT, UPDATE, DELETE

Permissions required for database accounts

Procedure

1. Purchase a synchronization task. For more information, see Purchase procedure.

   Note

   When you make the purchase, select the source instance as SQL Server, the destination instance as AnalyticDB for PostgreSQL, and the synchronization topology as One-way Synchronization.

2. Log on to the DTS console.

3. In the left-side navigation pane, click Data Synchronization.

4. At the top of the Synchronization Tasks page, select the region where the destination instance is located.

5. Find your data synchronization task and click Configure Synchronization Channel.

6. Configure the source and destination instances.

   Category	Setting	Description
   N/A	Synchronization Task Name	DTS automatically generates a task name. We recommend that you use a descriptive name for easier identification. The name does not need to be unique.
   Source Instance Details	Instance Type	Select RDS Instance.
   	Instance Region	The region of the source instance selected during purchase. This setting cannot be changed.
   	Instance ID	Select the ID of the source ApsaraDB RDS for SQL Server instance.
   	Database Account	Enter the database account for the ApsaraDB RDS for SQL Server instance. For more information about the required permissions, see Permissions required for database accounts.
   	Database Password	Enter the password for the database account.
   	Connection Method	Select Non-encrypted or SSL-encrypted based on your requirements. If you select SSL-encrypted, you must enable SSL encryption for the RDS instance first. For more information, see Configure SSL encryption. Note The Encryption setting is available only in the Chinese mainland and China (Hong Kong) regions.
   Destination Instance Details	Instance Type	Select AnalyticDB for PostgreSQL.
   	Instance Region	The region of the destination instance selected during purchase. This setting cannot be changed.
   	Instance ID	Select the ID of the destination AnalyticDB for PostgreSQL instance.
   	Database Name	Enter the name of the destination database that contains the tables to synchronize.
   	Database Account	Enter the database account for the AnalyticDB for PostgreSQL instance. For more information about the required permissions, see Permissions required for database accounts.
   	Database Password	Enter the password for the database account.

7. In the lower-right corner of the page, click Set Whitelist and Next.

   If the source or destination database is an Alibaba Cloud database instance, such as an ApsaraDB RDS for MySQL or ApsaraDB for MongoDB instance, DTS automatically adds the CIDR blocks of DTS servers to the IP address whitelist of the instance. If the source or destination database is a self-managed database hosted on an Elastic Compute Service (ECS) instance, DTS automatically adds the CIDR blocks of DTS servers to the security group rules of the ECS instance, and you must make sure that the ECS instance can access the database. If the self-managed database is hosted on multiple ECS instances, you must manually add the CIDR blocks of DTS servers to the security group rules of each ECS instance. If the source or destination database is a self-managed database that is deployed in a data center or provided by a third-party cloud service provider, you must manually add the CIDR blocks of DTS servers to the IP address whitelist of the database to allow DTS to access the database. For more information, see Whitelist DTS server IP addresses.

   Warning

   Adding the public IP address blocks of the DTS service, either automatically or manually, may pose security risks. Using this product, you acknowledge that you understand and accept the potential security risks and that you must implement basic security measures. These measures include, but are not limited to, strengthening password security, limiting the ports open to each CIDR block, using authentication for internal API calls, and regularly checking and restricting unnecessary CIDR blocks. Alternatively, you can connect through a private network using a leased line, VPN Gateway, or Smart Access Gateway.

8. Configure the synchronization policy and objects.

   Setting	Description
   Synchronization types	By default, Initial Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization are selected. After the precheck completes, DTS synchronizes the schemas and existing data of the selected objects to the destination database as a baseline for incremental synchronization.
   Processing mode for existing destination tables	Precheck and Report Errors: DTS checks for destination tables with the same names as the source tables. The precheck passes if no matching tables are found. If a matching table exists, the precheck fails and the task does not start. Note If the destination database already contains a table with the same name that cannot be deleted or renamed, you can rename the destination table. For more information, see Rename a synchronization object in the destination instance. Ignore Errors and Proceed: Skips the check for identically named tables in the destination database. Warning Selecting Ignore Errors and Proceed may cause data inconsistency. For example: If the schemas match and a destination record has the same primary key as a source record, the destination record is preserved and the source record is not synchronized. If the schemas differ, initial data synchronization may fail or synchronize only a subset of columns.
   Table merging	Yes: In OLTP scenarios, sharding is often used to improve response times. AnalyticDB for PostgreSQL can store massive amounts of data in a single table, which simplifies queries. You can use table merging to synchronize multiple source tables with the same schema into a single AnalyticDB for PostgreSQL table. Note After selecting multiple source tables, use the object name mapping feature to map them to a single table in AnalyticDB for PostgreSQL. For more information, see Rename a synchronization object in the destination instance. You must add a __dts_data_source column of the TEXT type to the destination table to track data origin. DTS writes values in the format <DTS data synchronization instance ID>:<source database name>.<source schema name>.<source table name>, such as dts********:dtstestdata.testschema.customer1. Table merging is a task-level feature and cannot be applied to individual tables. If you need to merge some tables but not others, you must create two separate synchronization tasks. No: This is the default option.
   Operation types	Select the operation types to synchronize based on your business needs. All operation types are selected by default.
   Synchronization objects	In the Source Objects box, click the objects that you want to synchronize, and then click the icon to move them to the Selected Objects box. Because this is a heterogeneous synchronization scenario, only tables can be synchronized. Other objects such as views, triggers, and stored procedures are not supported. Note By default, synchronized object names remain unchanged. To use different names in the destination instance, see Rename a synchronization object in the destination instance. If you set Merge Tables to Yes, you must use the object name mapping feature to map the selected source tables to a single table name in AnalyticDB for PostgreSQL.
   Add quotation marks to object names	Specifies whether to quote destination object names. If you select Yes and any of the following conditions are met, DTS adds quotation marks to destination object names during schema synchronization and incremental data synchronization: The source database environment is case-sensitive and uses mixed-case names. The source table name does not start with a letter, or contains characters other than letters, numbers, and supported special characters. Note Only underscores (_), pound signs (#), and dollar signs ($) are supported as special characters. The schema, table, or column name to be synchronized is a keyword, reserved word, or invalid character in the destination database. Note If you add quotation marks, you must use the quoted names when querying the objects after synchronization.
   Edit mapped names	Change the names of synchronized objects in the destination instance. For more information, see Map databases, tables, and columns.
   Connection retry duration	If DTS cannot connect to the source or destination instance, it retries for 720 minutes (12 hours) by default. You can also specify a custom retry duration. If DTS reconnects to the source or destination instance within the specified duration, the synchronization task automatically resumes. Otherwise, the task fails. Note You are billed for task run time during connection retries. Customize the retry duration based on your business needs, or release the DTS instance as soon as the source and destination instances are released.

9. Configure the table type, primary key columns, and distribution key for the destination tables in AnalyticDB for PostgreSQL.

   Note

   For more information about primary key columns and the distribution key, see Table constraints and Table distribution key.

10. After you complete the preceding configurations, click Precheck and Start in the lower-right corner of the page.

    Note

    • Before the synchronization task is officially started, a precheck is performed. The synchronization task can only be started after the precheck passes.

    • If the precheck fails, click the icon next to a specific check item to view the failure details.

      • Follow the prompt to fix the issue and run the precheck again.

      • If you do not need to fix the warning check items, you can also select Ignore, Ignore Warning Items and Rerun Precheck to skip the warning check items and run the precheck again.

11. After the Precheck dialog box shows that Precheck Passed, close the Precheck dialog box. The synchronization task starts.

12. Wait for the task to complete initialization and enter the Synchronizing state.

    You can view the status of the data synchronization task on the Data Synchronization page.

FAQ

Q: How do I find the destination tables in the AnalyticDB for PostgreSQL instance?