Sync RDS SQL Server Data in Real Time via DTS - Data Transmission Service

Use Data Transmission Service (DTS) to set up continuous, one-way data synchronization between two ApsaraDB RDS for SQL Server instances.

Prerequisites

Before you begin, make sure you have:

Created both the source and destination RDS for SQL Server instances. For supported versions, see Synchronization solutions overview. To create an instance, see Quickly create and use an RDS for SQL Server instance
A destination instance with more storage space than the source instance

If your source is a self-managed SQL Server database, use the RDS for SQL Server backup feature instead of DTS if any of the following apply:

More than 10 databases need to be synchronized
Log backups run more than once per hour on a single database
DDL operations exceed 100 times per hour on a single database
Log volume for a single database exceeds 20 MB/s
More than 1,000 tables require Change Data Capture (CDC)
Source database logs contain heap tables, tables without primary keys, compressed tables, or tables with computed columns

For the backup-based approach, see Migrate data from a self-managed database to an ApsaraDB RDS instance.

To check whether problematic table types exist in your source database, run the SQL statements in How to view information about heap tables, tables without a primary key, compressed tables, tables with computed columns, and tables with sparse columns in SQL Server.

Billing

Synchronization type	Pricing
Schema synchronization and full data synchronization	Free
Incremental data synchronization	Charged. For details, see Billing overview.

Supported synchronization topologies

One-way one-to-one synchronization
One-way one-to-many synchronization
One-way cascade synchronization
One-way many-to-one synchronization

For details on each topology, see Data synchronization topologies.

Supported SQL operations

Operation type	Supported operations
DML	INSERT, UPDATE, DELETE. UPDATE statements that only modify large object fields are not supported.
DDL	CREATE TABLE; ALTER TABLE (ADD COLUMN and DROP COLUMN only); DROP TABLE; CREATE INDEX; DROP INDEX

The following DDL scenarios are not supported:

Transactional DDL (for example, a single statement that adds multiple columns, or a statement that combines DDL and DML operations)
DDL operations that use custom types
Online DDL operations
DDL operations that use reserved keywords as attribute names
DDL operations run through system stored procedures
TRUNCATE TABLE
Partitions and table definitions that contain functions

Choose an incremental synchronization mode

DTS offers three incremental synchronization modes for SQL Server. Choose the mode that fits your source database before you start configuring the task — the choice affects which tables DTS can synchronize and what operations it performs on the source database.

Mode	Best for	Key limitations
Hybrid log-based parsing (log-based parsing for non-heap tables, CDC for heap tables)	Sources with heap tables, tables without primary keys, compressed tables, or tables with computed columns. High DDL stability required.	DTS creates the `dts_cdc_sync_ddl` trigger, `dts_sync_progress` heartbeat table, and `dts_cdc_ddl_history` DDL storage table in the source database, and enables database-level CDC and partial table CDC. `SELECT INTO`, `TRUNCATE`, and `RENAME COLUMN` are blocked on CDC-enabled tables.
Log-based parsing (incremental synchronization based on source logs, heap tables not supported)	Sources without heap tables, tables without primary keys, compressed tables, or tables with computed columns. Non-intrusive to the source database.	Heap tables, tables without a primary key, compressed tables, tables with computed columns, and tables with sparse columns are not supported. Tables must have a clustered index that includes the primary key column.
CDC polling (polling and querying CDC instances)	Sources hosted on Amazon RDS for SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure SQL Server on Virtual Machine, or Google Cloud SQL for SQL Server. Uses the native SQL Server CDC component.	The source database account must have permission to enable CDC. Incremental synchronization has a latency of about 10 seconds. No more than 1,000 tables per task.

Version requirements for hybrid log-based parsing mode:

Enterprise or Enterprise Evaluation Edition: SQL Server 2012, 2014, 2016, 2019, or 2022.
Standard Edition: SQL Server 2016, 2019, or 2022.

If the source is a web-based RDS SQL Server instance, set SQL Server Incremental Synchronization Mode to Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported).

Limitations

