Use Data Transmission Service (DTS) to synchronize data from ApsaraDB RDS for SQL Server to AnalyticDB for PostgreSQL. DTS handles schema synchronization, full data synchronization, and incremental data synchronization, keeping your analytical database current as your transactional database changes.
What DTS supports in this scenario
| Capability | Details |
|---|---|
| Supported SQL Server versions | 2012, 2014, 2016, 2017, 2019 |
| Schema synchronization objects | Schema, table, view, function, procedure |
| DDL operations | ADD COLUMN only. Transactional DDL operations are not supported. |
| DML operations | INSERT, UPDATE, DELETE |
| Synchronization topology | One-way synchronization |
| Billing | Schema and full data synchronization: free. Incremental data synchronization: charged. See Billing overview. |
Limitations
Unsupported data types
DTS does not synchronize columns of the following data types: TIMESTAMP, CURSOR, ROWVERSION, HIERARCHYID, SQL\_VARIANT, SPATIAL GEOMETRY, SPATIAL GEOGRAPHY, and TABLE.
Heterogeneous database constraints
SQL Server and AnalyticDB for PostgreSQL are heterogeneous databases. Data types do not map one-to-one between them. Review the data type mappings for schema synchronization before you start, and evaluate the impact on your business.
Source and destination table requirements
Source tables must have primary keys.
Destination tables must have primary keys or unique indexes.
Prerequisites
Before you begin, ensure that you have:
An ApsaraDB RDS for SQL Server instance (version 2012, 2014, 2016, 2017, or 2019). See Create an ApsaraDB RDS for SQL Server instance and Overview of data synchronization scenarios
An AnalyticDB for PostgreSQL instance. See Create an AnalyticDB for PostgreSQL instance
Database accounts with the permissions described in the next sectionModify Account Permissions
Required permissions
Grant the following permissions before configuring the synchronization task.
ApsaraDB RDS for SQL Server
The database account must have owner-level permissions on the source database. See Modify the permissions of an account.
AnalyticDB for PostgreSQL
The database account needs the following permissions. You can use the instance's initial account, which has these permissions by default.
LOGIN permission
SELECT, CREATE, INSERT, UPDATE, and DELETE permissions on the destination tables
CONNECT and CREATE permissions on the destination database
CREATE permission on the destination schemas
COPY permission (required for memory-based batch copy operations)
See Manage users and permissions for more information.
Usage notes
DTS adds a heartbeat table named
dts_log_heart_beatto the source database to maintain synchronization latency accuracy.DTS consumes read and write resources on both databases during synchronization. To avoid degrading database performance, run synchronization tasks during off-peak hours—ideally when CPU utilization on both databases is below 30%.
If DTS fails to connect to the source or destination database, it retries for up to 720 minutes (12 hours) by default. Adjust this value based on your business requirements. During retries, you are charged for the DTS instance.
When DTS CIDR blocks are added to database whitelists or ECS security group rules, security risks may arise. Before starting synchronization, take preventive measures including: strengthening username and password security, limiting exposed ports, authenticating API calls, and regularly auditing whitelist rules. As an alternative, connect your database to DTS through Express Connect, VPN Gateway, or Smart Access Gateway.
Configure the synchronization task
Setting up a synchronization task involves three phases: purchasing and configuring the DTS instance, selecting objects to synchronize, and configuring AnalyticDB for PostgreSQL-specific table settings.
Step 1: Purchase and configure the DTS instance
Purchase a DTS instance. On the buy page, set:
Source Instance: SQL Server
Destination Instance: AnalyticDB for PostgreSQL
Synchronization Topology: One-way Synchronization
Log on to the DTS console.
In the left-side navigation pane, click Data Synchronization.
At the top of the Synchronization Tasks page, select the region where the destination instance resides.
Find the synchronization instance and click Configure Synchronization Channel in the Actions column.
Configure the source and destination instances.
Section Parameter Description N/A Synchronization task name A name for this task. Use a descriptive name to identify the task easily. Task names do not need to be unique. Source instance details Instance type Select RDS Instance. Instance region The source region selected on the buy page. Read-only. Instance ID The ID of the source ApsaraDB RDS for SQL Server instance. Database account The account with owner-level permissions on the source database. Database password The password for the database account. Encryption Select Non-encrypted or SSL-encrypted. To use SSL encryption, enable SSL encryption on the RDS instance first. NoteThe Encryption parameter is available only for regions in the Chinese mainland and the China (Hong Kong) region.
Destination instance details Instance type Fixed to AnalyticDB for PostgreSQL. Instance region The destination region selected on the buy page. Read-only. Instance ID The ID of the destination AnalyticDB for PostgreSQL instance. Database name The name of the destination database. Database account The account with the required permissions on the destination instance. Database password The password for the database account. 
Click Set Whitelist and Next in the lower-right corner. DTS automatically adds its server CIDR blocks to the IP address whitelist of Alibaba Cloud database instances and to the security group rules of ECS-hosted databases. For self-managed databases in data centers or on third-party cloud platforms, manually add the CIDR blocks. See Add the CIDR blocks of DTS servers.
Step 2: Select the synchronization policy and objects
Configure the synchronization policy and select the objects to synchronize.
Requirements for merging tables
When Merge Multi Tables is set to Yes:
Add a column named
__dts_data_source(data type: TEXT) to the destination table. DTS uses this column to record the source of each row in the format:<Data synchronization instance ID>:<Source database name>.<Source schema name>.<Source table name>. For example:dts********:dtstestdata.testschema.customer1.Use object name mapping to rename all selected source tables to the destination table name in AnalyticDB for PostgreSQL.
All source tables selected in the task are merged into the same destination table. To exclude specific tables from merging, create a separate synchronization task for those tables.
Setting Description Initialize synchronization Initial Schema Synchronization, Initial Full Data Synchronization, and Initial Incremental Data Synchronization are selected by default. DTS synchronizes schemas and existing data before starting incremental synchronization. Processing mode in existed target table Pre-check and Intercept: fails the precheck if the destination database has tables with the same names as source tables. Use object name mapping to rename conflicting tables. Ignore Errors and Proceed: skips the precheck for duplicate table names. If schemas match, DTS skips records with duplicate primary keys. If schemas differ, the task may fail or synchronize only partial columns. Merge multi tables Yes: merges multiple source tables with the same schema into a single destination table. Useful for OLTP sharding scenarios where you want to consolidate data in AnalyticDB for PostgreSQL. See requirements for merging tables below. No (default): each source table maps to its own destination table. Select the operation types Select the types of operations that you want to synchronize based on your business requirements. All operation types are selected by default. Select the objects to be synchronized Select objects from the Available section and move them to the Selected section. Because this is a heterogeneous database synchronization, only tables are synchronized—views, triggers, and stored procedures are not. Use object name mapping to rename objects in the destination instance. Add quotation marks to the target object Select Yes if any of the following conditions apply: the source database environment is case-sensitive with mixed-case names; table names start with non-letter characters or contain unsupported characters (allowed special characters: underscores _, number signs#, dollar signs$); or object names are reserved keywords in the destination database. After DTS synchronizes data, reference quoted objects using their quoted names.Rename databases and tables Use object name mapping to rename objects in the destination instance. Retry time for failed connections DTS retries failed connections for 720 minutes (12 hours) by default. Adjust based on your requirements. 
Step 3: Configure AnalyticDB for PostgreSQL table settings
Specify the table type, primary key column, and distribution key for each table to synchronize to the AnalyticDB for PostgreSQL instance. For more information about primary key columns and distribution columns, see Manage tables and Define table distribution.
Click Precheck in the lower-right corner. DTS runs a precheck before starting the synchronization task. If any items fail:
Click the icon next to the failed item to view details.
Fix the issue based on the details, then initiate a new precheck.
To skip an issue without fixing it, ignore the failed items and initiate a new precheck.
Close the Precheck dialog after the message The precheck is passed. appears. The synchronization task starts automatically.
Wait until initial synchronization completes and the task status changes to Synchronizing. Monitor the task status on the Synchronization Tasks page.
FAQ
Where do synchronized tables appear in the AnalyticDB for PostgreSQL instance?
During schema synchronization, DTS creates tables in the destination database using the same schema structure as the source database. For example, if you synchronize the customer and Student tables from the dbo schema of the dtstestdata database, you can find these tables in the dbo schema of the destination instance.
