Precautions and limits for synchronizing data from an SQL Server database - Data Transmission Service

This topic describes the precautions and limits that apply when you synchronize data from an SQL Server database, such as a self-managed SQL Server database or an ApsaraDB RDS for SQL Server database. To ensure that your data synchronization task runs as expected, you must read the precautions and limits before you configure the task.

Scenarios of synchronizing data from an SQL Server database

The following list provides the scenarios of synchronizing data from an SQL Server database. The precautions and limits in the scenarios may vary. You can go to the related section to view the precautions and limits in a specific scenario.

Note

By default, Data Transmission Service (DTS) disables FOREIGN KEY constraints for the destination database in a data synchronization task. Therefore, operations such as cascade and delete of the source database are not synchronized to the following types of destination databases:

SQL Server database
ApsaraDB RDS for MySQL database or self-managed MySQL database
PolarDB for MySQL cluster
AnalyticDB for MySQL cluster
AnalyticDB for PostgreSQL database
DataHub project

Synchronize data between SQL Server databases
Synchronize data from an ApsaraDB RDS for SQL Server instance to a MySQL database
Synchronize data from an SQL Server database to a PolarDB for MySQL cluster
Synchronize data from an SQL Server database to an AnalyticDB for MySQL cluster
Synchronize data from an SQL Server database to an AnalyticDB for PostgreSQL instance
Synchronize data from an ApsaraDB RDS for SQL Server instance to a DataHub project

Synchronize data between SQL Server databases

The following table describes the precautions and limits that apply when you synchronize data between SQL Server databases.

Category	Description
Limits on the source database	The tables to be synchronized must have PRIMARY KEY or UNIQUE constraints and all fields must be unique. Otherwise, the destination database may contain duplicate data records. If you select tables as the objects to be synchronized and you need to edit the tables in the destination database, such as renaming tables or columns, you can synchronize up to 5,000 tables in a single data synchronization task. If you run a task to synchronize more than 5,000 tables, a request error occurs. In this case, we recommend that you configure multiple tasks to synchronize the tables in batches or configure a task to synchronize the entire database. A single data synchronization task can synchronize data from up to 10 databases. If you want to synchronize data from more than 10 databases, we recommend that you configure multiple tasks to synchronize the data in batches. Otherwise, the performance and stability of your data synchronization task may be compromised. DTS does not synchronize tables for memory optimization. DTS uses the fn_log function to obtain logs of the source database. However, this function has performance bottlenecks. Therefore, we recommend that you do not clear the logs of the source database before the task is complete. Otherwise, the task may fail. The following requirements for data logs must be met: The data logging feature must be enabled. The backup mode must be set to Full and full physical backup must be performed. If you perform only incremental data synchronization, the data logs of the source database must be stored for more than 24 hours. If you perform both full data synchronization and incremental data synchronization, the data logs of the source database must be stored for at least seven days. Otherwise, DTS may fail to obtain the data logs and the task may fail. In exceptional circumstances, data inconsistency or loss may occur. After full data synchronization is complete, you can set the retention period to more than 24 hours. Make sure that you set the retention period of data logs based on the preceding requirements. Otherwise, the service level agreement (SLA) of DTS does not guarantee service reliability or performance. If CDC needs to be enabled for the tables to be synchronized from the source database, make sure that the tables meet the following requirements. Otherwise, the precheck fails. The value of the srvname field in the sys.sysservers view is the same as the return value of the SERVERPROPERTY function. If the source database is a self-managed SQL Server database, the database owner must be the sa user. If the source database is an ApsaraDB RDS for SQL Server database, the database owner must be the sqlsa user. If the source database is of Enterprise Edition, you must use SQL Server 2008 or later. If the source database is of Standard Edition, you must use SQL Server 2016 SP1 or later. If the source database is of Standard Edition or Enterprise Edition and its version is SQL Server 2017, we recommend that you update the version. If the source database is a read-only instance, you cannot synchronize DDL operations. In hybrid log-based parsing mode, you cannot perform multiple operations to add columns to or remove columns from the source database within 10 minutes. For example, if you execute the following SQL statements within 10 minutes, an error is reported for the task. `ALTER TABLE test_table DROP COLUMN Flag; ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');`
Other limits	DTS does not synchronize data of the following types: TEXT, CURSOR, ROWVERSION, SQL_VARIANT, HIERACHYID, and GEOMETRY. If you synchronize data between different versions of databases, make sure that the database versions are compatible. If you set the SQL Server Incremental Synchronization Mode parameter to Incremental Synchronization Based on Logs of Source Database in the Configure Objects and Advanced Settings step, the tables to be synchronized must have clustered indexes that contain primary key columns. In addition, the tables to be synchronized cannot be heap tables, compressed tables, tables without primary keys, or tables with computed columns. Ignore the preceding limits in the hybrid log-based parsing mode. Note For more information about how to view table information, see the How do I view information about heap tables, tables without primary keys, compressed tables, and tables that contain computed columns in an SQL Server database? section of the "FAQ" topic. In Incremental Synchronization Based on Logs of Source Database mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a DDL history table named dts_cdc_ddl_history in the source database to ensure that the latency of data synchronization is accurate. In hybrid log-based parsing incremental synchronization mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a DDL history table named dts_cdc_ddl_history and enables CDC for the source database and specific tables. We recommend that you set the maximum number of records per second to 1,000 for the tables for which CDC is enabled in the source database. Before you synchronize data, evaluate the impact of data synchronization on the performance of the source and destination databases. We recommend that you synchronize data during off-peak hours. During initial full data synchronization, DTS uses the read and write resources of the source and destination databases. This may increase the loads of the database servers. During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. After the initial full data synchronization is complete, the size of used tablespace of the destination database is larger than that of the source database. During data synchronization, we recommend that you use only DTS to write data to the destination database. This prevents data inconsistency between the source and destination databases. For example, if you use tools other than DTS to write data to the destination database, data loss may occur in the destination database when you use Data Management (DMS) to perform online DDL operations. If the data synchronization task involves incremental data synchronization, DTS does not allow you to perform the reindexing operation. If you perform the reindexing operation, the data synchronization task may fail, or even data loss may occur. Note DTS cannot synchronize DDL operations related to the primary key of a table for which change data capture (CDC) is enabled. If the number of tables for which CDC is enabled exceeds 1,000 in a data synchronization task, the precheck fails. To perform incremental data synchronization, you must disable the trigger and foreign keys in the destination database. Otherwise, the data synchronization task fails.
Special cases	If the source instance is an ApsaraDB RDS for SQL Server instance, DTS automatically creates an account named `rdsdt_dtsacct` within the ApsaraDB RDS for SQL Server instance. This account is used for data synchronization. Do not delete this account or change the password of this account when your data synchronization task is running. Otherwise, the task may fail. For more information, see System accounts.