Review these limitations before starting. Violating them can cause task failures, data inconsistency, or data loss — none of which are covered by the DTS Service-Level Agreement (SLA).

Source database requirements

Limitation	Consequence if violated	Mitigation
Tables must have a primary key or a UNIQUE constraint with unique field values	Duplicate records may appear in the destination database	Add a primary key or unique constraint before starting
A single synchronization task supports a maximum of 10 databases	Stability and performance issues	Split databases into multiple tasks
Table-level synchronization with column name mapping: table count in a single task must not exceed 5,000	An error is reported after task submission	Split tables into multiple tasks, or configure the task to synchronize the entire database
Memory-optimized tables cannot be synchronized	Synchronization fails for those tables	Exclude memory-optimized tables from the task
When synchronizing specific objects to the same destination database, objects with the same table name but different schema names cannot be selected together	Task configuration error	Use object name mapping to rename conflicting objects

Log requirements:

DTS uses the fn_log function to read source database logs. Do not clear source database logs prematurely — this can cause the task to fail.

Data logs must be enabled. Backup mode must be set to Full, with at least one successful full physical backup completed.
Incremental-only synchronization: retain data logs for more than 24 hours.
Full + incremental synchronization: retain data logs for at least 7 days. After full synchronization completes, you can reduce the retention period to more than 24 hours. If the retention period is too short, DTS cannot read logs, which may cause task failure, data inconsistency, or data loss.

CDC prerequisites (applies when CDC needs to be enabled for source tables; otherwise the precheck fails):

The srvname field in the sys.sysservers view must match the return value of the SERVERPROPERTY function.
Self-managed SQL Server: the database owner must be sa.
RDS for SQL Server: the database owner must be sqlsa.
Enterprise Edition: SQL Server 2008 or later.
Standard Edition: SQL Server 2016 SP1 or later.
SQL Server 2017 (Standard or Enterprise Edition): upgrade to a supported version before proceeding.

Additional source requirements:

If the source database is an RDS for SQL Server instance, ensure that the Transparent Data Encryption (TDE) feature is disabled. For steps, see Disable TDE.
If the source is a read-only instance, DDL operations cannot be synchronized.
If the source is an Azure SQL Database, a single synchronization task can synchronize only one database.
Do not use sp_rename to rename objects (such as stored procedures) before a schema synchronization task runs — this may produce unexpected results or cause the task to fail. Use ALTER instead.
In hybrid log-based parsing mode, do not run consecutive add-column or drop-column operations within a 10-minute interval. For example, the following sequence causes a task error:
```
ALTER TABLE test_table DROP COLUMN Flag;
ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');
```
During schema synchronization and full data synchronization, do not perform DDL operations that change the schema. This causes the synchronization task to fail.
Note
During full synchronization, DTS queries the source database and acquires metadata locks, which may block DDL operations on the source database.
Keep the READ_COMMITTED_SNAPSHOT parameter enabled in the source database during full data synchronization. Disabling it prevents shared locks from being cleared and may cause data inconsistency or instance failures.

Other limitations

The following data types cannot be synchronized: CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, and GEOGRAPHY.
If data cannot be written to a TIMESTAMP field in the destination, DTS does not support full or incremental synchronization for that field — this may cause data inconsistency or task failure.
Cross-version synchronization may have compatibility issues. Confirm compatibility before starting.
To synchronize triggers from the source database, the database account used for the task must have Owner permissions on the destination database.
DTS does not synchronize foreign keys. As a result, cascade operations in the source database are not replicated to the destination.

For hybrid log-based parsing mode:

DTS depends on the CDC component in the source database. Verify that the CDC job is running correctly — if it stops, the DTS task fails.
By default, CDC retains incremental data for 3 days. To adjust the retention period:
```
exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention = <time>;
```
<time> is in minutes. If daily incremental change statements for a single table exceed 10 million, set <time> to 1440.
DTS enables database-level CDC and table-level CDC in the source database during task initialization. The source database may be briefly locked during this process due to SQL Server kernel limitations.
Do not enable CDC for more than 1,000 tables in a single task. Exceeding this limit may cause latency or task instability.
The data change rate for CDC-enabled tables in the source database should not exceed 1,000 records per second (RPS).

