Data Transmission Service:Migrate a self-managed SQL Server database to RDS for SQL Server

General

• DTS does not migrate foreign keys. Cascade and delete operations in the source database are not replicated to the destination.

• Tables must have primary keys or UNIQUE constraints with unique fields. Without these, duplicate data may appear in the destination.

• A single migration task supports a maximum of 1,000 tables when migrating table-level objects with column or name mapping. Split larger sets into multiple tasks, or migrate the entire database without table-level mapping.

• A single migration task supports a maximum of 10 databases. Split larger sets into multiple tasks to avoid stability and performance issues.

• When migrating specific objects (not an entire database), tables with the same name but different schema names cannot be migrated to the same destination database.

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

• If the source database is an Azure SQL Database, one 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) first. See Disable TDE.

• Do not use sp_rename to rename objects before the initial schema synchronization task runs. Use the ALTER command instead.

Incremental migration requirements

For incremental migration, the source database must meet these log requirements:

• Backup mode must be set to Full. A full physical backup must have already been performed.

• Log retention period: at least 24 hours for incremental-only tasks; at least 7 days for tasks that include both full and incremental migration. You can extend the retention period after full migration completes. Failures caused by insufficient log retention are not covered by the DTS Service-Level Agreement (SLA).

• DTS uses the fn_log function to obtain source database logs. This function has performance bottlenecks. Do not clear the source database logs too early. Otherwise, the DTS task may fail.

• During full data migration, enable the READ_COMMITTED_SNAPSHOT transaction processing mode in the source database to prevent shared locks from affecting data writes. Exceptions caused by this issue are not covered by the DTS SLA.

For CDC enablement in the source database, the following conditions must all be met:

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

• Database owner must be sa (self-managed) or sqlsa (RDS for SQL Server)

• Enterprise Edition: SQL Server 2008 or later

• Standard Edition: SQL Server 2016 SP1 or later

• SQL Server 2017 (Standard or Enterprise Edition): upgrade the database version

Additional restrictions during migration:

• During initial schema synchronization and full data migration, do not perform DDL operations that change database or table schemas.

• For full-migration-only tasks, do not write to the source database during migration. To avoid this constraint, include incremental data migration.

Unsupported data types

The following data types cannot be migrated: CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, GEOGRAPHY.

If data cannot be written to a TIMESTAMP column in the destination database, DTS does not support full or incremental migration for that table — this may cause data inconsistency or task failures.

DTS reads FLOAT and DOUBLE values using ROUND(COLUMN, PRECISION). Without a specified precision, FLOAT migrates at precision 38 and DOUBLE at precision 308. Verify this meets your business requirements before starting.

SQL operations supported for incremental migration

DDL limitations:

• Transactional DDL is not supported — for example, adding multiple columns in a single statement, or mixing DDL and DML in one statement

• DDL that uses user-defined types is not supported

• Online DDL is not supported

• DDL that uses reserved keywords as property names is not supported

• DDL executed by system stored procedures is not supported

• TRUNCATE TABLE is not supported

• Partitions and table definitions containing functions are not supported

Incremental synchronization mode limitations

DTS offers three incremental synchronization modes. The table below summarizes which mode handles which table types — choose based on your source database before configuring the task.

Hybrid log-based parsing — additional limits:

• Cannot consecutively add or remove columns within less than 10 minutes. 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('');

• Keep the CDC job running in the source database. If the CDC job stops, the DTS task fails.

• The default incremental data retention period for the CDC component is 3 days. Adjust it with:

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

  Where <time> is in minutes. If a single table exceeds 10 million incremental change SQL statements per day, set <time> to 1440.

• Keep the data change rate for CDC-enabled tables at or below 1,000 records per second (RPS).

• Enable CDC for no more than 1,000 tables in a single task to avoid latency and instability.

• During task initialization, the source database may be briefly locked while CDC is being enabled — this is a SQL Server kernel behavior.

Log-based parsing — additional limits:

• Tables must have a clustered index containing primary key columns.

• Web Edition RDS for SQL Server instances must use this mode.

CDC polling mode — additional limits:

• The source database account must have permissions to enable CDC. Enabling database-level CDC requires the sysadmin role; enabling table-level CDC requires a privileged account.

  • For Azure SQL Database: the privileged account (server administrator) provided by the console meets the requirements. For vCore-based databases, all instance types support CDC. For DTU-based databases, only instance types of S3 and later support CDC.

  • For Amazon RDS for SQL Server: the privileged account meets the requirements and can be used to enable database-level CDC for stored procedures.