Synchronize data from an ApsaraDB RDS for SQL Server instance to a MySQL database

The following table describes the precautions and limits that apply when you synchronize data from an ApsaraDB RDS for SQL Server instance to a MySQL database, such as a self-managed MySQL database or an ApsaraDB RDS for MySQL database.

Category	Description
Limits on the source database	The tables to be synchronized must have PRIMARY KEY or UNIQUE constraints and all fields must be unique. Otherwise, the destination database may contain duplicate data records. If you select tables as the objects to be synchronized and you need to edit the tables in the destination database, such as renaming tables or columns, you can synchronize up to 5,000 tables in a single data synchronization task. If you run a task to synchronize more than 5,000 tables, a request error occurs. In this case, we recommend that you configure multiple tasks to synchronize the tables in batches or configure a task to synchronize the entire database. A single data synchronization task can synchronize data from up to 10 databases. If you want to synchronize data from more than 10 databases, we recommend that you configure multiple tasks to synchronize the data in batches. Otherwise, the performance and stability of your data synchronization task may be compromised. DTS uses the fn_log function to obtain logs of the source database. However, this function has performance bottlenecks. Therefore, we recommend that you do not clear the logs of the source database before the task is complete. Otherwise, the task may fail. The following requirements for data logs must be met: The data logging feature must be enabled. The backup mode must be set to Full and full physical backup must be performed. If you perform only incremental data synchronization, the data logs of the source database must be stored for more than 24 hours. If you perform both full data synchronization and incremental data synchronization, the data logs of the source database must be stored for at least seven days. Otherwise, DTS may fail to obtain the data logs and the task may fail. In exceptional circumstances, data inconsistency or loss may occur. After full data synchronization is complete, you can set the retention period to more than 24 hours. Make sure that you set the retention period of data logs based on the preceding requirements. Otherwise, the service level agreement (SLA) of DTS does not guarantee service reliability or performance. If CDC needs to be enabled for the tables to be synchronized from the source database, make sure that the tables meet the following requirements. Otherwise, the precheck fails. The value of the srvname field in the sys.sysservers view is the same as the return value of the SERVERPROPERTY function. If the source database is a self-managed SQL Server database, the database owner must be the sa user. If the source database is an ApsaraDB RDS for SQL Server database, the database owner must be the sqlsa user. If the source database is of Enterprise Edition, you must use SQL Server 2008 or later. If the source database is of Standard Edition, you must use SQL Server 2016 SP1 or later. If the source database is of Standard Edition or Enterprise Edition and its version is SQL Server 2017, we recommend that you update the version. If the source database is a read-only instance, you cannot synchronize DDL operations. In hybrid log-based parsing mode, you cannot perform multiple operations to add columns to or remove columns from the source database within 10 minutes. For example, if you execute the following SQL statements within 10 minutes, an error is reported for the task. `ALTER TABLE test_table DROP COLUMN Flag; ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');`
Other limits	Requirements for the objects to be synchronized: DTS supports initial schema synchronization for the following types of objects: database, schema, and table. DTS does not synchronize data of the following types: TEXT, CURSOR, ROWVERSION, HIERACHYID, SQL_VARIANT, SPATIAL GEOMETRY, and SPATIAL GEOGRAPHY. If you set the SQL Server Incremental Synchronization Mode parameter to Incremental Synchronization Based on Logs of Source Database in the Configure Objects and Advanced Settings step, the tables to be synchronized must have clustered indexes that contain primary key columns. In addition, the tables to be synchronized cannot be heap tables, compressed tables, tables without primary keys, or tables with computed columns. Ignore the preceding limits in the hybrid log-based parsing mode. Note For more information about how to view table information, see the How do I view information about heap tables, tables without primary keys, compressed tables, and tables that contain computed columns in an SQL Server database? section of the "FAQ" topic. DTS does not support synchronization of complicated DDL operations. In Incremental Synchronization Based on Logs of Source Database mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a DDL history table named dts_cdc_ddl_history in the source database to ensure that the latency of data synchronization is accurate. In hybrid log-based parsing incremental synchronization mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a DDL history table named dts_cdc_ddl_history and enables CDC for the source database and specific tables. We recommend that you set the maximum number of records per second to 1,000 for the tables for which CDC is enabled in the source database. Before you synchronize data, evaluate the impact of data synchronization on the performance of the source and destination databases. We recommend that you synchronize data during off-peak hours. During initial full data synchronization, DTS uses the read and write resources of the source and destination databases. This may increase the loads of the database servers. During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. After the initial full data synchronization is complete, the size of used tablespace of the destination database is larger than that of the source database. During data synchronization, we recommend that you use only DTS to write data to the destination database. This prevents data inconsistency between the source and destination databases. For example, if you use tools other than DTS to write data to the destination database, data loss may occur in the destination database when you use DMS to perform online DDL operations. If the data synchronization task involves incremental data synchronization, DTS does not allow you to perform the reindexing operation. If you perform the reindexing operation, the data synchronization task may fail, or even data loss may occur. Note DTS cannot synchronize DDL operations related to the primary key of a table for which change data capture (CDC) is enabled. If the number of tables for which CDC is enabled exceeds 1,000 in a data synchronization task, the precheck fails. If DDL statements fail to be executed in the destination database, the DTS task continues to run. You can view the DDL statements that fail to be executed in the task logs. For more information about how to view the task logs, see View task logs. Column names in MySQL databases are not case-sensitive. Therefore, if multiple columns in the source database have the same name that differs only in capitalization, data in the columns are written to the same column in the destination MySQL database during the synchronization. This can cause unexpected synchronization results. After data synchronization is complete, we recommend that you run the `analyze table <Table name>` command to check whether data has been written to the destination table. For example, after a high-availability switchover is triggered in the source MySQL database, data may be written only to the memory. As a result, data loss may occur.
Special cases	If the source instance is an ApsaraDB RDS for SQL Server instance, DTS automatically creates an account named `rdsdt_dtsacct` within the ApsaraDB RDS for SQL Server instance. This account is used for data synchronization. Do not delete this account or change the password of this account when your data synchronization task is running. Otherwise, the task may fail. For more information, see System accounts.