For log-based parsing mode:

The data change rate for CDC-enabled tables in the source database should not exceed 1,000 records per second (RPS).

For CDC polling mode:

The source database account must have permission to enable CDC. Enabling database-level CDC requires the sysadmin role. Enabling table-level CDC requires a privileged account.
Note
The privileged account from the Azure SQL Database console meets these requirements. For the vCore purchasing model, all specifications support CDC. For the DTU purchasing model, the specification must be S3 or higher. For Amazon RDS for SQL Server, the privileged account supports enabling database-level CDC through stored procedures.
Do not synchronize more than 1,000 tables from a single source database to avoid latency or instability.
Do not run consecutive add-column or drop-column DDL operations (more than two DDL operations within one minute) — this may cause the task to fail.
Do not modify CDC instances in the source database — this may cause the task to fail or result in data loss.
CDC cannot be enabled for tables with clustered columnstore indexes.

Additional operational notes:

If a single field in a CDC-enabled table needs to store more than 64 KB of data, run the following command in advance:
```
exec sp_configure 'max text repl size', -1;
```
By default, a CDC job processes a maximum of 64 KB per field.
Disable any enabled triggers and foreign keys in the destination database before incremental synchronization begins. Otherwise, the task fails.
Do not write data to the destination database from any source other than DTS during synchronization. External writes — including online DDL operations through DMS — can cause data inconsistency or data loss.
Reindexing is not supported for synchronization instances. This can cause task failure or data loss.
Note
Primary key changes are not supported for tables with CDC enabled.
If the precheck fails because the number of CDC-enabled tables exceeds the configured maximum (default: 1,000), adjust the The maximum number of tables for which CDC is enabled that DTS supports setting before rerunning the precheck.
If multiple synchronization instances share the same SQL Server source database, their incremental data ingestion modules operate independently.
Initial full synchronization runs concurrent INSERT operations, which creates table fragmentation in the destination database. The destination tablespace will be larger than the source after full synchronization completes.
SQL Server is a commercial, closed-source database with a log format that can cause unpredictable behavior during incremental CDC and log parsing. Before using DTS for incremental synchronization from a SQL Server source in a production environment, run a thorough proof of concept (POC) that covers all business change types, schema adjustments, and peak-hour stress scenarios.
If the task fails, DTS technical support will attempt to recover it within 8 hours. During recovery, the task may be restarted or its parameters adjusted. Only DTS task parameters are modified — database parameters remain unchanged.
To modify synchronized objects, you cannot remove a database from the task.

Special cases

If the source instance is an RDS for SQL Server instance, DTS creates a rdsdt_dtsacct account in the source instance. Do not delete this account or change its password while the task is running — this will cause the task to fail. For details, see System accounts.

Create a synchronization task

Step 1: Go to the data synchronization page

Open the data synchronization task list using one of the following methods.

DTS console

Log on to the DTS console.DTS console
In the navigation pane, click Data Synchronization.
In the upper-left corner, select the region where the synchronization instance will reside.

DMS console

Note

Steps may vary depending on your DMS console mode and layout. For details, see Simple mode console and Customize the layout and style of the DMS console.

Log on to the DMS console.DMS console
In the top menu bar, choose Data + AI > DTS (DTS) > Data Synchronization.
To the right of Data Synchronization Tasks, select the region of the synchronization instance.

Step 2: Create a task and configure databases

Click Create Task to open the task configuration page.

Warning

After selecting source and destination instances, review the Limits displayed at the top of the page. Skipping this step may result in task failure or data inconsistency.

Configure the following settings:

General

Parameter	Description
Task Name	DTS generates a name automatically. Specify a descriptive name for easy identification. Names do not need to be unique.

Source Database

