Migrate a self-managed SQL Server database to AnalyticDB for PostgreSQL - Data Transmission Service

Use Data Transmission Service (DTS) to migrate data from a self-managed SQL Server database to an AnalyticDB for PostgreSQL instance. DTS supports schema migration, full data migration, and incremental data migration, letting you cut over with minimal downtime.

Prerequisites

Before you begin, make sure that:

The migration task is configured in the new DTS console
The destination AnalyticDB for PostgreSQL instance exists. If not, create an instance
The destination instance has more storage space than the source SQL Server database uses
For supported SQL Server versions, see Migration solutions

If the source RDS instance meets any of the following conditions, we recommend that you split the migration task into multiple subtasks:

The source has more than 10 databases
A single database backs up its logs more than once per hour
A single database runs more than 100 DDL statements per hour
Logs are written at more than 20 MB/s for a single database
Change data capture (CDC) must be enabled for more than 1,000 tables

Migration types

DTS supports three migration types for this scenario. Select the types that match your requirements when you configure the task.

Migration type	What it does
Schema migration	Migrates schema definitions from the source to the destination. Supported objects: Schema, Table, View, Function, Procedure. Unsupported objects: Assemblies, Service Broker, Full-text Index, Full-text Catalog, Distributed Schema, Distributed Function, CLR Stored Procedure, CLR Scalar Function, CLR Table-valued Function, Internal Table, System, Aggregate Function.
Full data migration	Migrates all existing data in the specified objects from the source to the destination.
Incremental data migration	After full data migration completes, continuously captures and applies data changes from the source. This lets you cut over without stopping your applications.

Warning

This is a heterogeneous database migration. SQL Server and AnalyticDB for PostgreSQL data types are not mapped one-to-one. Assess the impact before you start. See Data type mapping between heterogeneous databases.

Billing

Migration type	Instance fee	Internet traffic fee
Schema migration + full data migration	Free	Charged when Access Method is set to Public IP Address. See Billing overview.
Incremental data migration	Charged. See Billing overview.	—

SQL operations supported for incremental migration

Type	Supported statements
DML	INSERT, UPDATE, DELETE
DDL	CREATE TABLE; ALTER TABLE (ADD COLUMN and DROP COLUMN only); DROP TABLE; CREATE INDEX; DROP INDEX

DML notes:

If an UPDATE touches only large fields, DTS skips that operation.
UPDATE statements are converted to REPLACE INTO on the destination AnalyticDB for PostgreSQL instance. If the primary key is updated, DTS converts the statement to DELETE followed by INSERT.

DDL not supported:

DDL with custom data types
Transactional DDL (adding multiple columns in one statement, or mixing DDL and DML in one statement)
Online DDL
DDL using reserved keywords as property names
DDL executed by system stored procedures
TRUNCATE TABLE
Partitions or table definitions that contain functions

Database account permissions

Database	Schema migration	Full migration	Incremental migration
Self-managed SQL Server	SELECT	SELECT	sysadmin
AnalyticDB for PostgreSQL	LOGIN; SELECT, CREATE, INSERT, UPDATE, DELETE on destination tables; CONNECT and CREATE on the database; CREATE on the schema; COPY	Same as schema migration	Same as schema migration

For the AnalyticDB for PostgreSQL instance, you can also use the initial account.

To create accounts and grant permissions:

SQL Server: see CREATE USER and Manage user permissions.
AnalyticDB for PostgreSQL: see Create and manage users and Manage user permissions.

Limitations

General limits