Synchronize data from an SQL Server database to a PolarDB for MySQL cluster

The following table describes the precautions and limits that apply when you synchronize data from an SQL Server database to a PolarDB for MySQL cluster.

Category	Description
Limits on the source database	The tables to be synchronized must have PRIMARY KEY or UNIQUE constraints and all fields must be unique. Otherwise, the destination database may contain duplicate data records. If you select tables as the objects to be synchronized and you need to edit the tables in the destination database, such as renaming tables or columns, you can synchronize up to 5,000 tables in a single data synchronization task. If you run a task to synchronize more than 5,000 tables, a request error occurs. In this case, we recommend that you configure multiple tasks to synchronize the tables in batches or configure a task to synchronize the entire database. A single data synchronization task can synchronize data from up to 10 databases. If you want to synchronize data from more than 10 databases, we recommend that you configure multiple tasks to synchronize the data in batches. Otherwise, the performance and stability of your data synchronization task may be compromised. DTS uses the fn_log function to obtain logs of the source database. However, this function has performance bottlenecks. Therefore, we recommend that you do not clear the logs of the source database before the task is complete. Otherwise, the task may fail. The following requirements for data logs must be met: The data logging feature must be enabled. The backup mode must be set to Full and full physical backup must be performed. If you perform only incremental data synchronization, the data logs of the source database must be stored for more than 24 hours. If you perform both full data synchronization and incremental data synchronization, the data logs of the source database must be stored for at least seven days. Otherwise, DTS may fail to obtain the data logs and the task may fail. In exceptional circumstances, data inconsistency or loss may occur. After full data synchronization is complete, you can set the retention period to more than 24 hours. Make sure that you set the retention period of data logs based on the preceding requirements. Otherwise, the service level agreement (SLA) of DTS does not guarantee service reliability or performance. If CDC needs to be enabled for the tables to be synchronized from the source database, make sure that the tables meet the following requirements. Otherwise, the precheck fails. The value of the srvname field in the sys.sysservers view is the same as the return value of the SERVERPROPERTY function. If the source database is a self-managed SQL Server database, the database owner must be the sa user. If the source database is an ApsaraDB RDS for SQL Server database, the database owner must be the sqlsa user. If the source database is of Enterprise Edition, you must use SQL Server 2008 or later. If the source database is of Standard Edition, you must use SQL Server 2016 SP1 or later. If the source database is of Standard Edition or Enterprise Edition and its version is SQL Server 2017, we recommend that you update the version. If the source database is a read-only instance, you cannot synchronize DDL operations. In hybrid log-based parsing mode, you cannot perform multiple operations to add columns to or remove columns from the source database within 10 minutes. For example, if you execute the following SQL statements within 10 minutes, an error is reported for the task. `ALTER TABLE test_table DROP COLUMN Flag; ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');`
Other limits	Requirements for the objects to be synchronized: DTS supports initial schema synchronization for the following types of objects: database, schema, and table. DTS does not synchronize data of the following types: TEXT, CURSOR, ROWVERSION, HIERACHYID, SQL_VARIANT, SPATIAL GEOMETRY, and SPATIAL GEOGRAPHY. If you set the SQL Server Incremental Synchronization Mode parameter to Incremental Synchronization Based on Logs of Source Database in the Configure Objects and Advanced Settings step, the tables to be synchronized must have clustered indexes that contain primary key columns. In addition, the tables to be synchronized cannot be heap tables, compressed tables, tables without primary keys, or tables with computed columns. Ignore the preceding limits in the hybrid log-based parsing mode. Note For more information about how to view table information, see the How do I view information about heap tables, tables without primary keys, compressed tables, and tables that contain computed columns in an SQL Server database? section of the "FAQ" topic. DTS does not support synchronization of complicated DDL operations. In Incremental Synchronization Based on Logs of Source Database mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a DDL history table named dts_cdc_ddl_history in the source database to ensure that the latency of data synchronization is accurate. In hybrid log-based parsing incremental synchronization mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a DDL history table named dts_cdc_ddl_history and enables CDC for the source database and specific tables. We recommend that you set the maximum number of records per second to 1,000 for the tables for which CDC is enabled in the source database. Before you synchronize data, evaluate the impact of data synchronization on the performance of the source and destination databases. We recommend that you synchronize data during off-peak hours. During initial full data synchronization, DTS uses the read and write resources of the source and destination databases. This may increase the loads of the database servers. During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. After the initial full data synchronization is complete, the size of used tablespace of the destination database is larger than that of the source database. During data synchronization, we recommend that you use only DTS to write data to the destination database. This prevents data inconsistency between the source and destination databases. For example, if you use tools other than DTS to write data to the destination database, data loss may occur in the destination database when you use DMS to perform online DDL operations. If the data synchronization task involves incremental data synchronization, DTS does not allow you to perform the reindexing operation. If you perform the reindexing operation, the data synchronization task may fail, or even data loss may occur. Note DTS cannot synchronize DDL operations related to the primary key of a table for which change data capture (CDC) is enabled. If the number of tables for which CDC is enabled exceeds 1,000 in a data synchronization task, the precheck fails. If DDL statements fail to be executed in the destination database, the DTS task continues to run. You can view the DDL statements that fail to be executed in the task logs. For more information about how to view the task logs, see View task logs.
Special cases	If the source instance is an ApsaraDB RDS for SQL Server instance, DTS automatically creates an account named `rdsdt_dtsacct` within the ApsaraDB RDS for SQL Server instance. This account is used for data synchronization. Do not delete this account or change the password of this account when your data synchronization task is running. Otherwise, the task may fail. For more information, see System accounts.

