Data Transmission Service:Migrate data from an RDS SQL Server instance to an AnalyticDB for PostgreSQL instance

Source database limitations

• The server hosting the source database must have sufficient outbound bandwidth. Insufficient bandwidth reduces migration speed.

• Tables to be migrated must have PRIMARY KEY or UNIQUE constraints with all fields unique. Tables without these constraints may produce duplicate records in the destination.

• When migrating tables individually (rather than the entire database), a single task supports up to 1,000 tables. Exceeding this limit causes a request error. Either configure multiple tasks or migrate the entire database instead.

• A single task supports up to 10 databases. Migrating more than 10 databases in one task may reduce performance and stability.

• If the source is a read-only instance, DDL operations cannot be migrated.

Incremental migration requirements

To use incremental data migration, the source database must meet these requirements:

• Data logging is enabled, the backup mode is set to Full, and a full physical backup has been performed.

• Log retention period: If logs are unavailable, DTS cannot read them and the task fails. In some cases, data inconsistency or loss may occur. After full migration completes, you can reduce the retention period to more than 24 hours. The DTS Service Level Agreement (SLA) does not cover tasks that do not meet these log retention requirements.

  • Incremental migration only: more than 24 hours

  • Full + incremental migration: at least 7 days

• Do not clear logs before the task completes. DTS uses the fn_log function to read logs, and clearing them while the task is running causes the task to fail.

Prerequisites for CDC

If CDC needs to be enabled for the source tables, verify these conditions before starting the task. Failure to meet them causes the precheck to fail.

• The value of the srvname field in the sys.sysservers view matches the return value of the SERVERPROPERTY function.

• For self-managed SQL Server databases, the database owner must be the sa user. For ApsaraDB RDS for SQL Server databases, the database owner must be the sqlsa user.

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

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

• If the source runs SQL Server 2017 (Standard or Enterprise edition), update to a newer version to avoid known issues.

Data type limitations

The following data types are not migrated: CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, GEOGRAPHY.

Other limitations

• The destination table cannot be an append-optimized (AO) table.

• During schema migration and full data migration, do not execute DDL statements that change database or table schemas. Doing so causes the migration task to fail.

• If performing only full data migration (no incremental), do not write data to the source database while migration is in progress. Concurrent writes cause data inconsistency between the source and destination.

• Concurrent INSERT operations during full data migration cause table fragmentation in the destination. After full migration completes, the destination tablespace is larger than the source.

• If column mapping is used for non-full table migration, or if source and destination table schemas differ, columns in the source that have no corresponding column in the destination are lost.

• DTS attempts to resume failed tasks for up to seven days. Before switching workloads to the destination, stop or release any failed tasks, or use the REVOKE statement to revoke write permissions from the DTS accounts on the destination. Otherwise, a resumed task overwrites destination data with source data.

• If the task includes incremental migration, reindexing is not allowed. Running it may cause the task to fail and data loss.

• For FLOAT or DOUBLE columns, verify that the precision settings meet your requirements. DTS uses ROUND(COLUMN,PRECISION) to retrieve values: FLOAT defaults to 38 digits of precision, DOUBLE defaults to 308 digits.

• DTS does not migrate DDL operations related to the primary key of a table for which CDC is enabled.

• If the number of CDC-enabled tables to be migrated in a single migration task exceeds 1,000, the precheck fails.

Limitations by incremental synchronization mode

Incremental synchronization based on logs of source database (heap tables are not supported)

• Does not modify the source database settings.

• Does not support heap tables, tables without primary keys, compressed tables, or tables with computed columns. All tables must have clustered indexes that contain primary key columns. If your tables include any of these types, use hybrid log-based parsing mode instead.

• If the source runs SQL Server Web edition, this mode is required. Set SQL Server Incremental Synchronization Mode to Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) when configuring the task.

Log-based parsing for non-heap tables and CDC-based incremental synchronization for heap tables (hybrid log-based parsing)

• Supports heap tables, tables without primary keys, compressed tables, and tables with computed columns. Provides higher stability and a broader set of DDL statements.

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

• You cannot execute SELECT INTO or TRUNCATE on CDC-enabled tables. The triggers created by DTS cannot be manually deleted.

• Multiple ADD COLUMN or DROP COLUMN operations within 10 minutes are not allowed. For example, the following sequence within 10 minutes causes an 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 write rate for CDC-enabled tables to 1,000 records per second.

• Make sure that the CDC job in the source database runs as expected. If it fails, the DTS task also fails.

Polling and querying CDC instances for incremental synchronization

• Supported source databases: Amazon RDS SQL Server, Azure SQL Database, Google Cloud SQL for SQL Server.

• Uses the native CDC component of SQL Server. Incremental migration is more stable and consumes less network bandwidth.

