SQL Server source: Limitations and considerations

Type

Description

Source database limitations

• Tables for synchronization must have a primary key or unique constraint. Otherwise, duplicate data may occur in the destination database.

• If a data synchronization task synchronizes more than 5,000 tables at the table level and requires object edits, such as mapping column names, split the tables into multiple tasks.

• A single data synchronization task supports a maximum of 10 databases. If you exceed this limit, you risk stability and performance issues. In this case, split the tables into multiple tasks.

• Synchronization of memory-optimized tables is not supported.

• If you configure a task to synchronize specific objects instead of an entire database, you cannot synchronize objects that have the same table name but different schema names to the same destination database within that task.

• Data Transmission Service (DTS) uses the fn_log function to obtain logs from the source database. This function has performance bottlenecks. Do not clean up source database logs prematurely. Otherwise, the task may fail.

• Transaction logs:

  • Enable transaction logs, set the recovery model to Full, and make sure that a full physical backup has been completed.

  • For an incremental data synchronization task, DTS requires that transaction logs in the source database are retained for at least 24 hours. For a task that includes both full data synchronization and incremental data synchronization, transaction logs must be retained for at least 7 days. After the full data synchronization is complete, you can change the retention period to 24 hours or more. If DTS cannot obtain the transaction logs because the retention period is shorter than required, the task may fail. In extreme cases, this can lead to data inconsistency or loss. Issues that arise from a log retention period shorter than the DTS requirement are not covered by the service level agreement (SLA).

• To enable Change Data Capture (CDC) for tables in the source database, you must meet the following conditions. Otherwise, the precheck fails.

  • 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), upgrade it to a later version.

• If the source database is a read-only instance, DDL operations are not synchronized.

• If the source database is an Azure SQL Database, a single synchronization instance can synchronize data from only one database.

• If the source database is an ApsaraDB RDS for SQL Server instance, disable the Transparent Data Encryption (TDE) feature to ensure the stability of the synchronization instance. For more information, see Disable TDE.

• If you use the sp_rename command to modify the names of objects, such as stored procedures, in the source database before the schema synchronization task runs, the task may produce unexpected results or fail.

  Note

  We recommend that you use the ALTER command to rename objects in the database.

• In hybrid log parsing mode, you cannot perform multiple operations to add or drop columns in quick succession (less than 10 minutes apart). For example, running the following SQL statements consecutively will cause the task to fail.

  ALTER TABLE test_table DROP COLUMN Flag;
  ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');

• Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

  Note

  During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

• If the source database is a Web Edition of ApsaraDB RDS for SQL Server, you must select Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) for SQL Server Incremental Synchronization Mode when you configure the task.

• We recommend that you keep the READ_COMMITTED_SNAPSHOT transaction processing mode parameter for the source database enabled during a full data synchronization task to prevent shared locks from affecting data writes. Otherwise, issues such as data inconsistency and instance failures may occur. Any resulting exceptions are not covered by the DTS SLA.

Other limitations

• Synchronization of the following data types is not supported: CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, and GEOGRAPHY.

• DTS does not support full data synchronization or incremental data synchronization if data cannot be written to a TIMESTAMP field in the destination database. This may lead to data inconsistency or task failure.

• If you synchronize data between different database versions, you must verify their compatibility beforehand.

• To synchronize triggers from the source database, the database account for the task must have Owner permissions on the destination database.

• If you select Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) for SQL Server Incremental Synchronization Mode in the Configure Objects step, the synchronized tables must have a clustered index that includes the primary key columns. Heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns are not supported. These limitations do not apply in hybrid log parsing mode.

  Note

  For information about how to check for these table types, see How to view information about heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns in SQL Server.

• If you select Log-based Parsing for Non-heap Tables and CDC-based Incremental Synchronization for Heap Tables (Hybrid Log-based Parsing) for SQL Server Incremental Synchronization Mode in the Configure Objects step, the following limitations also apply:

  • Incremental data synchronization in DTS depends on the CDC component. Ensure that the CDC jobs in the source database are running correctly. Otherwise, the DTS task fails.

  • By default, the CDC component stores incremental data for 3 days. You can use the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command to adjust the retention period as needed.

    Note

    • <time> specifies the time in minutes.

    • If a single table in the source database generates more than 10 million incremental change SQL statements per day on average, we recommend that you set <time> to 1440.

  • The DTS precheck for an incremental data synchronization task enables CDC on the source database and tables. Due to limitations in the SQL Server database engine, this process can cause brief locking on the source database.

  • In a single data synchronization task, enable CDC on no more than 1,000 tables. Exceeding this limit may cause task latency or instability.

• If you select Polling and querying CDC instances for incremental synchronization for SQL Server Incremental Synchronization Mode in the Configure Objects step, the following limitations also apply:

  • The source database account used by the DTS instance must have permissions to enable CDC. An account with the sysadmin role is required to enable database-level CDC. A high-privilege account is required to enable table-level CDC.

    Note

    • The highest-privilege account (server administrator) provided in the Azure SQL Database console meets this requirement. For databases using the vCore-based purchasing model, all service tiers support CDC. For databases using the DTU-based purchasing model, the service tier must be S3 or higher to support CDC.

    • The high-privilege account for Amazon RDS for SQL Server meets this requirement and supports enabling database-level CDC for stored procedures.

    • CDC is not supported for tables with clustered columnstore indexes.

    • The DTS precheck for an incremental data synchronization task enables CDC on the source database and tables. Due to limitations in the SQL Server database engine, this process can cause brief locking on the source database.

  • DTS polls the CDC instance for each table in the source database to obtain incremental data. Therefore, synchronize no more than 1,000 tables. Otherwise, the task may experience latency or instability.

  • Incremental data stored by the CDC component is retained for 3 days by default. We recommend that you run the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command to set a proper retention period.

  Note

  • <time> specifies the time in minutes.

  • If the average daily number of incremental change SQL statements for a single table in the source database exceeds 10 million, we recommend setting <time> to 1440.

  • Performing consecutive DDL operations to add or drop columns (more than two such operations within one minute) is not supported and may cause the task to fail.

  • Modifying the CDC instance in the source database is not supported and may cause task failure or data loss.

• To ensure accurate latency monitoring for incremental data synchronization, DTS creates specific objects in the source database based on the selected mode. In log parsing mode, DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL history table. In hybrid mode, DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL history table, and also enables CDC at the database level and for some tables. The rate of data changes on tables with CDC enabled should not exceed 1,000 RPS.

• Before you start a data synchronization task, evaluate the performance of the source and destination databases. Also, run the task during off-peak hours. Full data initialization consumes read and write resources on both the source and destination databases, which can increase the database load.