Synchronize data from an SQL Server database to an AnalyticDB for MySQL cluster

Category	Description
Limits on the source database	The tables to be synchronized must have PRIMARY KEY or UNIQUE constraints and all fields must be unique. Otherwise, the destination database may contain duplicate data records. If you select tables as the objects to be synchronized and you need to edit the tables in the destination database, such as renaming tables or columns, you can synchronize up to 5,000 tables in a single data synchronization task. If you run a task to synchronize more than 5,000 tables, a request error occurs. In this case, we recommend that you configure multiple tasks to synchronize the tables in batches or configure a task to synchronize the entire database. A single data synchronization task can synchronize data from up to 10 databases. If you want to synchronize data from more than 10 databases, we recommend that you configure multiple tasks to synchronize the data in batches. Otherwise, the performance and stability of your data synchronization task may be compromised. DTS uses the fn_log function to obtain logs of the source database. However, this function has performance bottlenecks. Therefore, we recommend that you do not clear the logs of the source database before the task is complete. Otherwise, the task may fail. The following requirements for data logs must be met: The data logging feature must be enabled. The backup mode must be set to Full and full physical backup must be performed. If you perform only incremental data synchronization, the data logs of the source database must be stored for more than 24 hours. If you perform both full data synchronization and incremental data synchronization, the data logs of the source database must be stored for at least seven days. Otherwise, DTS may fail to obtain the data logs and the task may fail. In exceptional circumstances, data inconsistency or loss may occur. After full data synchronization is complete, you can set the retention period to more than 24 hours. Make sure that you set the retention period of data logs based on the preceding requirements. Otherwise, the service level agreement (SLA) of DTS does not guarantee service reliability or performance. If CDC needs to be enabled for the tables to be synchronized from the source database, make sure that the tables meet the following requirements. Otherwise, the precheck fails. The value of the srvname field in the sys.sysservers view is the same as the return value of the SERVERPROPERTY function. If the source database is a self-managed SQL Server database, the database owner must be the sa user. If the source database is an ApsaraDB RDS for SQL Server database, the database owner must be the sqlsa user. If the source database is of Enterprise Edition, you must use SQL Server 2008 or later. If the source database is of Standard Edition, you must use SQL Server 2016 SP1 or later. If the source database is of Standard Edition or Enterprise Edition and its version is SQL Server 2017, we recommend that you update the version. If the source database is a read-only instance, you cannot synchronize DDL operations. In hybrid log-based parsing mode, you cannot perform multiple operations to add columns to or remove columns from the source database within 10 minutes. For example, if you execute the following SQL statements within 10 minutes, an error is reported for the task. `ALTER TABLE test_table DROP COLUMN Flag; ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');`
Other limits	Requirements for the objects to be synchronized: DTS supports initial schema synchronization for the following types of objects: schema, table, view, function, and procedure. Warning The source and destination databases are heterogeneous databases. The data types that they support do not have one-to-one correspondence. In this case, the task may fail or data loss may occur. We recommend that you evaluate the impact of data type conversion on your business. For more information, see Data type mappings for schema synchronization. DTS does not synchronize the schemas of the following objects: assemblies, service brokers, full-text indexes, full-text catalogs, distributed schemas, distributed functions, CLR stored procedures, CLR scalar-valued functions, CLR table-valued functions, internal tables, systems, or aggregate functions. DTS does not synchronize data of the following types: TIMESTAMP, CURSOR, ROWVERSION, HIERACHYID, SQL_VARIANT, SPATIAL GEOMETRY, SPATIAL GEOGRAPHY, and TABLE. DTS does not synchronize tables that contain computed columns. If you set the SQL Server Incremental Synchronization Mode parameter to Incremental Synchronization Based on Logs of Source Database in the Configure Objects and Advanced Settings step, the tables to be synchronized must have clustered indexes that contain primary key columns. In addition, the tables to be synchronized cannot be heap tables, compressed tables, tables without primary keys, or tables with computed columns. Ignore the preceding limits in the hybrid log-based parsing mode. Note For more information about how to view table information, see the How do I view information about heap tables, tables without primary keys, compressed tables, and tables that contain computed columns in an SQL Server database? section of the "FAQ" topic. In Incremental Synchronization Based on Logs of Source Database mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a DDL history table named dts_cdc_ddl_history in the source database to ensure that the latency of data synchronization is accurate. In hybrid log-based parsing incremental synchronization mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a DDL history table named dts_cdc_ddl_history and enables CDC for the source database and specific tables. We recommend that you set the maximum number of records per second to 1,000 for the tables for which CDC is enabled in the source database. Due to the limits of AnalyticDB for MySQL clusters, if the disk space usage of the nodes in an AnalyticDB for MySQL cluster exceeds 80%, the performance of data writes to the destination database is compromised, and the DTS task is delayed. If the disk space usage of the node in the AnalyticDB for MySQL cluster exceeds 90%, data cannot be written to the destination database and error messages are returned. We recommend that you estimate the required disk space based on the objects to be synchronized. Make sure that the destination cluster has sufficient storage space. Before you synchronize data, evaluate the impact of data synchronization on the performance of the source and destination databases. We recommend that you synchronize data during off-peak hours. During initial full data synchronization, DTS uses the read and write resources of the source and destination databases. This may increase the loads of the database servers. During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. After the initial full data synchronization is complete, the size of used tablespace of the destination database is larger than that of the source database. During data synchronization, we recommend that you use only DTS to write data to the destination database. This prevents data inconsistency between the source and destination databases. For example, if you use tools other than DTS to write data to the destination database, data loss may occur in the destination database when you use DMS to perform online DDL operations. If the data synchronization task involves incremental data synchronization, DTS does not allow you to perform the reindexing operation. If you perform the reindexing operation, the data synchronization task may fail, or even data loss may occur. Note DTS cannot synchronize DDL operations related to the primary key of a table for which change data capture (CDC) is enabled. If the number of tables for which CDC is enabled exceeds 1,000 in a data synchronization task, the precheck fails. If DDL statements fail to be executed in the destination database, the DTS task continues to run. You can view the DDL statements that fail to be executed in the task logs. For more information about how to view the task logs, see View task logs.
Special cases	If the source instance is an ApsaraDB RDS for SQL Server instance, DTS automatically creates an account named `rdsdt_dtsacct` within the ApsaraDB RDS for SQL Server instance. This account is used for data synchronization. Do not delete this account or change the password of this account when your data synchronization task is running. Otherwise, the task may fail. For more information, see System accounts.