• The DTS account used for the source database must have permission to enable CDC:

  • To enable database-level CDC: use an account assigned the sysadmin role.

  • To enable table-level CDC: use a privileged account.

  Note

  • A server administrator account in Microsoft Azure SQL Database has the required permissions. CDC can be enabled for all databases purchased in Azure SQL Database based on the vCore model. For databases based on the database transaction unit (DTU) model, CDC requires a service tier of S3 or greater.

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

• Incremental data migration delay is approximately 10 seconds.

• The number of tables to be migrated cannot exceed 1,000. Exceeding this limit causes the precheck to fail and may cause delays or instability.

• Do not execute DDL statements to add or remove columns more than twice within a minute. Exceeding this limit may cause the task to fail.

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

DTS account on source RDS instance

When 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 migration task is running. Doing so causes the task to fail. For more information, see System accounts.

Foreign key behavior

• DTS migrates foreign keys during schema migration.

• During full data migration and incremental data migration, DTS temporarily disables constraint checks and cascade operations on foreign keys at the session level. If you run cascade UPDATE or DELETE operations on the source during migration, data inconsistency may occur.

Step 1: Go to the Data Migration Tasks page

1. Log on to the Data Management (DMS) console.

2. In the top navigation bar, move the pointer over DTS.

3. Choose DTS (DTS) > Data Migration.

Step 2: Select region and create a task

1. From the drop-down list to the right of Data Migration Tasks, select the region where your data migration instance resides.

   Note

   In the new DTS console, select the region in the upper-left corner.

2. Click Create Task.

Step 3: Configure source and destination databases

On the Create Task page, configure the following parameters:

Step 4: Test connectivity

Click Test Connectivity and Proceed.

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 deployed in data centers or on third-party clouds, manually add the DTS server CIDR blocks to the database whitelist. See Add the CIDR blocks of DTS servers.

Step 5: Configure objects and migration settings

Configure the following parameters:

Parameter	Description
Migration Types	Select the migration types for your scenario: Schema Migration + Full Data Migration for one-time migration; Schema Migration + Full Data Migration + Incremental Data Migration for continuous sync during migration. If Incremental Data Migration is not selected, do not write data to the source database during migration to ensure data consistency.
SQL Server Incremental Synchronization Mode	Select the mode that matches your source database. See Limitations by incremental synchronization mode for details on each mode's requirements and constraints. This parameter is available only when Full Data Migration is selected.
DDL and DML Operations to Be Synchronized	The SQL operations to migrate at the instance level. See SQL operations supported for incremental migration. To select operations for a specific database or table, right-click an object in Selected Objects.
Processing Mode of Conflicting Tables	Precheck and Report Errors: fails the precheck if the destination has tables with the same names as source tables. To resolve conflicts, use object name mapping. Ignore Errors and Proceed: skips the check. During full migration, DTS skips conflicting records and keeps the existing destination data. During incremental migration, DTS overwrites conflicting records. Use with caution.
Capitalization of Object Names in Destination Instance	Controls the capitalization of database names, table names, and column names in the destination. Default is DTS default policy. See Specify the capitalization of object names in the destination instance.
Source Objects	Select the objects to migrate and click to add them to Selected Objects. In this heterogeneous migration scenario, only tables can be migrated. Views, triggers, and stored procedures are not migrated to the destination.
Selected Objects	To rename a migrated object, right-click it. See Map the name of a single object. To rename multiple objects at once, click Batch Edit. See Map multiple object names at a time. To filter rows using a WHERE condition, right-click a table and specify conditions. See Specify filter conditions.

Step 6: Configure advanced settings

Click Next: Advanced Settings and configure the following:

Data verification settings

To enable data verification after migration, see Configure a data verification task.

Advanced settings

Step 7 (optional): Configure database and table fields

Click Next: Configure Database and Table Fields to set the Type, Primary Key Column, and Distribution Key for tables being migrated to AnalyticDB for PostgreSQL.

Step 8: Save settings and run the precheck

Click Next: Save Task Settings and Precheck.

DTS runs a precheck before starting the migration task. The task starts only after the precheck passes.

• If any item fails, click View Details next to that item, resolve the issue, then click Precheck Again.

• If an alert is triggered:

  • If the alert cannot be ignored, fix the issue and rerun the precheck.

  • If the alert can be ignored, click Confirm Alert Details, then click Ignore in the dialog box, and click OK to confirm. Then click Precheck Again. Ignoring an alert may result in data inconsistency.

Step 9: Purchase the instance

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

On the Purchase Instance page, configure:

Step 10: Start the task

1. Read and accept Data Transmission Service (Pay-as-you-go) Service Terms by selecting the check box.

2. Click Buy and Start, then click OK in the dialog box.

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