• Full data initialization performs concurrent INSERT operations, which can cause fragmentation in the tables of the destination database. As a result, the table space in the destination instance may be larger than in the source instance after initialization.

• During DTS synchronization, do not write data to the destination database from any source other than DTS. This can cause data inconsistency between the source and destination databases. For example, if you use Data Management (DMS) to perform online DDL operations while other data is being written to the destination, data loss may occur in the destination database.

• Do not rebuild indexes during synchronization. This can cause task failure or even data loss.

  Note

  You cannot perform DDL operations related to the primary key on a table for which CDC is enabled.

• If the number of tables with CDC enabled in a single data synchronization task exceeds the The maximum number of tables for which CDC is enabled that DTS supports., the precheck fails.

• If you need to write more than 64 KB of data to a single field in a CDC-enabled table, you must use the exec sp_configure 'max text repl size', -1; command to adjust the configuration of the source database in advance.

  Note

  By default, the maximum size of a single field that a CDC job can process is 64 KB.

• For incremental data synchronization, you must disable existing triggers and foreign keys in the destination database, or the data synchronization task will fail.

• If you use the Modify Objects to be Synchronized feature, you cannot remove databases from the task.

• Multiple synchronization instances that use the same SQL Server database as a source have independent incremental data capture modules.

• 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. Due to known or unknown characteristics of its log format, issues can arise during incremental change data capture (CDC) and parsing by DTS. Before using DTS for incremental synchronization or migration from a SQL Server source in a production environment, perform comprehensive proof-of-concept (POC) testing. Your tests should cover all business change scenarios, schema modifications, and peak-load stress tests. Ensuring that your production business logic is identical to what was tested in the POC phase is critical for the stable and efficient operation of DTS.

• During incremental data synchronization, partially rolled back transactions on the source are not supported and may be lost.

Special cases

When the source instance is an ApsaraDB RDS for SQL Server, DTS creates an rdsdt_dtsacct account in the source instance for data synchronization. Do not delete this account or change its password while the task is running, or the task may fail. For more information, see System account descriptions.

Type

Description

Source database limitations

• Tables for synchronization must have a primary key or unique constraint. Otherwise, duplicate data may occur in the destination database.

• If a data synchronization task synchronizes more than 5,000 tables at the table level and requires object edits, such as mapping column names, split the tables into multiple tasks.

• A single data synchronization task supports a maximum of 10 databases. If you exceed this limit, you risk stability and performance issues. In this case, split the tables into multiple tasks.

• If you configure a task to synchronize specific objects instead of an entire database, you cannot synchronize objects that have the same table name but different schema names to the same destination database within that task.

• Data Transmission Service (DTS) uses the fn_log function to obtain logs from the source database. This function has performance bottlenecks. Do not clean up source database logs prematurely. Otherwise, the task may fail.

• Transaction logs:

  • Enable transaction logs, set the recovery model to Full, and make sure that a full physical backup has been completed.

  • For an incremental data synchronization task, DTS requires that transaction logs in the source database are retained for at least 24 hours. For a task that includes both full data synchronization and incremental data synchronization, transaction logs must be retained for at least 7 days. After the full data synchronization is complete, you can change the retention period to 24 hours or more. If DTS cannot obtain the transaction logs because the retention period is shorter than required, the task may fail. In extreme cases, this can lead to data inconsistency or loss. Issues that arise from a log retention period shorter than the DTS requirement are not covered by the service level agreement (SLA).

• To enable Change Data Capture (CDC) for tables in the source database, you must meet the following conditions. Otherwise, the precheck fails.

  • 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), upgrade it to a later version.

• If the source database is a read-only instance, DDL operations are not synchronized.

• If the source database is an Azure SQL Database, a single synchronization instance can synchronize data from only one database.

• If the source database is an ApsaraDB RDS for SQL Server instance, disable the Transparent Data Encryption (TDE) feature to ensure the stability of the synchronization instance. For more information, see Disable TDE.

• In hybrid log parsing mode, you cannot perform multiple operations to add or drop columns in quick succession (less than 10 minutes apart). For example, running the following SQL statements consecutively will cause the task to fail.

  ALTER TABLE test_table DROP COLUMN Flag;
  ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');

• If you use the sp_rename command to modify the names of objects, such as stored procedures, in the source database before the schema synchronization task runs, the task may produce unexpected results or fail.

  Note

  We recommend that you use the ALTER command to rename objects in the database.

• Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

  Note

  During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

• If the source database is a Web Edition of ApsaraDB RDS for SQL Server, you must select Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) for SQL Server Incremental Synchronization Mode when you configure the task.

• We recommend that you keep the READ_COMMITTED_SNAPSHOT transaction processing mode parameter for the source database enabled during a full data synchronization task to prevent shared locks from affecting data writes. Otherwise, issues such as data inconsistency and instance failures may occur. Any resulting exceptions are not covered by the DTS SLA.

Other limitations

• Requirements for synchronized objects:

  • DTS supports schema initialization for database, schema, and table objects.

  • Synchronization is not supported for data that uses the CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, or GEOGRAPHY data types, or for user-defined types that are created by using the CREATE TYPE command.

  • DTS does not support synchronizing the following objects: PROCEDURE, FUNCTION, TRIGGER, DATATYPE, SYNONYM, CATALOG, PLAN_GUIDE, and SEQUENCE.

• If your data includes four-byte characters—such as rare Chinese characters or emojis—the destination database and table must use the utf8mb4 charset.

  Note

  If you use DTS to synchronize table schemas, set the character_set_server parameter at the instance level in the destination database to utf8mb4.

• If you select Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) for SQL Server Incremental Synchronization Mode in the Configure Objects step, the synchronized tables must have a clustered index that includes the primary key columns. Heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns are not supported. These limitations do not apply in hybrid log parsing mode.

  Note

  For information about how to check for these table types, see How to view information about heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns in SQL Server.

• If you select Log-based Parsing for Non-heap Tables and CDC-based Incremental Synchronization for Heap Tables (Hybrid Log-based Parsing) for SQL Server Incremental Synchronization Mode in the Configure Objects step, the following limitations also apply:

  • Incremental data synchronization in DTS depends on the CDC component. Ensure that the CDC jobs in the source database are running correctly. Otherwise, the DTS task fails.

  • By default, the CDC component stores incremental data for 3 days. You can use the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command to adjust the retention period as needed.

    Note

    • <time> specifies the time in minutes.

    • If a single table in the source database generates more than 10 million incremental change SQL statements per day on average, we recommend that you set <time> to 1440.

  • The DTS precheck for an incremental data synchronization task enables CDC on the source database and tables. Due to limitations in the SQL Server database engine, this process can cause brief locking on the source database.

  • In a single data synchronization task, enable CDC on no more than 1,000 tables. Exceeding this limit may cause task latency or instability.

