Data Transmission Service:Synchronize data from a self-managed SQL Server database to an RDS PostgreSQL instance

Source database

• Tables must have a PRIMARY KEY or UNIQUE constraint with all fields unique. Without this, the destination database may contain duplicate records.

• When selecting table-level objects with destination editing (such as renaming tables or columns), a single task can synchronize up to 5,000 tables. For more than 5,000 tables, configure multiple tasks or synchronize at the database level.

• A single task can synchronize from up to 10 databases. For more than 10 databases, configure multiple tasks to avoid performance and stability issues.

• DTS uses the fn_log function to read source logs. Do not clear source logs before the task completes, or the task may fail.

• Log backup requirements:

  • Enable data logging. Set the backup mode to Full and perform a full physical backup.

  • For incremental synchronization only: retain logs for more than 24 hours.

  • For full and incremental synchronization combined: retain logs for at least 7 days. After full synchronization completes, reduce the retention period to more than 24 hours. If these requirements are not met, the DTS service level agreement (SLA) may not be guaranteed.

• For CDC to be enabled on source tables, the following conditions must all be met (otherwise the precheck fails):

  • The srvname field in sys.sysservers must match the return value of the SERVERPROPERTY function.

  • Self-managed SQL Server: the database owner must be the sa user.

  • ApsaraDB RDS for SQL Server: the database owner must be the sqlsa user.

  • Enterprise edition: SQL Server 2008 or later is required.

  • Standard edition: SQL Server 2016 SP1 or later is required.

  • SQL Server 2017 (Standard or Enterprise edition): upgrade to a newer version.

• Read-only source instances: DDL operations cannot be synchronized.

• Azure SQL Database source: a single task can synchronize data from only one database.

• ApsaraDB RDS for SQL Server source: disable Transparent Data Encryption (TDE) for task stability. See the Disable TDE section in "Configure TDE".

• During full data synchronization, enable the READ_COMMITTED_SNAPSHOT transaction isolation level in the source database to prevent shared locks from interfering with data writes. Without this setting, data inconsistency or instance failures may occur, and such issues are not covered by the DTS SLA.

• During schema synchronization and full data synchronization, do not execute DDL statements. Doing so causes the task to fail.

• ApsaraDB RDS for SQL Server Web edition: set SQL Server Incremental Synchronization Mode to Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) when configuring the task.

Other limits

• Unsupported data types: CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, GEOGRAPHY, and user-defined types created with CREATE TYPE. Because the source and destination databases are heterogeneous, data types do not map one-to-one. Evaluate the impact of type conversion on your workload. For the type mapping table, see Data type mappings for schema synchronization.

• Only tables can be selected as synchronization objects, and all selected tables must be in the same schema. Otherwise, data inconsistency or task failure may occur.

• If you use Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) mode, tables must have clustered indexes that contain primary key columns. The tables cannot be heap tables, tables without primary keys, compressed tables, or tables with computed columns.

• If the task involves incremental data synchronization, do not perform reindexing operations. Doing so may cause the task to fail and data loss to occur. DTS cannot synchronize DDL operations related to the primary key of a CDC-enabled table.

• If the number of tables for which CDC is enabled in a data synchronization task exceeds the maximum number of tables for which CDC is enabled that DTS supports, the precheck fails.

• If a single field written to a CDC-enabled table exceeds 64 KB, run the following command to remove the size limit:

  exec sp_configure 'max text repl size', -1;

• When modifying synchronization objects, databases cannot be removed.

• The incremental data collection modules of multiple synchronization tasks sharing the same source SQL Server database operate independently.

• If a DTS task fails, DTS support restores it within 8 hours. During restoration, the task may be restarted and task parameters may be modified. Database parameters are not modified.

Hybrid log-based parsing mode

• DTS uses the CDC component to synchronize incremental data. Make sure that the CDC job in the source database runs as expected. Otherwise, the DTS task fails.

• DTS creates the trigger dts_cdc_sync_ddl, the heartbeat table dts_sync_progress, and the DDL history table dts_cdc_ddl_history in the source database, and enables CDC for the source database and specific tables.

• Do not execute SELECT INTO, TRUNCATE, or RENAME COLUMN on CDC-enabled tables. Triggers created by DTS cannot be deleted manually.

• Do not add or remove columns more than once within 10 minutes. For example, running the following two statements within 10 minutes causes a task error:

  ALTER TABLE test_table DROP COLUMN Flag;
  ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');

• We recommend that you set the maximum number of records per second to 1,000 for the tables for which CDC is enabled in the source database.