Parameter	Description
Select Existing Connection	Select a registered database instance from the drop-down list to auto-fill the connection details. If you haven't registered the instance or prefer not to use one, fill in the connection details manually. In the DMS console, this field is labeled Select a DMS database instance.
Database Type	Select SQL Server.
Connection Type	Select Cloud Instance.
Instance Region	Select the region of the source RDS for SQL Server instance.
Instance ID	Select the ID of the source RDS for SQL Server instance.
Database Account	Enter the database account. The account must have owner permission on the objects to be synchronized. An account with administrative permission meets this requirement.
Database Password	Enter the password for the database account.
Encryption	Select Non-encrypted if SSL encryption is not enabled on the source database. Select SSL-encrypted if SSL is enabled — DTS trusts the server-side certificate by default.

Destination Database

Parameter	Description
Select Existing Connection	Select a registered database instance from the drop-down list to auto-fill the connection details. If you haven't registered the instance or prefer not to use one, fill in the connection details manually. In the DMS console, this field is labeled Select a DMS database instance.
Database Type	Select SQL Server.
Connection Type	Select Cloud Instance.
Instance Region	Select the region of the destination RDS for SQL Server instance.
Instance ID	Select the ID of the destination RDS for SQL Server instance.
Database Account	Enter the database account. The account must have owner permission on the objects to be synchronized.
Database Password	Enter the password for the database account.
Encryption	Select Non-encrypted if SSL encryption is not enabled on the destination database. Select SSL-encrypted if SSL is enabled — DTS trusts the server-side certificate by default.

After filling in all fields, click Test Connectivity and Proceed at the bottom of the page.

Note

Add the CIDR blocks of DTS servers to the security settings (whitelists) of both the source and destination databases. For instructions, see Add the IP address whitelist of DTS servers. If the source or destination is a self-managed database, also click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

Step 3: Configure objects

On the Configure Objects page, specify what to synchronize.

Parameter	Description
Synchronization Types	DTS always selects Incremental Data Synchronization. By default, also select Schema Synchronization and Full Data Synchronization. After the precheck, DTS uses a full data snapshot of the selected source objects to initialize the destination database, establishing the baseline for incremental synchronization.
Method to Migrate Triggers in Source Database	Select how to handle triggers. If no triggers are involved, skip this setting. For details, see Configure a method to synchronize or migrate triggers. This setting is available only when Schema Synchronization is selected.
SQL Server Incremental Synchronization Mode	Select the mode based on your source database. See Choose an incremental synchronization mode for guidance.
The maximum number of tables for which CDC is enabled that DTS supports	Set the upper limit for CDC-enabled tables in this synchronization instance. The default is 1,000. Not available when the mode is set to Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported).
Processing Mode of Conflicting Tables	Precheck and Report Errors: DTS checks for tables with duplicate names in the destination database before starting. If duplicates are found, the precheck fails and the task does not start. If you cannot delete or rename the conflicting table, use object name mapping instead. Ignore Errors and Proceed: Skips the duplicate-name check. During full data synchronization, if a record in the destination has the same primary key or unique key as a source record, DTS retains the destination record. During incremental synchronization, DTS overwrites the destination record with the source record. If table schemas are inconsistent, initialization may fail. Use with caution.
Source Objects	In the Source Objects box, select the objects to synchronize, then click to move them to the Selected Objects box. You can select at the database, table, or column level. Selecting only tables or columns excludes other object types such as views, triggers, and stored procedures.
Selected Objects	To rename a single object in the destination, right-click it in the Selected Objects box. To rename multiple objects in bulk, click Batch Edit in the upper-right corner. For details, see Map object names. To filter data using a WHERE clause, right-click a table and configure the filter condition. For details, see Set filter conditions. Note that using object name mapping may cause dependent objects to fail synchronization.

Click Next: Advanced Settings.

Step 4: Configure advanced settings