• If you select Polling and querying CDC instances for incremental synchronization for SQL Server Incremental Synchronization Mode in the Configure Objects step, the following limitations also apply:

  • The source database account used by the DTS instance must have permissions to enable CDC. An account with the sysadmin role is required to enable database-level CDC. A high-privilege account is required to enable table-level CDC.

    Note

    • The highest-privilege account (server administrator) provided in the Azure SQL Database console meets this requirement. For databases using the vCore-based purchasing model, all service tiers support CDC. For databases using the DTU-based purchasing model, the service tier must be S3 or higher to support CDC.

    • The high-privilege account for Amazon RDS for SQL Server meets this requirement and supports enabling database-level CDC for stored procedures.

    • CDC is not supported for tables with clustered columnstore indexes.

    • The DTS precheck for an incremental data synchronization task enables CDC on the source database and tables. Due to limitations in the SQL Server database engine, this process can cause brief locking on the source database.

  • DTS polls the CDC instance for each table in the source database to obtain incremental data. Therefore, synchronize no more than 1,000 tables. Otherwise, the task may experience latency or instability.

  • Incremental data stored by the CDC component is retained for 3 days by default. We recommend that you run the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command to set a proper retention period.

  Note

  • <time> specifies the time in minutes.

  • If the average daily number of incremental change SQL statements for a single table in the source database exceeds 10 million, we recommend setting <time> to 1440.

  • Performing consecutive DDL operations to add or drop columns (more than two such operations within one minute) is not supported and may cause the task to fail.

  • Modifying the CDC instance in the source database is not supported and may cause task failure or data loss.

• DTS does not support synchronizing complex DDL operations.

• To ensure accurate latency monitoring for incremental data synchronization, DTS creates specific objects in the source database based on the selected mode. In log parsing mode, DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL history table. In hybrid mode, DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL history table, and also enables CDC at the database level and for some tables. The rate of data changes on tables with CDC enabled should not exceed 1,000 RPS.

• Before you start a data synchronization task, evaluate the performance of the source and destination databases. Also, run the task during off-peak hours. Full data initialization consumes read and write resources on both the source and destination databases, which can increase the database load.

• Full data initialization performs concurrent INSERT operations, which can cause fragmentation in the tables of the destination database. As a result, the table space in the destination instance may be larger than in the source instance after initialization.

• During DTS synchronization, do not write data to the destination database from any source other than DTS. This can cause data inconsistency between the source and destination. For example, using DMS to perform online DDL changes while other data is being written to the destination can cause data loss in the destination database.

• Do not rebuild indexes during synchronization. This can cause task failure or even data loss.

  Note

  You cannot perform DDL operations related to the primary key on a table for which CDC is enabled.

• If the number of tables with CDC enabled in a single data synchronization task exceeds the The maximum number of tables for which CDC is enabled that DTS supports., the precheck fails.

• If you need to write more than 64 KB of data to a single field in a CDC-enabled table, you must use the exec sp_configure 'max text repl size', -1; command to adjust the configuration of the source database in advance.

  Note

  By default, the maximum size of a single field that a CDC job can process is 64 KB.

• If a DDL statement fails to be written to the destination database, the DTS task continues to run. You must check the task logs to view the failed DDL statement. For more information about how to view task logs, see View task logs.

• If you write columns with names that differ only in case to the same table in the destination MySQL database, unexpected results may occur because MySQL column names are case-insensitive.

• After data synchronization completes (the instance's Status is Completed), you should use the ANALYZE TABLE <table_name> command to confirm that all data is written to the target table. For example, after the HA failover mechanism is triggered in the target MySQL database, data might be written only to memory, which can cause data loss.

• If you use the Modify Objects to be Synchronized feature, you cannot remove databases from the task.

• Multiple synchronization instances that use the same SQL Server database as a source have independent incremental data capture modules.

• 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. Due to known or unknown characteristics of its log format, issues can arise during incremental change data capture (CDC) and parsing by DTS. Before using DTS for incremental synchronization or migration from a SQL Server source in a production environment, perform comprehensive proof-of-concept (POC) testing. Your tests should cover all business change scenarios, schema modifications, and peak-load stress tests. Ensuring that your production business logic is identical to what was tested in the POC phase is critical for the stable and efficient operation of DTS.

• DTS does not support partially rolled back transactions on the source during incremental synchronization. This can cause the rollback operation to be lost.

Special cases

When the source instance is an ApsaraDB RDS for SQL Server, DTS creates an rdsdt_dtsacct account in the source instance for data synchronization. Do not delete this account or change its password while the task is running, or the task may fail. For more information, see System account descriptions.

Type

Description

Source database limitations

• Tables for synchronization must have a primary key or unique constraint. Otherwise, duplicate data may occur in the destination database.

• If a data synchronization task synchronizes more than 5,000 tables at the table level and requires object edits, such as mapping column names, split the tables into multiple tasks.

• A single data synchronization task supports a maximum of 10 databases. If you exceed this limit, you risk stability and performance issues. In this case, split the tables into multiple tasks.

• If you configure a task to synchronize specific objects instead of an entire database, you cannot synchronize objects that have the same table name but different schema names to the same destination database within that task.

• Data Transmission Service (DTS) uses the fn_log function to obtain logs from the source database. This function has performance bottlenecks. Do not clean up source database logs prematurely. Otherwise, the task may fail.

• Transaction logs:

  • Enable transaction logs, set the recovery model to Full, and make sure that a full physical backup has been completed.

  • For an incremental data synchronization task, DTS requires that transaction logs in the source database are retained for at least 24 hours. For a task that includes both full data synchronization and incremental data synchronization, transaction logs must be retained for at least 7 days. After the full data synchronization is complete, you can change the retention period to 24 hours or more. If DTS cannot obtain the transaction logs because the retention period is shorter than required, the task may fail. In extreme cases, this can lead to data inconsistency or loss. Issues that arise from a log retention period shorter than the DTS requirement are not covered by the service level agreement (SLA).

• To enable Change Data Capture (CDC) for tables in the source database, you must meet the following conditions. Otherwise, the precheck fails.

  • 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), upgrade it to a later version.

• If the source database is a read-only instance, DDL operations are not synchronized.

• If the source database is an Azure SQL Database, a single synchronization instance can synchronize data from only one database.