• The CDC component stores incremental data for 3 days by default. To extend the retention period, run:

  exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>;

  <time> is in minutes. If the average daily volume of incremental data exceeds 10 million records, set <time> to 1440.

• A single task supports up to 1,000 CDC-enabled tables. Exceeding this limit may cause delays or instability.

• When the task pre-module enables database-level and table-level CDC in the source database, table-level locks lasting a few seconds occur due to SQL Server limitations.

CDC-based incremental synchronization (polling mode)

• The source database account must have permission to enable CDC. To enable database-level CDC, use an account with the sysadmin role. To enable table-level CDC, use a privileged account.

  • Azure SQL Database (vCore model): all databases support CDC. For DTU-model databases, a service tier of S3 or higher is required.

  • Amazon RDS for SQL Server: a privileged account has the required permissions; CDC can be enabled for stored procedures at the database level.

  • CDC cannot be enabled for clustered columnstore indexes.

• Incremental data synchronization has a latency of 10 seconds.

• A single task supports up to 1,000 tables. Exceeding this limit may cause delays or instability.

• Do not add or remove columns more than twice within one minute, or the task may fail.

• Do not modify CDC instances in the source database during synchronization. Doing so may cause the task to fail or data loss to occur.

• The CDC component stores incremental data for 3 days by default. To extend the retention period, run:

  exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>;

  <time> is in minutes. If the average daily volume of incremental data exceeds 10 million records, set <time> to 1440.

• When the task pre-module enables database-level and table-level CDC in the source database, table-level locks lasting a few seconds occur due to SQL Server limitations.

Foreign keys

• During schema synchronization, DTS synchronizes foreign keys from the source to the destination.

• During full data synchronization and incremental data synchronization, DTS temporarily disables foreign key constraint checks and cascade operations at the session level. If you run cascade updates or deletes on the source during synchronization, data inconsistency may occur.

Special cases

If the source is an ApsaraDB RDS for SQL Server instance, DTS automatically creates an account named rdsdt_dtsacct on the instance. Do not delete this account or change its password while the synchronization task is running. For more information, see System accounts.

Storage impact

After initial full data synchronization completes, the destination tables may use more storage than the source. Concurrent INSERT operations during full synchronization cause table fragmentation in the destination.

During initial full data synchronization, DTS uses some read and write resources of the source and destination databases, which may increase the database load. Evaluate the performance of the source and destination databases before you perform data synchronization. We recommend that you perform data synchronization during off-peak hours, for example, when the CPU load of the source and destination databases is below 30%.

Step 1: Open the Data Synchronization page

Use one of the following methods:

Step 2: Configure source and destination databases

Click Create Task. On the task configuration page, set the following parameters.

Task name

Source database

Destination database

Step 3: Test connectivity

At the bottom of the page, click Test Connectivity and Proceed. In the CIDR Blocks of DTS Servers dialog box, click Test Connectivity.

Make sure that DTS server CIDR blocks are added to the security settings of both the source and destination databases. See Add the CIDR blocks of DTS servers.

Step 4: Configure synchronization objects

In the Configure Objects step, set the following parameters.

Step 5: Configure advanced settings

Click Next: Advanced Settings.

Step 6: Configure data verification (optional)

Click Next Step: Data Verification. For details, see Configure a data verification task.

Step 7: Run the precheck

Click Next: Save Task Settings and Precheck.

• To preview the API parameters for configuring this task, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.

• The task must pass the precheck before it can start.

• If the precheck fails, click View Details next to each failed item, fix the issues, and rerun the precheck.

• If an alert is triggered: for alerts that cannot be ignored, fix the issue and rerun. For ignorable alerts, click Confirm Alert Details, then Ignore in the details dialog, click OK, and then click Precheck Again.

Step 8: Purchase the instance

1. Wait until Success Rate reaches 100%, then click Next: Purchase Instance.

2. On the purchase page, configure the following parameters.

   Parameter	Description
   Billing Method	Subscription: pay upfront for a period. More cost-effective for long-term use. Options: 1–9 months, 1 year, 2 years, 3 years, or 5 years. Pay-as-you-go: billed hourly. Suitable for short-term use. Release the instance when no longer needed to avoid ongoing charges.
   Resource Group Settings	Resource group for the synchronization instance. Default: default resource group. See What is Resource Management?
   Instance Class	Determines synchronization speed. Select based on workload requirements. See Instance classes of data synchronization instances.

3. Read and select Data Transmission Service (Pay-as-you-go) Service Terms.

4. Click Buy and Start. In the dialog box, click OK.

The task appears in the task list. Monitor its progress from there.