Parameter	Description
Dedicated Cluster for Task Scheduling	By default, DTS uses a shared cluster. For higher stability, purchase a dedicated cluster. For details, see What is a DTS dedicated cluster?.
Retry Time for Failed Connections	If the connection to the source or destination database fails after the task starts, DTS retries immediately. Default: 720 minutes. Range: 10–1,440 minutes. Set 30 minutes or more for production tasks. If the connection is restored within this period, the task resumes automatically. If multiple DTS instances share a source or destination, DTS uses the shortest configured retry duration across those instances. DTS charges for runtime during connection retries.
Retry Time for Other Issues	If a non-connection issue occurs (such as a DDL or DML execution error), DTS retries immediately. Default: 10 minutes. Range: 1–1,440 minutes. This value must be less than Retry Time for Failed Connections.
Enable Throttling for Full Data Synchronization	Limit the full data synchronization rate to reduce load on source and destination databases. Set Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s). Available only when Full Data Synchronization is selected. You can also adjust this rate after the task starts.
Enable Throttling for Incremental Data Synchronization	Limit the incremental synchronization rate. Set RPS of Incremental Data Synchronization and Data synchronization speed for incremental synchronization (MB/s).
Environment Tag	Optionally tag the instance to identify its environment (for example, production or staging).
Configure ETL	Choose whether to enable extract, transform, and load (ETL). Select Yesalert notifications to enter data processing statements in the code editor. For details, see Configure ETL in a data migration or data synchronization task. Select No to disable ETL.
Monitoring and Alerting	Select Yes to set up alerts for task failures or excessive latency. Configure the alert threshold and notification contacts. For details, see Configure monitoring and alerting during task configuration.

Click Data Verification to optionally configure a data verification task. For details, see Configure data verification.

Step 5: Save settings and run the precheck

Click Next: Save Task Settings and Precheck.

Note

To preview the API parameters for this task configuration, hover over the button and click Preview OpenAPI parameters in the tooltip before proceeding.

DTS runs a precheck before the task starts. The task only starts if the precheck passes.

If the precheck fails, click View Details next to the failed item, fix the issue, and rerun the precheck.
If the precheck produces warnings:
- For non-ignorable warnings, click View Details, fix the issue, and rerun the precheck.
- For ignorable warnings, click Confirm Alert Details > Ignore > OK, then click Precheck Again to proceed. Ignoring precheck warnings may cause data inconsistency — proceed with caution.

Step 6: Purchase and start the instance

When the precheck Success Rate reaches 100%, click Next: Purchase Instance.
On the Purchase page, select the billing method and instance specifications.

Parameter	Description
Billing method	Subscription: Pay upfront for a fixed duration. Cost-effective for long-running, continuous tasks. Monthly options: 1–9 months. Yearly options: 1, 2, 3, or 5 years. Pay-as-you-go: Billed hourly based on actual usage. Suitable for short-term or test tasks — release the instance at any time to stop charges.
Resource Group Settings	The resource group for the instance. Defaults to default resource group. For details, see What is Resource Management?.
Instance Class	DTS offers multiple performance tiers that affect the synchronization rate. Select based on your workload. For details, see Data synchronization link specifications.

Read and select the Data Transmission Service (Pay-as-you-go) Service Terms checkbox.
Click Buy and Start, then click OK in the confirmation dialog.

Monitor task progress on the data synchronization page.

Check the CDC status

Use the following SQL statements to monitor or disable CDC in the source database. Replace the variable placeholders before running.

Check CDC status:

SELECT name, is_cdc_enabled FROM sys.databases WHERE name = '<your db name>';

Check CDC job status:

SELECT database_name(database_id), job_type
FROM [msdb].[dbo].[cdc_jobs]
WHERE database_id = DB_ID('<your db name>');

Verify CDC is functioning correctly:

Check disk space usage:

SELECT * FROM sys.dm_db_log_space_usage;

Check whether CDC is scanning data correctly (if not, the DTS task may retry or stop):

SELECT * FROM sys.dm_cdc_log_scan_sessions;

For SQL Server Agent status, see SQL Server Agent.

Disable CDC at the database level:

Drop the DTS trigger first, then disable CDC:

USE [<your db name>];

DROP TRIGGER [dts_cdc_sync_ddl] ON database;
-- Ignore this step if the trigger does not exist

EXECUTE [sys].[sp_cdc_disable_db];