• If the source database is an ApsaraDB RDS for SQL Server instance, disable the Transparent Data Encryption (TDE) feature to ensure the stability of the synchronization instance. For more information, see Disable TDE.

• In hybrid log parsing mode, you cannot perform multiple operations to add or drop columns in quick succession (less than 10 minutes apart). For example, running the following SQL statements consecutively will cause the task to fail.

  ALTER TABLE test_table DROP COLUMN Flag;
  ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');

• If you use the sp_rename command to modify the names of objects, such as stored procedures, in the source database before the schema synchronization task runs, the task may produce unexpected results or fail.

  Note

  We recommend that you use the ALTER command to rename objects in the database.

• Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

  Note

  During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

• If the source database is a Web Edition of ApsaraDB RDS for SQL Server, you must select Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) for SQL Server Incremental Synchronization Mode when you configure the task.

• We recommend that you keep the READ_COMMITTED_SNAPSHOT transaction processing mode parameter for the source database enabled during a full data synchronization task to prevent shared locks from affecting data writes. Otherwise, issues such as data inconsistency and instance failures may occur. Any resulting exceptions are not covered by the DTS SLA.

Other limitations

• Requirements for synchronized objects:

  • DTS supports schema synchronization for the following object types: database, schema, and table.

  • Synchronization is not supported for data that uses the CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, or GEOGRAPHY data types, or for user-defined types that are created by using the CREATE TYPE command.

  • DTS does not support synchronizing the following objects: PROCEDURE, FUNCTION, TRIGGER, DATATYPE, SYNONYM, CATALOG, PLAN_GUIDE, and SEQUENCE.

• If your data includes four-byte characters—such as rare Chinese characters or emojis—the destination database and table must use the utf8mb4 charset.

  Note

  If you use DTS to synchronize table schemas, set the character_set_server parameter at the instance level in the destination database to utf8mb4.

• If you select Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) for SQL Server Incremental Synchronization Mode in the Configure Objects step, the synchronized tables must have a clustered index that includes the primary key columns. Heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns are not supported. These limitations do not apply in hybrid log parsing mode.

  Note

  For information about how to check for these table types, see How to view information about heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns in SQL Server.

• If you select Log-based Parsing for Non-heap Tables and CDC-based Incremental Synchronization for Heap Tables (Hybrid Log-based Parsing) for SQL Server Incremental Synchronization Mode in the Configure Objects step, the following limitations also apply:

  • Incremental data synchronization in DTS depends on the CDC component. Ensure that the CDC jobs in the source database are running correctly. Otherwise, the DTS task fails.

  • By default, the CDC component stores incremental data for 3 days. You can use the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command to adjust the retention period as needed.

    Note

    • <time> specifies the time in minutes.

    • If a single table in the source database generates more than 10 million incremental change SQL statements per day on average, we recommend that you set <time> to 1440.

  • The DTS precheck for an incremental data synchronization task enables CDC on the source database and tables. Due to limitations in the SQL Server database engine, this process can cause brief locking on the source database.

  • In a single data synchronization task, enable CDC on no more than 1,000 tables. Exceeding this limit may cause task latency or instability.

• If you select Polling and querying CDC instances for incremental synchronization for SQL Server Incremental Synchronization Mode in the Configure Objects step, the following limitations also apply:

  • The source database account used by the DTS instance must have permissions to enable CDC. An account with the sysadmin role is required to enable database-level CDC. A high-privilege account is required to enable table-level CDC.

    Note

    • The highest-privilege account (server administrator) provided in the Azure SQL Database console meets this requirement. For databases using the vCore-based purchasing model, all service tiers support CDC. For databases using the DTU-based purchasing model, the service tier must be S3 or higher to support CDC.

    • The high-privilege account for Amazon RDS for SQL Server meets this requirement and supports enabling database-level CDC for stored procedures.

    • CDC is not supported for tables with clustered columnstore indexes.

    • The DTS precheck for an incremental data synchronization task enables CDC on the source database and tables. Due to limitations in the SQL Server database engine, this process can cause brief locking on the source database.

  • DTS polls the CDC instance for each table in the source database to obtain incremental data. Therefore, synchronize no more than 1,000 tables. Otherwise, the task may experience latency or instability.

  • Incremental data stored by the CDC component is retained for 3 days by default. We recommend that you run the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command to set a proper retention period.

  Note

  • <time> specifies the time in minutes.

  • If the average daily number of incremental change SQL statements for a single table in the source database exceeds 10 million, we recommend setting <time> to 1440.

  • Performing consecutive DDL operations to add or drop columns (more than two such operations within one minute) is not supported and may cause the task to fail.

  • Modifying the CDC instance in the source database is not supported and may cause task failure or data loss.

• Synchronization of complex DDL operations is not supported.

• To ensure accurate latency monitoring for incremental data synchronization, DTS creates specific objects in the source database based on the selected mode. In log parsing mode, DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL history table. In hybrid mode, DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL history table, and also enables CDC at the database level and for some tables. The rate of data changes on tables with CDC enabled should not exceed 1,000 RPS.

• Before you start a data synchronization task, evaluate the performance of the source and destination databases. Also, run the task during off-peak hours. Full data initialization consumes read and write resources on both the source and destination databases, which can increase the database load.

• Full data initialization performs concurrent INSERT operations, which can cause fragmentation in the tables of the destination database. As a result, the table space in the destination instance may be larger than in the source instance after initialization.

• During DTS synchronization, do not write data to the destination database from any source other than DTS. This can cause data inconsistency between the source and destination. For example, if you use DMS to perform online DDL changes while other data is being written to the destination, data loss may occur in the destination database.

• Do not rebuild indexes during synchronization. This can cause task failure or even data loss.

  Note

  You cannot perform DDL operations related to the primary key on a table for which CDC is enabled.

• If the number of tables with CDC enabled in a single data synchronization task exceeds the The maximum number of tables for which CDC is enabled that DTS supports., the precheck fails.

• If you need to write more than 64 KB of data to a single field in a CDC-enabled table, you must use the exec sp_configure 'max text repl size', -1; command to adjust the configuration of the source database in advance.

  Note

  By default, the maximum size of a single field that a CDC job can process is 64 KB.

• If DTS fails to write a DDL operation to the destination database, the task continues to run. You must check the task log to view the failed DDL operation. For more information about how to view the task log, see View task logs.

• If you use the Modify Objects to be Synchronized feature, you cannot remove databases from the task.

• Multiple synchronization instances that use the same SQL Server database as a source have independent incremental data capture modules.