Synchronize data from an SQL Server database to an AnalyticDB for PostgreSQL instance

The following table describes the precautions and limits that apply when you synchronize data from an SQL Server database to an AnalyticDB for PostgreSQL instance.

Category	Description
Limits on the source database	The tables to be synchronized must have PRIMARY KEY or UNIQUE constraints and all fields must be unique. Otherwise, the destination database may contain duplicate data records. If you select tables as the objects to be synchronized and you need to edit the tables in the destination database, such as renaming tables or columns, you can synchronize up to 5,000 tables in a single data synchronization task. If you run a task to synchronize more than 5,000 tables, a request error occurs. In this case, we recommend that you configure multiple tasks to synchronize the tables in batches or configure a task to synchronize the entire database. A single data synchronization task can synchronize data from up to 10 databases. If you want to synchronize data from more than 10 databases, we recommend that you configure multiple tasks to synchronize the data in batches. Otherwise, the performance and stability of your data synchronization task may be compromised. DTS uses the fn_log function to obtain logs of the source database. However, this function has performance bottlenecks. Therefore, we recommend that you do not clear the logs of the source database before the task is complete. Otherwise, the task may fail. The following requirements for data logs must be met: The data logging feature must be enabled. The backup mode must be set to Full and full physical backup must be performed. If you perform only incremental data synchronization, the data logs of the source database must be stored for more than 24 hours. If you perform both full data synchronization and incremental data synchronization, the data logs of the source database must be stored for at least seven days. Otherwise, DTS may fail to obtain the data logs and the task may fail. In exceptional circumstances, data inconsistency or loss may occur. After full data synchronization is complete, you can set the retention period to more than 24 hours. Make sure that you set the retention period of data logs based on the preceding requirements. Otherwise, the service level agreement (SLA) of DTS does not guarantee service reliability or performance. If CDC needs to be enabled for the tables to be synchronized from the source database, make sure that the tables meet the following requirements. Otherwise, the precheck fails. The value of the srvname field in the sys.sysservers view is the same as the return value of the SERVERPROPERTY function. If the source database is a self-managed SQL Server database, the database owner must be the sa user. If the source database is an ApsaraDB RDS for SQL Server database, the database owner must be the sqlsa user. If the source database is of Enterprise Edition, you must use SQL Server 2008 or later. If the source database is of Standard Edition, you must use SQL Server 2016 SP1 or later. If the source database is of Standard Edition or Enterprise Edition and its version is SQL Server 2017, we recommend that you update the version. If the source database is a read-only instance, you cannot synchronize DDL operations. In hybrid log-based parsing mode, you cannot perform multiple operations to add columns to or remove columns from the source database within 10 minutes. For example, if you execute the following SQL statements within 10 minutes, an error is reported for the task. `ALTER TABLE test_table DROP COLUMN Flag; ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');`
Other limits	Requirements for the objects to be synchronized: DTS supports initial schema synchronization for the following types of objects: schema, table, view, function, and procedure. Warning The source and destination databases are heterogeneous databases. The data types that they support do not have one-to-one correspondence. In this case, the task may fail or data loss may occur. We recommend that you evaluate the impact of data type conversion on your business. For more information, see Data type mappings for schema synchronization. DTS does not synchronize the schemas of the following objects: assemblies, service brokers, full-text indexes, full-text catalogs, distributed schemas, distributed functions, CLR stored procedures, CLR scalar-valued functions, CLR table-valued functions, internal tables, systems, or aggregate functions. DTS does not synchronize data of the following types: TIMESTAMP, CURSOR, ROWVERSION, HIERACHYID, SQL_VARIANT, SPATIAL GEOMETRY, SPATIAL GEOGRAPHY, and TABLE. DTS does not synchronize tables that contain computed columns. If you set the SQL Server Incremental Synchronization Mode parameter to Incremental Synchronization Based on Logs of Source Database in the Configure Objects and Advanced Settings step, the tables to be synchronized must have clustered indexes that contain primary key columns. In addition, the tables to be synchronized cannot be heap tables, compressed tables, tables without primary keys, or tables with computed columns. Ignore the preceding limits in the hybrid log-based parsing mode. Note For more information about how to view table information, see the How do I view information about heap tables, tables without primary keys, compressed tables, and tables that contain computed columns in an SQL Server database? section of the "FAQ" topic. In Incremental Synchronization Based on Logs of Source Database mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a DDL history table named dts_cdc_ddl_history in the source database to ensure that the latency of data synchronization is accurate. In hybrid log-based parsing incremental synchronization mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a DDL history table named dts_cdc_ddl_history and enables CDC for the source database and specific tables. We recommend that you set the maximum number of records per second to 1,000 for the tables for which CDC is enabled in the source database. Before you synchronize data, evaluate the impact of data synchronization on the performance of the source and destination databases. We recommend that you synchronize data during off-peak hours. During initial full data synchronization, DTS uses the read and write resources of the source and destination databases. This may increase the loads of the database servers. During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. After the initial full data synchronization is complete, the size of used tablespace of the destination database is larger than that of the source database. During data synchronization, we recommend that you use only DTS to write data to the destination database. This prevents data inconsistency between the source and destination databases. For example, if you use tools other than DTS to write data to the destination database, data loss may occur in the destination database when you use DMS to perform online DDL operations. If you select tables as the objects to be synchronized, you can modify the mapping relation between columns. If column mapping is used for non-full table synchronization or if the source and destination table schemas are inconsistent, the data is lost for the columns that are missing in the destination database. If the data synchronization task involves incremental data synchronization, DTS does not allow you to perform the reindexing operation. If you perform the reindexing operation, the data synchronization task may fail, or even data loss may occur. Note DTS cannot synchronize DDL operations related to the primary key of a table for which change data capture (CDC) is enabled. If the number of tables for which CDC is enabled exceeds 1,000 in a data synchronization task, the precheck fails.
Special cases	If the source instance is an ApsaraDB RDS for SQL Server instance, DTS automatically creates an account named `rdsdt_dtsacct` within the ApsaraDB RDS for SQL Server instance. This account is used for data synchronization. Do not delete this account or change the password of this account when your data synchronization task is running. Otherwise, the task may fail. For more information, see System accounts.

