AnalyticDB:Synchronize data from a self-managed SQL Server database on an ECS instance to a cloud-native data warehouse AnalyticDB for PostgreSQL instance

How it works

DTS reads the SQL Server transaction log to capture incremental changes after the initial full data synchronization. For this to work, the source database must run in full recovery mode — this ensures the transaction log retains changes long enough for DTS to read them. DTS also adds a heartbeat table named <source_table_name>_dts_mysql_heartbeat to the source database to track synchronization latency.

Prerequisites

Before you begin, make sure that:

• The SQL Server version is 2008, 2008 R2, 2012, 2014, 2016, 2017, or 2019

• Tables to be synchronized have primary keys or UNIQUE NOT NULL indexes

• The AnalyticDB for PostgreSQL instance has more available storage space than the total data size in the source SQL Server database

• If your SQL Server database is deployed in an Always On availability group, it uses synchronous-commit mode

Billing

Limits

Unsupported schema objects

DTS does not synchronize assemblies, Service Brokers, full-text indexes, full-text catalogs, distributed schemas, distributed functions, CLR stored procedures, CLR scalar-valued functions, CLR table-valued functions, internal tables, systems, or aggregate functions.

Unsupported data types

DTS does not synchronize columns of the following types: TIMESTAMP, CURSOR, ROWVERSION, HIERARCHYID, SQL\_VARIANT, SPATIAL GEOMETRY, SPATIAL GEOGRAPHY, and TABLE.

Other limits

• Tables with computed columns are not synchronized

• Transactional DDL operations are not migrated

Supported SQL operations

• DML: INSERT, UPDATE, and DELETE

• DDL: ADD COLUMN only

Required permissions

Permissions differ depending on whether you need incremental data synchronization.

Full synchronization only (schema + full data)

Full synchronization + incremental data synchronization

The same permission set applies. For the AnalyticDB for PostgreSQL account, the initial account of the instance already has all required permissions.

For instructions on granting permissions, see:

• SQL Server: CREATE USER and Manage users and permissions

• AnalyticDB for PostgreSQL: Create a database account and Manage users and permissions

Usage notes

• DTS uses read and write resources on both source and destination instances during initial full data synchronization, which increases their load. Run the synchronization during off-peak hours — ideally when the CPU utilization of both instances is below 30%.

• Do not frequently back up the source database during synchronization. Retain log files for more than three days. If log files are truncated before DTS reads them, DTS cannot retrieve the changes.

Prepare the source SQL Server database

Complete these steps before configuring the synchronization task. They ensure DTS can read the transaction log and capture incremental changes.

1. Set the recovery model to full. This enables the transaction log to retain changes that DTS needs for incremental synchronization. Run the following command, or use SQL Server Management Studio (SSMS). For SSMS instructions, see View or Change the Recovery Model of a Database.

   use master;
   GO
   ALTER DATABASE <database_name> SET RECOVERY FULL WITH ROLLBACK IMMEDIATE;
   GO

   Replace <database_name> with the name of the source database. For example:

   use master;
   GO
   ALTER DATABASE mytestdata SET RECOVERY FULL WITH ROLLBACK IMMEDIATE;
   GO

2. Create a full database backup. Skip this step if you already have one.

   Placeholder	Description	Example
   <database_name>	Name of the source database	mytestdata
   <backup_file_path>	Storage path and file name for the backup	D:\backup\dbdata.bak

   BACKUP DATABASE <database_name> TO DISK='<backup_file_path>';
   GO

   Example:

   BACKUP DATABASE mytestdata TO DISK='D:\backup\dbdata.bak';
   GO

3. Back up the transaction log.

   BACKUP LOG <database_name> TO DISK='<backup_file_path>' WITH init;
   GO

   Example:

   BACKUP LOG mytestdata TO DISK='D:\backup\dblog.bak' WITH init;
   GO

4. Create clustered indexes on the tables you want to synchronize. For instructions, see Create Clustered Indexes.

Configure the synchronization task

1. Purchase a DTS synchronization instance. On the buy page, set: For purchase instructions, see Purchase a DTS instance.

   • Source Instance: SQL Server

   • Destination Instance: AnalyticDB for PostgreSQL

   • Synchronization Topology: 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 resides.

5. Find the synchronization instance and click Configure Synchronization Channel in the Actions column.