• 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. Due to known or unknown characteristics of its log format, issues can arise during incremental change data capture (CDC) and parsing by DTS. Before using DTS for incremental synchronization or migration from a SQL Server source in a production environment, perform comprehensive proof-of-concept (POC) testing. Your tests should cover all business change scenarios, schema modifications, and peak-load stress tests. Ensuring that your production business logic is identical to what was tested in the POC phase is critical for the stable and efficient operation of DTS.

• During incremental synchronization, DTS does not support partially rolled back transactions from the source database. The rollback operation may be lost.

Special case

When the source instance is an ApsaraDB RDS for SQL Server, DTS creates an rdsdt_dtsacct account in the source instance for data synchronization. Do not delete this account or change its password while the task is running, or the task may fail. For more information, see System account descriptions.

Type

Description

Source database limitations

• Tables for synchronization must have a primary key or unique constraint. Otherwise, duplicate data may occur in the destination database.

• If a data synchronization task synchronizes more than 5,000 tables at the table level and requires object edits, such as mapping column names, split the tables into multiple tasks.

• A single data synchronization task supports a maximum of 10 databases. If you exceed this limit, you risk stability and performance issues. In this case, split the tables into multiple tasks.

• If you configure a task to synchronize specific objects instead of an entire database, you cannot synchronize objects that have the same table name but different schema names to the same destination database within that task.

• Data Transmission Service (DTS) uses the fn_log function to obtain logs from the source database. This function has performance bottlenecks. Do not clean up source database logs prematurely. Otherwise, the task may fail.

• Transaction logs:

  • Enable transaction logs, set the recovery model to Full, and make sure that a full physical backup has been completed.

  • For an incremental data synchronization task, DTS requires that transaction logs in the source database are retained for at least 24 hours. For a task that includes both full data synchronization and incremental data synchronization, transaction logs must be retained for at least 7 days. After the full data synchronization is complete, you can change the retention period to 24 hours or more. If DTS cannot obtain the transaction logs because the retention period is shorter than required, the task may fail. In extreme cases, this can lead to data inconsistency or loss. Issues that arise from a log retention period shorter than the DTS requirement are not covered by the service level agreement (SLA).

• To enable Change Data Capture (CDC) for tables in the source database, you must meet the following conditions. Otherwise, the precheck fails.

  • 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), upgrade it to a later version.

• If the source database is a read-only instance, DDL operations are not synchronized.

• If the source database is an Azure SQL Database, a single synchronization instance can synchronize data from only one database.

• If the source database is an ApsaraDB RDS for SQL Server instance, disable the Transparent Data Encryption (TDE) feature to ensure the stability of the synchronization instance. For more information, see Disable TDE.

• In hybrid log parsing mode, you cannot perform multiple operations to add or drop columns in quick succession (less than 10 minutes apart). For example, running the following SQL statements consecutively will cause the task to fail.

  ALTER TABLE test_table DROP COLUMN Flag;
  ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');

• If you use the sp_rename command to modify the names of objects, such as stored procedures, in the source database before the schema synchronization task runs, the task may produce unexpected results or fail.

  Note

  We recommend that you use the ALTER command to rename objects in the database.

• Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

  Note

  During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

• If the source database is a Web Edition of ApsaraDB RDS for SQL Server, you must select Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) for SQL Server Incremental Synchronization Mode when you configure the task.

• We recommend that you keep the READ_COMMITTED_SNAPSHOT transaction processing mode parameter for the source database enabled during a full data synchronization task to prevent shared locks from affecting data writes. Otherwise, issues such as data inconsistency and instance failures may occur. Any resulting exceptions are not covered by the DTS SLA.

Other limitations

• Requirements for synchronized objects:

  • Schema synchronization is supported for Schema, Table, View, Function, and Procedure objects.

    Warning

    Data synchronization between heterogeneous databases can lead to data type mapping issues, potentially causing task failure or data loss. Carefully evaluate the business impact of data type mapping. For more information, see Data type mappings for schema synchronization.

  • Schema synchronization is not supported for the following objects: assemblies, service broker, full-text indexes, full-text catalogs, distributed schemas, distributed functions, CLR stored procedures, CLR scalar-valued functions, CLR table-valued functions, internal tables, system objects, and aggregate functions.

  • Synchronization is not supported for data that uses the CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, or GEOGRAPHY data types, or for user-defined types that are created by using the CREATE TYPE command.

  • Synchronization of tables that contain computed columns is not supported.

• The destination database must contain a custom primary key. Or, in the Configurations for Databases, Tables, and Columns step, configure a Primary Key Column. Otherwise, synchronization may fail.

• If you select Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) for SQL Server Incremental Synchronization Mode in the Configure Objects step, the synchronized tables must have a clustered index that includes the primary key columns. Heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns are not supported. These limitations do not apply in hybrid log parsing mode.

  Note

  For information about how to check for these table types, see How to view information about heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns in SQL Server.

• If you select Log-based Parsing for Non-heap Tables and CDC-based Incremental Synchronization for Heap Tables (Hybrid Log-based Parsing) for SQL Server Incremental Synchronization Mode in the Configure Objects step, the following limitations also apply:

  • Incremental data synchronization in DTS depends on the CDC component. Ensure that the CDC jobs in the source database are running correctly. Otherwise, the DTS task fails.

  • By default, the CDC component stores incremental data for 3 days. You can use the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command to adjust the retention period as needed.

    Note

    • <time> specifies the time in minutes.

    • If a single table in the source database generates more than 10 million incremental change SQL statements per day on average, we recommend that you set <time> to 1440.

  • The DTS precheck for an incremental data synchronization task enables CDC on the source database and tables. Due to limitations in the SQL Server database engine, this process can cause brief locking on the source database.

  • In a single data synchronization task, enable CDC on no more than 1,000 tables. Exceeding this limit may cause task latency or instability.

• If you select Polling and querying CDC instances for incremental synchronization for SQL Server Incremental Synchronization Mode in the Configure Objects step, the following limitations also apply:

  • The source database account used by the DTS instance must have permissions to enable CDC. An account with the sysadmin role is required to enable database-level CDC. A high-privilege account is required to enable table-level CDC.

    Note

    • The highest-privilege account (server administrator) provided in the Azure SQL Database console meets this requirement. For databases using the vCore-based purchasing model, all service tiers support CDC. For databases using the DTU-based purchasing model, the service tier must be S3 or higher to support CDC.

    • The high-privilege account for Amazon RDS for SQL Server meets this requirement and supports enabling database-level CDC for stored procedures.

    • CDC is not supported for tables with clustered columnstore indexes.

    • The DTS precheck for an incremental data synchronization task enables CDC on the source database and tables. Due to limitations in the SQL Server database engine, this process can cause brief locking on the source database.

  • DTS polls the CDC instance for each table in the source database to obtain incremental data. Therefore, synchronize no more than 1,000 tables. Otherwise, the task may experience latency or instability.

  • Incremental data stored by the CDC component is retained for 3 days by default. We recommend that you run the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command to set a proper retention period.

  Note

  • <time> specifies the time in minutes.

  • If the average daily number of incremental change SQL statements for a single table in the source database exceeds 10 million, we recommend setting <time> to 1440.

  • Performing consecutive DDL operations to add or drop columns (more than two such operations within one minute) is not supported and may cause the task to fail.

  • Modifying the CDC instance in the source database is not supported and may cause task failure or data loss.