The server that hosts the source database must have sufficient outbound bandwidth. Otherwise, the data migration speed is affected.
Tables to migrate must have primary keys or UNIQUE constraints with unique fields. Otherwise, duplicate records may appear in the destination.
A single migration task supports a maximum of 10 databases. Split databases across multiple tasks if you exceed this limit.
If you migrate table-level objects with name editing (such as column mapping), a single task supports a maximum of 1,000 tables. If you exceed this limit, either split the tables across multiple tasks or migrate the entire database.
When migrating specific objects (not an entire database), you cannot migrate tables that share a name but have different schema names to the same destination database.
Append-optimized (AO) tables are not supported as destination tables.
If a source table has a primary key, the destination primary key column must match the source. If a source table has no primary key, the destination primary key column must match the distribution key.
The unique key of the destination table (including the primary key column) must contain all columns of the distribution key.
Data types that cannot be migrated: CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, GEOGRAPHY, and user-defined types created with CREATE TYPE.
Object types that cannot be migrated: INDEX, VIEW, PROCEDURE, FUNCTION, TRIGGER, FK, FULL_TEXT_INDEX, DATATYPE, DEFAULT, SYNONYM, CATALOG, PLAN_GUIDE, DEFAULT_CONSTRAINT, UK, CK, SEQUENCE.
DTS migrates foreign keys during schema migration, but temporarily disables constraint checks and foreign key cascade operations at the session level during full and incremental migration. If cascade updates or deletes occur in the source while the task runs, data inconsistency may occur.
If you use column mapping for a non-full table migration, or if the source and destination schemas are inconsistent, data in columns that exist in the source but not in the destination is lost.
FLOAT values are migrated with a precision of 38, and DOUBLE values with a precision of 308, using ROUND(COLUMN, PRECISION). Verify that this meets your requirements before you start.
Full data migration uses concurrent INSERT operations, which can cause table fragmentation. After full migration completes, the destination table storage is larger than the source.
Schedule migration during off-peak hours. Full data migration increases read and write load on both the source and destination.
DTS tries to resume a failed task for up to 7 days. Before switching your applications to the destination, end or release the task, or revoke the write permissions of the DTS account on the destination. This prevents source data from overwriting the destination after an automatic resume.

Limits for incremental migration

Log requirements:

The backup mode must be set to Full, and a full physical backup must have completed.
For incremental-only tasks, source data logs must be retained for more than 24 hours. For tasks that include both full and incremental migration, logs must be retained for at least 7 days. Issues caused by insufficient log retention are not covered by the DTS service-level agreement (SLA).
DTS uses the fn_log function to read source logs. Do not clear source logs prematurely.
During full data migration, make sure that the READ_COMMITTED_SNAPSHOT transaction isolation mode is enabled for the source database. This prevents shared locks from affecting data writes. Exceptions caused by this issue are not covered by the DTS SLA.

CDC requirements (for source tables to pass precheck):

The srvname field in the sys.sysservers view must match the return value of the SERVERPROPERTY function.
For self-managed SQL Server instances, the database owner must be sa. For RDS for SQL Server instances, 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 later version.

Other incremental migration limits:

Do not perform DDL operations during initial schema synchronization and full data migration.
If running full migration only, do not write new data to the source. To ensure real-time consistency, select all three migration types: Schema Migration, Full Data Migration, and Incremental Data Migration.
Read-only source databases cannot migrate DDL operations.
If the source is an Azure SQL Database, a single DTS instance can migrate only one database.
If the source is an RDS for SQL Server instance with incremental migration, disable transparent data encryption (TDE) to ensure task stability. See Disable TDE.
Use the ALTER command to rename objects before the initial schema synchronization task runs. Using sp_rename may cause the task to fail or produce unexpected results.
If a task includes incremental migration, do not reindex. Reindexing may cause task failure or data loss. You also cannot change the primary keys of tables with CDC enabled.
If a single field in a CDC-enabled table requires writes larger than 64 KB, run exec sp_configure 'max text repl size', -1; on the source database before starting the task.
If multiple DTS instances use the same SQL Server database as the source, their incremental data ingestion modules operate independently.
If the task fails, DTS technical support will attempt recovery within 8 hours. During recovery, task parameters may be adjusted (database parameters remain unchanged). See Modify instance parameters.
SQL Server CDC and log parsing has known format-specific limitations. Before enabling incremental migration in production, run a comprehensive proof of concept (POC) test that covers all business change types, schema changes, and peak-hour load tests.

Limits specific to Web Edition RDS for SQL Server

Set SQL Server Incremental Synchronization Mode to Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported).

Limits for RDS for SQL Server sources

DTS creates an rdsdt_dtsacct account in the source instance. Do not delete this account or change its password while the task is running. See System accounts.

Limits by synchronization mode

The incremental synchronization mode you select determines which source table types are supported and what restrictions apply.

