|
Type
|
Description
|
|
Source database limits
|
-
The source database server must have sufficient outbound bandwidth. Insufficient bandwidth reduces data migration speed.
-
The tables to be migrated must have a primary key or unique constraint, and the columns in the key or constraint must be unique. Otherwise, data duplication may occur in the destination database.
-
If you select specific tables for migration and need to edit them (for example, map table or column names), a single migration task can migrate a maximum of 1,000 tables. If you exceed this limit, the task fails upon submission. Split the tables into multiple tasks or configure a task to migrate the entire database.
-
A single migration task supports a maximum of 10 databases. Exceeding this limit may cause stability and performance issues. Split the migration into multiple tasks if needed.
-
If you configure a task to migrate specific objects instead of an entire database, you cannot migrate objects that have the same table name but different schema names to the same destination database within a single task.
-
If you perform incremental data migration, the transaction logs must meet the following requirements:
-
Transaction logging must be enabled, the backup mode must be set to Full, and a full physical backup must have been successfully performed.
-
For incremental-only data migration, Data Transmission Service (DTS) requires retaining source database transaction logs for more than 24 hours. If a task includes both full and incremental data migration, retain logs for at least seven days. After full data migration is complete, you can reduce the retention period to more than 24 hours. Otherwise, the DTS task may fail due to unavailable transaction logs. In extreme cases, data inconsistency or loss may occur. Issues caused by insufficient log retention are not covered by the DTS Service Level Agreement (SLA).
-
If you enable CDC (change data capture) for the source tables to be migrated, they must meet the following conditions. Otherwise, the precheck will fail.
-
The value of the srvname field in the sys.sysservers view must be the same as the return value of the SERVERPROPERTY function.
-
If the source database is a self-managed SQL Server, the database owner must be sa. If the source database is an ApsaraDB RDS for SQL Server instance, the database owner must be sqlsa.
-
If the source database is Enterprise Edition, it must be SQL Server 2008 or later.
-
If the source database is Standard Edition, it must be SQL Server 2016 SP1 or later.
-
If the source database is SQL Server 2017 (Standard or Enterprise Edition), we recommend that you upgrade it to a later version.
-
DTS uses the fn_log function to obtain logs from the source database. This function has performance bottlenecks, so do not clear source database logs prematurely. Otherwise, the DTS task may fail.
-
Limits on operations in the source database:
-
During the schema migration and full data migration phases, do not perform DDL operations that change the database or table structure. Otherwise, the data migration task will fail.
-
If you perform only full data migration, do not write new data to the source instance. Otherwise, data in the source and destination databases becomes inconsistent. To ensure real-time data consistency, select Schema Migration, Full Data Migration, and Incremental Data Migration.
-
If the source database is a read-only instance, DTS does not migrate DDL operations.
-
If the source database is an Azure SQL Database, a migration instance can migrate only one database.
-
If the source database is an ApsaraDB RDS for SQL Server instance and the migration task includes incremental data migration, ensure that Transparent Data Encryption (TDE) is disabled for task stability. For more information, see Disable TDE.
-
If you use the sp_rename command to rename objects such as stored procedures before the schema migration task runs, the task may not work as expected or may fail.
Note
Use the ALTER command to rename database objects.
-
In hybrid log-based parsing mode, the source database does not support consecutive column add or drop operations with an interval of less than 10 minutes. For example, if you run the following SQL statements consecutively, the task will fail. ALTER TABLE test_table DROP COLUMN Flag;
ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');
-
If the source database is an ApsaraDB RDS for SQL Server instance that runs the Web edition, set SQL Server Incremental Synchronization Mode to Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) when you configure the task.
-
During full data migration, enable the READ_COMMITTED_SNAPSHOT parameter for the source database to prevent shared lock contention from affecting data writes. Otherwise, data inconsistency or task failure may occur. Issues caused by not enabling this parameter are not covered by the DTS SLA.
|
|
Other limits
|
-
Migration is supported for basic data types only, but not for data of the CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, or GEOGRAPHY types, or for user-defined types that are created by using the CREATE TYPE command.
-
DTS does not support migrating the following database objects: INDEX, VIEW, PROCEDURE, FUNCTION, TRIGGER, FK, FULL_TEXT_INDEX, DATATYPE, DEFAULT, SYNONYM, CATALOG, PLAN_GUIDE, DEFAULT_CONSTRAINT, UK, CK, and SEQUENCE.
-
When you select migration objects, the smallest supported unit is a table. You can modify column mappings. If you use column mapping for a partial table migration or if the source and destination table structures are inconsistent, data in columns that exist in the source table but not the destination table is lost.
-
The destination table cannot be an append-optimized (AO) table.
-
If a table to be migrated has a primary key, the primary key column in the destination table must be the same as that in the source table. If a table to be migrated does not have a primary key, the primary key column in the destination table must be the same as the distribution key.
-
The unique key of the destination table, including the primary key column, must contain all columns of the distribution key.
-
If you set Configure Objects to SQL Server Incremental Synchronization Mode in the Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) step, the tables to be migrated must have a clustered index that includes the primary key column. DTS does not support migrating heap tables, tables without primary keys, compressed tables, tables with computed columns, or tables with sparse columns in this mode. These limitations do not apply in hybrid log-based parsing mode.
-
If you set Configure Objects to SQL Server Incremental Synchronization Mode in the Log-based Parsing for Non-heap Tables and CDC-based Incremental Synchronization for Heap Tables (Hybrid Log-based Parsing) step, the following limits also apply:
-
DTS relies on the CDC component for incremental migration. Ensure that the CDC job in the source database is running as expected. Otherwise, the DTS task will fail.
-
By default, the CDC component stores incremental data for three days. Adjust the retention period by running the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command.
Note
-
<time> specifies the retention period in minutes.
-
If the average number of daily incremental SQL changes for a single table exceeds 10 million, set <time> to 1440.
-
In a single migration task, enable CDC for no more than 1,000 tables. Otherwise, the task may experience high latency or become unstable.
-
The precheck module for a DTS incremental data migration task enables CDC in the source database. During this process, the SQL Server engine's internal limitations may cause temporary table locking in the source database.
-
If you set Configure Objects to SQL Server Incremental Synchronization Mode in the Polling and querying CDC instances for incremental synchronization step, the following limits also apply:
-
The source database account used by the DTS instance must have the permissions to enable CDC. An account with the sysadmin role is required to enable database-level CDC. A privileged account is required to enable table-level CDC.
Note
-
The most privileged account (server administrator) provided by the Azure SQL Database console meets the requirements. For databases purchased based on the vCore model, CDC can be enabled for all specifications. For databases purchased based on the DTU model, CDC can be enabled only for service tiers of S3 or higher.
-
The privileged account of an Amazon RDS for SQL Server instance meets the requirements and supports enabling database-level CDC via stored procedures.
-
CDC cannot be enabled for tables that have a clustered columnstore index.
-
The precheck module for a DTS incremental data migration task enables CDC in the source database. During this process, the SQL Server engine's internal limitations may cause temporary table locking in the source database.
-
DTS obtains incremental data by polling and querying the CDC instance of each table in the source database. Migrate no more than 1,000 tables from the source database. Otherwise, the task may experience high latency or become unstable.
-
By default, the CDC component stores incremental data for three days. Adjust the retention period by running the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command.
Note
-
<time> specifies the retention period in minutes.
-
If the average number of daily incremental SQL changes for a single table exceeds 10 million, set <time> to 1440.
-
Consecutive column add or drop operations are not supported (more than two ADD COLUMN or DROP COLUMN DDL operations within one minute). Otherwise, the task may fail.
-
Do not modify the CDC instances of the source database. Otherwise, the data migration task may fail or data loss may occur.
-
To ensure accurate latency reporting for incremental data migration, DTS creates objects in the source database. In the log-based parsing mode, DTS creates a dts_cdc_sync_ddl trigger, a dts_sync_progress heartbeat table, and a dts_cdc_ddl_history DDL storage table. In the hybrid log-based parsing mode, DTS creates the same objects and also enables database-level and table-level CDC. For tables where CDC is enabled, the rate of data changes should not exceed 1,000 rows per second (RPS).
-
Before migrating data, evaluate the performance of the source and destination databases and perform migration during off-peak hours. During full data migration, DTS consumes read and write resources on both databases, which may increase their load.
-
During full data migration, concurrent INSERT operations can cause fragmentation in the destination tables, so table storage space in the destination database may be larger than in the source database after migration.
-
Verify that the precision DTS uses to migrate columns of the FLOAT or DOUBLE data type meets your business requirements. DTS uses the ROUND(COLUMN,PRECISION) function to read values from these columns. If a precision is not explicitly defined, DTS migrates FLOAT values with a precision of 38 digits and DOUBLE values with a precision of 308 digits.
-
DTS attempts to resume a failed task for up to seven days. Before switching your business workloads to the destination instance, end or release the migration task. Alternatively, revoke the write permissions of the DTS account on the destination instance by using the revoke command to prevent a resumed task from overwriting destination data.
-
If a migration task includes incremental data migration, you cannot perform re-indexing operations. This may cause the task to fail or result in data loss.
Note
You cannot modify the primary key of a table for which CDC is enabled.
-
If the number of CDC-enabled tables in a single migration task exceeds the value set in the The maximum number of tables for which CDC is enabled that DTS supports. parameter, the precheck fails.
-
If a task includes incremental migration and a single field in a CDC-enabled table to be written contains more than 64 KB of data, run the exec sp_configure 'max text repl size', -1; command to adjust the source database configuration in advance.
Note
By default, a CDC job can process a maximum data size of 64 KB for a single field.
-
When multiple migration instances use the same source SQL Server database, their incremental data collection modules operate independently.
-
If a task fails, DTS support staff will attempt to restore it within eight hours. During restoration, they may restart the task or adjust its parameters.
Note
Only DTS task parameters are modified—not database parameters. Parameters that may be adjusted include those listed in Modify instance parameters.
-
SQL Server is a commercial, closed-source database whose log format can cause unavoidable issues during incremental CDC and parsing in DTS. Before enabling DTS for incremental synchronization from a SQL Server source in a production environment, perform comprehensive proof-of-concept (POC) testing that covers all business change scenarios, schema adjustments, and peak-load stress tests. Consistency between your production environment logic and the POC phase is essential for stable DTS operations.
-
DTS does not support partial transaction rollbacks in the source database during incremental migration, which may result in lost rollback operations.
|
|
Special cases
|
If the source is an ApsaraDB RDS for SQL Server instance, DTS creates a system account named rdsdt_dtsacct on the instance for data migration. Do not delete this account or change its password while the task is running. Otherwise, the task may fail. For more information, see System accounts.
|