• To ensure accurate latency monitoring for incremental data synchronization, DTS creates specific objects in the source database based on the selected mode. In log parsing mode, DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL history table. In hybrid mode, DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL history table, and also enables CDC at the database level and for some tables. The rate of data changes on tables with CDC enabled should not exceed 1,000 RPS.

• Due to limitations of AnalyticDB for MySQL (3.0), if the disk space usage of nodes in an AnalyticDB for MySQL (3.0) cluster exceeds 80%, the performance of data writes to the destination database slows down, causing latency in the DTS task. If the disk space usage exceeds 90%, data cannot be written to the destination database, and the DTS task fails. Estimate the required storage space based on the objects to be synchronized and ensure that the destination cluster has sufficient storage.

• Before you start a data synchronization task, evaluate the performance of the source and destination databases. Also, run the task during off-peak hours. Full data initialization consumes read and write resources on both the source and destination databases, which can increase the database load.

• Full data initialization performs concurrent INSERT operations, which can cause fragmentation in the tables of the destination database. As a result, the table space in the destination instance may be larger than in the source instance after initialization.

• To prevent data inconsistency, do not write data to the destination database from any source other than the DTS task. For example, if you use Data Management (DMS) to perform online DDL changes while other data is being written to the destination, data loss may occur in the destination database.

• Do not rebuild indexes during synchronization. This can cause task failure or even data loss.

  Note

  You cannot perform DDL operations related to the primary key on a table for which CDC is enabled.

• If the number of tables with CDC enabled in a single data synchronization task exceeds the The maximum number of tables for which CDC is enabled that DTS supports., the precheck fails.

• If you need to write more than 64 KB of data to a single field in a CDC-enabled table, you must use the exec sp_configure 'max text repl size', -1; command to adjust the configuration of the source database in advance.

  Note

  By default, the maximum size of a single field that a CDC job can process is 64 KB.

• If a DDL statement fails to be written to the destination database, the DTS task continues to run. You must check the task logs to view the failed DDL statement. For more information about how to view task logs, see View task logs.

• If you use the Modify Objects to be Synchronized feature, you cannot remove databases from the task.

• If the destination AnalyticDB for MySQL 3.0 cluster is backing up while the DTS task runs, the task fails.

• Multiple synchronization instances that use the same SQL Server database as a source have independent incremental data capture modules.

• 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. Due to known or unknown characteristics of its log format, issues can arise during incremental change data capture (CDC) and parsing by DTS. Before using DTS for incremental synchronization or migration from a SQL Server source in a production environment, perform comprehensive proof-of-concept (POC) testing. Your tests should cover all business change scenarios, schema modifications, and peak-load stress tests. Ensuring that your production business logic is identical to what was tested in the POC phase is critical for the stable and efficient operation of DTS.

• When the destination database is AnalyticDB for MySQL, DTS supports writing only the data types that are supported by the destination, including basic data types and complex data types such as ARRAY, MAP, and JSON. Other types, such as MULTIVALUE, are not supported.

• During incremental data synchronization, partially rolled back transactions on the source are not supported. The rollback operation may be lost.

Special cases

When the source instance is an ApsaraDB RDS for SQL Server, DTS creates an rdsdt_dtsacct account in the source instance for data synchronization. Do not delete this account or change its password while the task is running, or the task may fail. For more information, see System account descriptions.

Type

Description

Source database limitations

• Tables for synchronization must have a primary key or unique constraint. Otherwise, duplicate data may occur in the destination database.

• If a data synchronization task synchronizes more than 5,000 tables at the table level and requires object edits, such as mapping column names, split the tables into multiple tasks.

• A single data synchronization task supports a maximum of 10 databases. If you exceed this limit, you risk stability and performance issues. In this case, split the tables into multiple tasks.

• If you configure a task to synchronize specific objects instead of an entire database, you cannot synchronize objects that have the same table name but different schema names to the same destination database within that task.

• Data Transmission Service (DTS) uses the fn_log function to obtain logs from the source database. This function has performance bottlenecks. Do not clean up source database logs prematurely. Otherwise, the task may fail.

• Transaction logs:

  • Enable transaction logs, set the recovery model to Full, and make sure that a full physical backup has been completed.

  • For an incremental data synchronization task, DTS requires that transaction logs in the source database are retained for at least 24 hours. For a task that includes both full data synchronization and incremental data synchronization, transaction logs must be retained for at least 7 days. After the full data synchronization is complete, you can change the retention period to 24 hours or more. If DTS cannot obtain the transaction logs because the retention period is shorter than required, the task may fail. In extreme cases, this can lead to data inconsistency or loss. Issues that arise from a log retention period shorter than the DTS requirement are not covered by the service level agreement (SLA).

• To enable Change Data Capture (CDC) for tables in the source database, you must meet the following conditions. Otherwise, the precheck fails.

  • 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), upgrade it to a later version.

• If the source database is a read-only instance, DDL operations are not synchronized.

• If the source database is an Azure SQL Database, a single synchronization instance can synchronize data from only one database.

• If the source database is an ApsaraDB RDS for SQL Server instance, disable the Transparent Data Encryption (TDE) feature to ensure the stability of the synchronization instance. For more information, see Disable TDE.

• If you use the sp_rename command to modify the names of objects, such as stored procedures, in the source database before the schema synchronization task runs, the task may produce unexpected results or fail.

  Note

  We recommend that you use the ALTER command to rename objects in the database.

• In hybrid log parsing mode, you cannot perform multiple operations to add or drop columns in quick succession (less than 10 minutes apart). For example, running the following SQL statements consecutively will cause the task to fail.

  ALTER TABLE test_table DROP COLUMN Flag;
  ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');

• Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

  Note

  During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

• If the source database is a Web Edition of ApsaraDB RDS for SQL Server, you must select Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) for SQL Server Incremental Synchronization Mode when you configure the task.

• We recommend that you keep the READ_COMMITTED_SNAPSHOT transaction processing mode parameter for the source database enabled during a full data synchronization task to prevent shared locks from affecting data writes. Otherwise, issues such as data inconsistency and instance failures may occur. Any resulting exceptions are not covered by the DTS SLA.