Mode	Supported table types	Intrusive to source	Key restrictions
Hybrid Log-based Parsing (log-based for non-heap tables, CDC-based for heap tables)	Heap tables, tables without primary keys, compressed tables, tables with computed columns	Yes — creates `dts_cdc_sync_ddl` trigger, `dts_sync_progress` heartbeat table, `dts_cdc_ddl_history` DDL storage table; enables database-level CDC	Cannot run `SELECT INTO`, `TRUNCATE`, or `RENAME COLUMN` on CDC-enabled tables. Cannot manually delete DTS-created triggers. Cannot add or remove columns consecutively within less than 10 minutes. Recommended CDC data change rate: 1,000 records/second or fewer.
Log-based parsing (heap tables not supported)	Tables with a clustered index containing primary key columns	No — non-intrusive	Does not support heap tables, tables without primary keys, compressed tables, tables with computed columns, or tables with sparse columns. See How to view these table types.
CDC polling (polling and querying CDC instances)	Amazon RDS for SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure SQL Server on Virtual Machine, Google Cloud SQL for SQL Server	Yes — requires CDC-enable permissions on the source account	Approximately 10-second incremental latency. Migrate no more than 1,000 tables to avoid latency and instability. Cannot perform more than two DDL operations to add or remove columns within one minute. Cannot change the CDC instance of the source database.

CDC data retention (applies to Hybrid Log-based Parsing and CDC polling modes):

By default, CDC stores 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 a single table exceeds 10 million incremental change statements per day, set <time> to 1440.

Keep the number of CDC-enabled tables per task at or below 1,000 to avoid latency and instability. The prerequisite module briefly locks the source database when enabling CDC, due to SQL Server kernel limitations.

Preparations for incremental migration

If the migration task includes incremental migration, configure transaction log settings on the source before creating the task.

Important

If migrating multiple databases, repeat these steps for each database. Skipping this for any database may cause data inconsistency.

Step 1: Set the database recovery model to full.

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

Replace <database_name> with the name of the database to migrate. Example:

ALTER DATABASE mytestdata SET RECOVERY FULL WITH ROLLBACK IMMEDIATE;

Step 2: Perform a logical backup of the database (skip if a logical backup already exists).

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

Replace <backup_file_path> with the path and file name of the backup file. Example:

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

Step 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;

Create a migration task

Step 1: Go to Data Migration

From the DTS console

Log on to the DTS console.Data Transmission Service (DTS) console
In the left navigation pane, click Data Migration.
In the upper-left corner, select the region where the migration instance will be located.

From the DMS console

DMS console operations vary by mode and layout. See Simple mode console and Customize the layout and style of the DMS console.

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

Step 2: Configure source and destination databases

Click Create Task, then configure the connection parameters.

Warning

Read the limits displayed at the top of the page after you select the source and destination instances. Skipping this may cause task failure or data inconsistency.

Source database

Parameter	Value
Database Type	SQL Server
Connection Type	Self-managed Database on ECS
Instance Region	Region of the self-managed SQL Server instance
ECS instance ID	ECS instance ID of the SQL Server host
Port	SQL Server service port. Default: 1433
Database Account	See Database account permissions
Database Password	Password for the database account
Encryption	Non-encrypted if SSL is disabled; SSL-encrypted if SSL is enabled (DTS trusts the server certificate by default)

For connection type requirements, see Preparations overview.

Destination database

Parameter	Value
Database Type	AnalyticDB PostgreSQL
Connection Type	Cloud instance
Instance Region	Region of the AnalyticDB for PostgreSQL instance
Instance ID	Instance ID of the AnalyticDB for PostgreSQL instance
Database name	Destination database name
Database Account	See Database account permissions
Database Password	Password for the database account

Step 3: Test connectivity

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

Add the DTS server IP address ranges to the security settings of the source and destination databases. See Add DTS server IP addresses to a whitelist.

Step 4: Configure migration objects

On the Configure Objects page, set the following parameters.

Migration Types

Scenario	What to select
Full migration only	Schema Migration and Full Data Migration
Zero-downtime migration	Schema Migration, Full Data Migration, and Incremental Data Migration

If you skip Schema Migration, create the target tables manually before migration starts, or use object name mapping in Selected Objects.

If you skip Incremental Data Migration, do not write to the source during migration.

Processing Mode for Existing Destination Tables