Synchronize data from an SQL Server database to a DataHub project

The following table describes the precautions and limits that apply when you synchronize data from an SQL Server database to a DataHub project.

Category	Description
Limits on the source database	The tables to be synchronized must have PRIMARY KEY or UNIQUE constraints and all fields must be unique. Otherwise, the destination database may contain duplicate data records. If you select tables as the objects to be synchronized and you need to edit the tables in the destination database, such as renaming tables or columns, you can synchronize up to 5,000 tables in a single data synchronization task. If you run a task to synchronize more than 5,000 tables, a request error occurs. In this case, we recommend that you configure multiple tasks to synchronize the tables in batches or configure a task to synchronize the entire database. A single data synchronization task can synchronize data from up to 10 databases. If you want to synchronize data from more than 10 databases, we recommend that you configure multiple tasks to synchronize the data in batches. Otherwise, the performance and stability of your data synchronization task may be compromised. DTS uses the fn_log function to obtain logs of the source database. However, this function has performance bottlenecks. Therefore, we recommend that you do not clear the logs of the source database before the task is complete. Otherwise, the task may fail. The following requirements for data logs must be met: The data logging feature must be enabled. The backup mode must be set to Full and full physical backup must be performed. If you perform only incremental data synchronization, the data logs of the source database must be stored for more than 24 hours. If you perform both full data synchronization and incremental data synchronization, the data logs of the source database must be stored for at least seven days. Otherwise, DTS may fail to obtain the data logs and the task may fail. In exceptional circumstances, data inconsistency or loss may occur. After full data synchronization is complete, you can set the retention period to more than 24 hours. Make sure that you set the retention period of data logs based on the preceding requirements. Otherwise, the service level agreement (SLA) of DTS does not guarantee service reliability or performance. If CDC needs to be enabled for the tables to be synchronized from the source database, make sure that the tables meet the following requirements. Otherwise, the precheck fails. The value of the srvname field in the sys.sysservers view is the same as the return value of the SERVERPROPERTY function. If the source database is a self-managed SQL Server database, the database owner must be the sa user. If the source database is an ApsaraDB RDS for SQL Server database, the database owner must be the sqlsa user. If the source database is of Enterprise Edition, you must use SQL Server 2008 or later. If the source database is of Standard Edition, you must use SQL Server 2016 SP1 or later. If the source database is of Standard Edition or Enterprise Edition and its version is SQL Server 2017, we recommend that you update the version. In hybrid log-based parsing mode, you cannot perform multiple operations to add columns to or remove columns from the source database within 10 minutes. For example, if you execute the following SQL statements within 10 minutes, an error is reported for the task. `ALTER TABLE test_table DROP COLUMN Flag; ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');`
Other limits	Only incremental synchronization and schema synchronization are supported. Full synchronization is not supported. A single string in the destination DataHub project cannot exceed 2 MB in length. Requirements for the objects to be synchronized: DTS supports initial schema synchronization for the following types of objects: schema, table, view, function, and procedure. Warning The source and destination databases are heterogeneous databases. The data types that they support do not have one-to-one correspondence. In this case, the task may fail or data loss may occur. We recommend that you evaluate the impact of data type conversion on your business. For more information, see Data type mappings for schema synchronization. DTS does not synchronize the schemas of the following objects: assemblies, service brokers, full-text indexes, full-text catalogs, distributed schemas, distributed functions, CLR stored procedures, CLR scalar-valued functions, CLR table-valued functions, internal tables, systems, or aggregate functions. DTS does not synchronize data of the following types: TIMESTAMP, CURSOR, ROWVERSION, HIERACHYID, SQL_VARIANT, SPATIAL GEOMETRY, SPATIAL GEOGRAPHY, and TABLE. DTS does not synchronize tables that contain computed columns. In Incremental Synchronization Based on Logs of Source Database mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a DDL history table named dts_cdc_ddl_history in the source database to ensure that the latency of data synchronization is accurate. In hybrid log-based parsing incremental synchronization mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a DDL history table named dts_cdc_ddl_history and enables CDC for the source database and specific tables. We recommend that you set the maximum number of records per second to 1,000 for the tables for which CDC is enabled in the source database. Before you synchronize data, evaluate the impact of data synchronization on the performance of the source and destination databases. We recommend that you synchronize data during off-peak hours. During initial full data synchronization, DTS uses the read and write resources of the source and destination databases. This may increase the loads of the database servers. During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. After the initial full data synchronization is complete, the size of used tablespace of the destination database is larger than that of the source database. During data synchronization, we recommend that you use only DTS to write data to the destination database. This prevents data inconsistency between the source and destination databases. For example, if you use tools other than DTS to write data to the destination database, data loss may occur in the destination database when you use DMS to perform online DDL operations. If the data synchronization task involves incremental data synchronization, DTS does not allow you to perform the reindexing operation. If you perform the reindexing operation, the data synchronization task may fail, or even data loss may occur. Note DTS cannot synchronize DDL operations related to the primary key of a table for which change data capture (CDC) is enabled. If the number of tables for which CDC is enabled exceeds 1,000 in a data synchronization task, the precheck fails.
Special cases	If the source instance is an ApsaraDB RDS for SQL Server instance, DTS automatically creates an account named `rdsdt_dtsacct` within the ApsaraDB RDS for SQL Server instance. This account is used for data synchronization. Do not delete this account or change the password of this account when your data synchronization task is running. Otherwise, the task may fail. For more information, see System accounts.