Other limitations

• Requirements for synchronized objects:

  • Schema initialization is supported for Schema, Table, View, Function, and Procedure objects.

    Warning

    Because this scenario involves data synchronization between heterogeneous databases, data types may not map one-to-one. This can cause task failure or data loss. Carefully evaluate the business impact of data type mapping. For more information, see Data type mappings for schema initialization.

  • DTS does not support schema initialization for the following objects: assemblies, service broker, full-text indexes, full-text catalogs, distributed schemas, distributed functions, CLR stored procedures, CLR scalar-valued functions, CLR table-valued functions, internal tables, system objects, and aggregate functions.

  • Synchronization is not supported for data that uses the CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, or GEOGRAPHY data types, or for user-defined types that are created by using the CREATE TYPE command.

  • DTS does not support synchronizing tables with computed columns.

  • DTS does not support synchronizing the following objects: INDEX, VIEW, PROCEDURE, FUNCTION, TRIGGER, FK, FULL_TEXT_INDEX, DATATYPE, DEFAULT, SYNONYM, CATALOG, PLAN_GUIDE, DEFAULT_CONSTRAINT, UK, CK, and SEQUENCE.

• If the table to synchronize has a primary key, the primary key column in the destination table must match the source table. If the table to synchronize lacks a primary key, the primary key column in the destination table must match the distribution key.

• The unique key in the destination table—including the primary key column—must include all columns in the distribution key.

• If you select Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) for SQL Server Incremental Synchronization Mode in the Configure Objects step, the synchronized tables must have a clustered index that includes the primary key columns. Heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns are not supported. These limitations do not apply in hybrid log parsing mode.

  Note

  For information about how to check for these table types, see How to view information about heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns in SQL Server.

• If you select Log-based Parsing for Non-heap Tables and CDC-based Incremental Synchronization for Heap Tables (Hybrid Log-based Parsing) for SQL Server Incremental Synchronization Mode in the Configure Objects step, the following limitations also apply:

  • Incremental data synchronization in DTS depends on the CDC component. Ensure that the CDC jobs in the source database are running correctly. Otherwise, the DTS task fails.

  • By default, the CDC component stores incremental data for 3 days. You can use the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command to adjust the retention period as needed.

    Note

    • <time> specifies the time in minutes.

    • If a single table in the source database generates more than 10 million incremental change SQL statements per day on average, we recommend that you set <time> to 1440.

  • The DTS precheck for an incremental data synchronization task enables CDC on the source database and tables. Due to limitations in the SQL Server database engine, this process can cause brief locking on the source database.

  • In a single data synchronization task, enable CDC on no more than 1,000 tables. Exceeding this limit may cause task latency or instability.

• If you select Polling and querying CDC instances for incremental synchronization for SQL Server Incremental Synchronization Mode in the Configure Objects step, the following limitations also apply:

  • The source database account used by the DTS instance must have permissions to enable CDC. An account with the sysadmin role is required to enable database-level CDC. A high-privilege account is required to enable table-level CDC.

    Note

    • The highest-privilege account (server administrator) provided in the Azure SQL Database console meets this requirement. For databases using the vCore-based purchasing model, all service tiers support CDC. For databases using the DTU-based purchasing model, the service tier must be S3 or higher to support CDC.

    • The high-privilege account for Amazon RDS for SQL Server meets this requirement and supports enabling database-level CDC for stored procedures.

    • CDC is not supported for tables with clustered columnstore indexes.

    • The DTS precheck for an incremental data synchronization task enables CDC on the source database and tables. Due to limitations in the SQL Server database engine, this process can cause brief locking on the source database.

  • DTS polls the CDC instance for each table in the source database to obtain incremental data. Therefore, synchronize no more than 1,000 tables. Otherwise, the task may experience latency or instability.

  • Incremental data stored by the CDC component is retained for 3 days by default. We recommend that you run the exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; command to set a proper retention period.

  Note

  • <time> specifies the time in minutes.

  • If the average daily number of incremental change SQL statements for a single table in the source database exceeds 10 million, we recommend setting <time> to 1440.

  • Performing consecutive DDL operations to add or drop columns (more than two such operations within one minute) is not supported and may cause the task to fail.

  • Modifying the CDC instance in the source database is not supported and may cause task failure or data loss.

• To ensure accurate latency monitoring for incremental data synchronization, DTS creates specific objects in the source database based on the selected mode. In log parsing mode, DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL history table. In hybrid mode, DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL history table, and also enables CDC at the database level and for some tables. The rate of data changes on tables with CDC enabled should not exceed 1,000 RPS.

• Before you start a data synchronization task, evaluate the performance of the source and destination databases. Also, run the task during off-peak hours. Full data initialization consumes read and write resources on both the source and destination databases, which can increase the database load.

• Full data initialization performs concurrent INSERT operations, which can cause fragmentation in the tables of the destination database. As a result, the table space in the destination instance may be larger than in the source instance after initialization.

• During synchronization, do not write data to the destination database from any external source. This can cause data inconsistency. For example, if you use DMS to perform online DDL changes while other data is being written to the destination, data loss may occur in the destination database.

• You can select objects at the table level and modify column mappings. If you use column mapping for a partial table synchronization or if the source and destination table schemas are inconsistent, data in columns that exist in the source but not in the destination is lost.

• Do not rebuild indexes during synchronization. This can cause task failure or even data loss.

  Note

  You cannot perform DDL operations related to the primary key on a table for which CDC is enabled.

• If the number of tables with CDC enabled in a single data synchronization task exceeds the The maximum number of tables for which CDC is enabled that DTS supports., the precheck fails.

• If you need to write more than 64 KB of data to a single field in a CDC-enabled table, you must use the exec sp_configure 'max text repl size', -1; command to adjust the configuration of the source database in advance.

  Note

  By default, the maximum size of a single field that a CDC job can process is 64 KB.

• If you use the Modify Objects to be Synchronized feature, you cannot remove databases from the task.

• Multiple synchronization instances that use the same SQL Server database as a source have independent incremental data capture modules.

• 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. Due to known or unknown characteristics of its log format, issues can arise during incremental change data capture (CDC) and parsing by DTS. Before using DTS for incremental synchronization or migration from a SQL Server source in a production environment, perform comprehensive proof-of-concept (POC) testing. Your tests should cover all business change scenarios, schema modifications, and peak-load stress tests. Ensuring that your production business logic is identical to what was tested in the POC phase is critical for the stable and efficient operation of DTS.