Option	Behavior
Precheck and Report Errors	Fails the precheck if tables with the same names exist in the destination. Recommended for most cases. If a conflicting table exists, rename it. See Object name mapping.
Ignore Errors and Proceed	Skips the same-name check. During full migration, existing destination records with the same primary key are kept. During incremental migration, source records overwrite destination records. If schemas are inconsistent, only some columns may migrate, or migration may fail.

SQL Server incremental synchronization mode (visible only when Incremental Data Migration is selected)

Select the mode based on your source table types. See Limits by synchronization mode for a full comparison.

Log-based Parsing for Non-heap Tables and CDC-based Incremental Synchronization for Heap Tables (Hybrid Log-based Parsing) — use this when the source contains heap tables, tables without primary keys, compressed tables, or tables with computed columns.
Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) — use this for a non-intrusive option when the source has no heap tables.
Polling and querying CDC instances for incremental synchronization — use this when the source is 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.

Other object configuration parameters

Parameter	Description
The maximum number of tables for which CDC is enabled that DTS supports.	Default: 1,000. Adjust based on your workload. Not available in log-based parsing mode.
Select DDL and DML to Sync at the Instance Level	Select SQL operations to replicate. To set per-database or per-table operations, right-click the object in Selected Objects.
Storage Engine Type	Default: Beam. Available when the destination AnalyticDB for PostgreSQL kernel is v7.0.6.6 or later and Schema Migration is selected.
Source Objects	Click the objects to migrate, then click the right arrow to move them to Selected Objects. Only tables can be migrated in this heterogeneous scenario.
Selected Objects	To rename an object, right-click it. See Individual table column mapping. To rename multiple objects, click Batch Edit. See Map multiple object names at a time. To filter rows, right-click the table and set a WHERE condition. See Set filter conditions.

Object name mapping may cause dependent objects to fail migration.

Step 5: Configure advanced settings

Click Next: Advanced Settings and configure the following.

Parameter	Default	Description
Dedicated Cluster for Task Scheduling	Shared cluster	For more stable tasks, use a dedicated cluster.
Retry Time for Failed Connections	720 minutes (range: 10–1440)	Set to at least 30 minutes. DTS automatically resumes the task if it reconnects within this period. Billing continues during retry.
Retry Time for Other Issues	10 minutes (range: 1–1440)	Set to at least 10 minutes. Must be less than Retry Time for Failed Connections.
Enable Throttling for Full Data Migration	Disabled	Limit QPS to the source, RPS, and migration speed (MB/s) to reduce database load. Can also be adjusted after the task starts.
Enable Throttling for Incremental Data Migration	Disabled	Limit RPS and migration speed (MB/s). Can also be adjusted after the task starts.
Configure ETL	No	Enable to transform data during migration. See Configure ETL in a data migration or data synchronization task.
Monitoring and Alerting	No	Enable to receive alerts when migration fails or latency exceeds a threshold. Configure an alert thresholdalert notifications.

Step 6: Configure data validation

Click Next: Data Validation to set up a data validation task. See Configure data validation.

Step 7: Configure database and table fields (optional)

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

This step appears only when Schema Migration is selected. Set Definition Status to All to view and edit all tables.

The Primary Key Column can be a composite key. The Distribution Key must be a subset of the Primary Key Column. See Manage data tables and Define table distribution.

Step 8: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the API parameters for this configuration, hover over the button and click Preview OpenAPI parameters.

The task starts only after the precheck passes.

If the precheck fails, click View Details next to the failed item, fix the issue, then click Precheck Again.

If a warning appears: for items that cannot be ignored, fix the issue and recheck. For items that can be ignored, click Confirm Alert Details > Ignore > OK > Precheck Again. Ignoring warnings may cause data inconsistency.

Step 9: Purchase the migration instance

When Success Rate reaches 100%, click Next: Purchase Instance.
On the Purchase page, select an Instance Class. The instance class determines migration speed. See Data migration link specifications.
Select the resource group under Resource Group Settings (default: default resource group).
Read and accept Data Transmission Service (Pay-as-you-go) Service Terms.
Click Buy and Start, then click OK in the confirmation dialog box.

Track progress on the Data Migration Tasks list page.

Full-only tasks stop automatically when complete. The task Status changes to Completed.

Tasks with incremental migration run continuously. The task Status remains Running.

Data Transmission Service:Migrate a Self-managed SQL Server Database to AnalyticDB for PostgreSQL