6. Configure the source and destination instances.

   Section	Parameter	Description
   N/A	Synchronization Task Name	A name DTS generates automatically. Specify a descriptive name to make the task easy to identify. The name does not need to be unique.
   Source Instance Details	Instance Type	Select User-Created Database in ECS Instance. If you select a different instance type, prepare the required environment first. For details, see Preparation overview.
   	Instance Region	The source region you selected when purchasing the instance. Read-only.
   	ECS Instance ID	The ID of the ECS instance that hosts the source database.
   	Database Type	Fixed to SQLServer. Read-only.
   	Port Number	The service port of the source database. Default: 3306.
   	Database Account	The source database account. See Required permissions.
   	Database Password	The password for the database account.
   	Encryption	Select Non-encrypted or SSL-encrypted. This parameter is available only in regions in the Chinese mainland and the China (Hong Kong) region. Note Currently, the Connection Method setting is supported only in the Chinese mainland and China (Hong Kong) regions.
   Destination Instance Details	Instance Type	Fixed to AnalyticDB for PostgreSQL. Read-only.
   	Instance Region	The destination region you selected when purchasing the instance. Read-only.
   	Instance ID	The ID of the destination AnalyticDB for PostgreSQL instance.
   	Database Name	The name of the destination database.
   	Database Account	The destination database account. See Required permissions.
   	Database Password	The password for the database account.

   

7. Click Set Whitelist and Next. DTS automatically adds its server CIDR blocks to the ECS security group rules. If the source database runs on multiple ECS instances, manually add the DTS server CIDR blocks to each instance's security group rules. For the full list of DTS CIDR blocks, see Add the CIDR blocks of DTS servers.

   Warning

   Adding DTS server CIDR blocks to security group rules or IP address whitelists creates security risks. Before proceeding, take preventive measures, including: strengthening username and password security, limiting exposed ports, authenticating API calls, regularly auditing whitelist and security group rules, and connecting the database to DTS through Express Connect, VPN Gateway, or Smart Access Gateway.

8. Select the synchronization policy and objects.

   Setting	Description
   Initialize Synchronization	Initial Schema Synchronization, Initial Full Data Synchronization, and Initial Incremental Data Synchronization are selected by default. DTS synchronizes schemas and data before starting incremental replication.
   Processing Mode In Existed Target Table	Pre-check and Intercept (default): fails the precheck if the destination database already contains tables with the same names. Use the object name mapping feature to rename conflicting tables. See Rename an object to be synchronized. Ignore Errors and Proceed: skips the name-conflict check. If schemas match, DTS skips records with duplicate primary keys. If schemas differ, only some columns are synchronized or the task fails.
   Merge Multi Tables	Yes: merges multiple source tables with the same schema into a single destination table. This is useful in OLTP sharding scenarios where you want to consolidate sharded tables for analytics in AnalyticDB for PostgreSQL. When enabled, add a __dts_data_source column (TEXT type) to the destination table to identify the source of each record. DTS writes values in the format <instance_ID>:<database_name>.<schema_name>.<table_name> — for example, dts********:dtstestdata.testschema.customer1. Then use the object name mapping feature to rename each selected source table to the destination table name. No (default).
   Select the operation types	Select the DML and DDL operations to synchronize. All types are selected by default.
   Select the objects to be synchronized	Move tables from the Available section to the Selected section. In this heterogeneous synchronization scenario, only tables are synchronized — views, triggers, and stored procedures are excluded. Use the object name mapping feature to rename objects in the destination instance. See Rename an object to be synchronized.
   Add quotation marks to the target object	Select Yes if object names contain mixed-case characters, start with a non-letter character, or are reserved keywords in the destination database. When enabled, DTS encloses object names in single or double quotation marks during schema synchronization and incremental synchronization. After synchronization, reference these objects using quoted names in queries.
   Rename Databases and Tables	Use the object name mapping feature to rename synchronized objects. See Object name mapping.
   Retry Time for Failed Connections	If DTS loses the connection to the source or destination database, it retries for up to 720 minutes (12 hours) by default. If DTS reconnects within this window, it resumes the task. Otherwise, the task fails. Adjust this value based on your requirements. While DTS is retrying, you are charged for the instance.

   

9. Specify the table type, primary key column, and distribution key for each table to be synchronized to AnalyticDB for PostgreSQL. For information about primary key columns and distribution columns, see Manage tables and Define table distribution.

   

10. Click Precheck. DTS validates the configuration before starting the task. If any checks fail, click the icon next to the failed item to view details. Fix the issue and run the precheck again. If the issue is not critical, you can ignore the failed item and rerun the precheck.

11. After the precheck passes, close the Precheck dialog box. The synchronization task starts automatically.

12. On the Synchronization Tasks page, wait until the task status changes to Synchronizing.

    

What's next

• Monitor synchronization latency and task health in the DTS console.

• Review the AnalyticDB for PostgreSQL documentation to optimize query performance using distribution keys and table types: Define table distribution.