• During incremental synchronization, partially rolled back transactions on the source are not supported. The rollback operation may be lost.

Special cases

When the source instance is an ApsaraDB RDS for SQL Server, DTS creates an rdsdt_dtsacct account in the source instance for data synchronization. Do not delete this account or change its password while the task is running, or the task may fail. For more information, see System account descriptions.

Type

Description

Source database limitations

• Tables for synchronization must have a primary key or unique constraint. Otherwise, duplicate data may occur in the destination database.

• If a data synchronization task synchronizes more than 5,000 tables at the table level and requires object edits, such as mapping column names, split the tables into multiple tasks.

• A single data synchronization task supports a maximum of 10 databases. If you exceed this limit, you risk stability and performance issues. In this case, split the tables into multiple tasks.

• If you configure a task to synchronize specific objects instead of an entire database, you cannot synchronize objects that have the same table name but different schema names to the same destination database within that task.

• Data Transmission Service (DTS) uses the fn_log function to obtain logs from the source database. This function has performance bottlenecks. Do not clean up source database logs prematurely. Otherwise, the task may fail.

• Transaction logs:

  • Enable transaction logs, set the recovery model to Full, and make sure that a full physical backup has been completed.

  • For an incremental data synchronization task, DTS requires that transaction logs in the source database are retained for at least 24 hours. For a task that includes both full data synchronization and incremental data synchronization, transaction logs must be retained for at least 7 days. After the full data synchronization is complete, you can change the retention period to 24 hours or more. If DTS cannot obtain the transaction logs because the retention period is shorter than required, the task may fail. In extreme cases, this can lead to data inconsistency or loss. Issues that arise from a log retention period shorter than the DTS requirement are not covered by the service level agreement (SLA).

• To enable Change Data Capture (CDC) for tables in the source database, you must meet the following conditions. Otherwise, the precheck fails.

  • 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), upgrade it to a later version.

• If the source database is an Azure SQL Database, a single synchronization instance can synchronize data from only one database.

• If the source database is an ApsaraDB RDS for SQL Server instance, disable the Transparent Data Encryption (TDE) feature to ensure the stability of the synchronization instance. For more information, see Disable TDE.

• If you use the sp_rename command to modify the names of objects, such as stored procedures, in the source database before the schema synchronization task runs, the task may produce unexpected results or fail.

  Note

  We recommend that you use the ALTER command to rename objects in the database.

• In hybrid log parsing mode, you cannot perform multiple operations to add or drop columns in quick succession (less than 10 minutes apart). For example, running the following SQL statements consecutively will cause the task to fail.

  ALTER TABLE test_table DROP COLUMN Flag;
  ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');

• Do not perform DDL operations that change database or table schemas during schema synchronization, as this will cause the task to fail.

• We recommend that you keep the READ_COMMITTED_SNAPSHOT transaction processing mode parameter for the source database enabled during a full data synchronization task to prevent shared locks from affecting data writes. Otherwise, issues such as data inconsistency and instance failures may occur. Any resulting exceptions are not covered by the DTS SLA.

Other limitations

• Only incremental synchronization and schema synchronization are supported. Full synchronization is not supported.

• The maximum length of a single STRING field in the destination DataHub is 2 MB.

• Requirements for synchronized objects:

  • Schema synchronization is supported for the following object types: tables, views, functions, and procedures.

    Warning

    Because this scenario involves data synchronization between heterogeneous databases, data types may not map one-to-one. This can cause task failure or data loss. Carefully evaluate the business impact of data type mapping. For more information, see Data type mappings for schema synchronization.

  • Schema synchronization is not supported for the following objects: assemblies, service broker, full-text indexes, full-text catalogs, distributed schemas, distributed functions, CLR stored procedures, CLR scalar-valued functions, CLR table-valued functions, internal tables, systems, and aggregate functions.

  • Synchronization is not supported for data that uses the CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, or GEOGRAPHY data types, or for user-defined types that are created by using the CREATE TYPE command.

  • Synchronization of tables that contain computed columns is not supported.

  • Synchronization of the following objects is not supported: INDEX, trigger, FK, FULL_TEXT_INDEX, DATATYPE, DEFAULT, SYNONYM, CATALOG, PLAN_GUIDE, DEFAULT_CONSTRAINT, UK, CK, and SEQUENCE.

• To ensure accurate latency monitoring for incremental data synchronization, DTS creates specific objects in the source database based on the selected mode. In log parsing mode, DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL history table. In hybrid mode, DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL history table, and also enables CDC at the database level and for some tables. The rate of data changes on tables with CDC enabled should not exceed 1,000 RPS.

• Before you start a data synchronization task, evaluate the performance of the source and destination databases. Also, run the task during off-peak hours. Full data initialization consumes read and write resources on both the source and destination databases, which can increase the database load.

• Full data initialization performs concurrent INSERT operations, which can cause fragmentation in the tables of the destination database. As a result, the table space in the destination instance may be larger than in the source instance after initialization.

• During DTS synchronization, do not write data to the destination database from other sources. This can cause data inconsistency. For example, if you use Data Management Service (DMS) to perform online DDL changes while other data is being written to the destination, data loss may occur in the destination database.

• Do not rebuild indexes during synchronization. This can cause task failure or even data loss.

  Note

  You cannot perform DDL operations related to the primary key on a table for which CDC is enabled.

• If the number of tables with CDC enabled in a single synchronization task exceeds 1,000, the precheck fails.

• If you need to write more than 64 KB of data to a single field in a CDC-enabled table, you must use the exec sp_configure 'max text repl size', -1; command to adjust the configuration of the source database in advance.

  Note

  By default, the maximum size of a single field that a CDC job can process is 64 KB.

• If you use the Modify Objects to be Synchronized feature, you cannot remove databases from the task.

• Multiple synchronization instances that use the same SQL Server database as a source have independent incremental data capture modules.

• 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. Due to known or unknown characteristics of its log format, issues can arise during incremental change data capture (CDC) and parsing by DTS. Before using DTS for incremental synchronization or migration from a SQL Server source in a production environment, perform comprehensive proof-of-concept (POC) testing. Your tests should cover all business change scenarios, schema modifications, and peak-load stress tests. Ensuring that your production business logic is identical to what was tested in the POC phase is critical for the stable and efficient operation of DTS.

• During incremental synchronization, partially rolled back transactions on the source are not supported. The rollback operation may be lost.

Special cases

When the source instance is an ApsaraDB RDS for SQL Server, DTS creates an rdsdt_dtsacct account in the source instance for data synchronization. Do not delete this account or change its password while the task is running, or the task may fail. For more information, see System account descriptions.