• Clustered columnstore index tables do not support CDC.

• Incremental migration latency is approximately 10 seconds.

• Migrate no more than 1,000 tables per task to avoid latency and instability.

• Cannot consecutively add or remove columns. Performing more than two such DDL operations within one minute may cause the task to fail.

• Do not change the CDC instance of the source database — this may cause task failure or data loss.

• Adjust the CDC retention period using the same command as hybrid log-based parsing mode (see above).

• During task initialization, the source database may be briefly locked while CDC is being enabled.

Cross-version migration:

Check compatibility in advance when migrating across different SQL Server versions.

Multiple DTS instances sharing the same source:

If multiple DTS instances use the same SQL Server database as the source, their incremental data ingestion modules run independently.

Task failure recovery:

DTS technical support will attempt to recover a failed task within 8 hours. During recovery, task parameters may be adjusted — only DTS task parameters are changed, not database parameters. DTS automatically retries failed tasks for up to 7 days. Before switching to the destination instance, end or release the migration task, or revoke write permissions from the DTS account using the revoke command. This prevents the source data from overwriting the destination after an automatic task resume.

SQL Server log format notice:

SQL Server is a commercial closed-source database. Due to the proprietary log format, CDC and log parsing may encounter unpredictable issues. Before enabling incremental migration in production, run a comprehensive proof of concept (POC) test that covers all business change types, table schema changes, and peak-hour load. Keep your production business logic consistent with what was tested.

Step 1: Set the recovery model to full

Run the following command in the source database:

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:

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

Step 2: Back up the database

Skip this step if a logical backup has already been performed.

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

Example:

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

Step 3: Back up the transaction log

BACKUP LOG <database_name> to DISK='<physical_backup_device_name>' WITH init;
GO

Example:

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

Step 1: Open the Data Migration page

Step 2: Configure source and destination databases

Click Create Task, then configure the source and destination databases.

Task Name

DTS generates a task name automatically. Specify a descriptive name for easy identification. The name does not need to be unique.

Source database (self-managed SQL Server)

When using a self-managed database, additional preparations may be required. See Preparations overview.

In the DMS console, Select Existing Connection is labeled Select a DMS database instance.

Destination database (RDS for SQL Server)

Step 3: Test connectivity and configure objects

1. 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 both source and destination databases before testing. See Add DTS server IP addresses to a whitelist.

2. On the Configure Objects page, configure the following settings:

Migration types

Without Schema Migration, create the target databases and tables manually before starting the task, or use object name mapping in Selected Objects.

Without Incremental Data Migration, do not write to the source database during migration.

Incremental sync mode

This setting appears only when Incremental Data Migration is selected. The three modes differ in which underlying mechanism they use to capture changes:

• Hybrid log-based parsing: Uses log parsing for tables with clustered indexes, and CDC for heap tables and tables without primary keys. Choose this when your source has heap tables, tables without primary keys, compressed tables, or computed columns.

• Log-based parsing (heap tables not supported): Uses only log parsing. Choose this to minimize impact on the source database and when your source has no heap tables or tables without primary keys.

• CDC polling mode: Uses the native SQL Server CDC component. Choose this when migrating from cloud-hosted SQL Server (Amazon RDS, Azure SQL Database, Azure SQL Managed Instance, Google Cloud SQL for SQL Server).

Other object settings

Step 4: Configure advanced settings

Click Next: Advanced Settings and configure the following:

Step 5: Configure data validation

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

Step 6: Run the precheck

Click Next: Save Task Settings and Precheck.

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

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

• If the precheck fails, click View Details next to the failed item, fix the issue, and run the precheck again.

• If a warning appears for an item that cannot be ignored, fix the issue and rerun. For ignorable warnings, click Confirm Alert Details > Ignore > OK > Precheck Again to proceed. Ignored warnings may cause data inconsistency.

Step 7: Purchase the instance and start the task

1. When Success Rate reaches 100%, click Next: Purchase Instance.

2. Select the instance class for the migration task.

   Setting	Description
   Resource Group Settings	Resource group for the instance. Default: default resource group. See What is Resource Management?
   Instance Class	Affects migration speed. See Data migration link specifications.

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

4. Click Buy and Start, then click OK in the confirmation dialog.