This topic describes the precautions and limits when you synchronize data from an SQL Server database, such as a self-managed SQL Server database and 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, the cascade and delete operations of the source database are not synchronized to the following 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

The following table describes the precautions and limits.
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 want to edit the tables (such as renaming tables or columns) in the destination database, 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 to up to 10 databases. If you want to synchronize data to more than 10 databases, we recommend that you configure multiple tasks to synchronize the data. Otherwise, the performance and stability of your data synchronization task may be compromised.
  • DTS does not synchronize tables for memory optimization.
  • 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 logical backup must be performed.
    • If you perform only incremental data synchronization, the data logs of the source 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 must be stored for at least seven days. After full data synchronization is complete, you can set the retention period to more than 24 hours. Otherwise, DTS may fail to obtain the data logs and the task may fail. In extreme cases, data may be inconsistent or lost. Make sure that you set the retention period of data logs based on the preceding requirements. Otherwise, the service reliability or performance stated in the Service Level Agreement (SLA) of DTS may not be achieved.

  • If Change Data Capture (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.
Other limits
  • DTS does not synchronize data of the following types: 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 SQL Server Incremental Synchronization Mode to Incremental Synchronization Based on Logs of Source Database in the Configure Objects and Advanced Settings step, the tables to synchronize must have clustered indexes that contain primary key columns. In addition, the table to synchronize cannot be compressed tables and cannot contain computed columns. Ignore the preceding limits in the mixed log-based parsing mode.
  • In the Incremental Synchronization Based on Logs of Source Database mode, DTS adds a heartbeat table named dts_log_heart_beat to the source database to ensure that the synchronization latency of incremental data is accurate. In the mixed log-based parsing incremental synchronization mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a storage table named dts_cdc_ddl_history by using DDL and enables CDC for the database and some tables. We recommend that you set the upper limit of the number of requests 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 read and write resources of the source and destination databases. This may increase the loads on the database servers.
  • During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. Therefore, after initial full data synchronization is complete, the size of the used tablespace of the destination database is larger than that of the source database.
  • We recommend that you do not use gh-ost or pt-online-schema-change to perform DDL operations on source tables during data synchronization. Otherwise, data synchronization may fail.
  • 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. 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.
  • If the number of tables for which CDC is enabled is greater than 1,000 in a data synchronization task, the precheck fails.
  • To perform incremental data synchronization, you must disable the trigger in the destination database. Otherwise, the data synchronization task fails.

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

The following table describes the precautions and limits when you synchronize data from an ApsaraDB RDS for SQL Server database 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 want to edit the tables (such as renaming tables or columns) in the destination database, 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 to up to 10 databases. If you want to synchronize data to more than 10 databases, we recommend that you configure multiple tasks to synchronize the data. Otherwise, the performance and stability of your data synchronization task may be compromised.
  • 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 logical backup must be performed.
    • If you perform only incremental data synchronization, the data logs of the source 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 must be stored for at least seven days. After full data synchronization is complete, you can set the retention period to more than 24 hours. Otherwise, DTS may fail to obtain the data logs and the task may fail. In extreme cases, data may be inconsistent or lost. Make sure that you set the retention period of data logs based on the preceding requirements. Otherwise, the service reliability or performance stated in the Service Level Agreement (SLA) of DTS may not be achieved.

  • If Change Data Capture (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.
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 SQL Server Incremental Synchronization Mode to Incremental Synchronization Based on Logs of Source Database in the Configure Objects and Advanced Settings step, the tables to synchronize must have clustered indexes that contain primary key columns. In addition, the table to synchronize cannot be compressed tables and cannot contain computed columns. Ignore the preceding limits in the mixed log-based parsing mode.
  • DTS does not support complicated DDL operations.
  • In the Incremental Synchronization Based on Logs of Source Database mode, DTS adds a heartbeat table named dts_log_heart_beat to the source database to ensure that the synchronization latency of incremental data is accurate. In the mixed log-based parsing incremental synchronization mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a storage table named dts_cdc_ddl_history by using DDL and enables CDC for the database and some tables. We recommend that you set the upper limit of the number of requests 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 read and write resources of the source and destination databases. This may increase the loads on the database servers.
  • During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. Therefore, after initial full data synchronization is complete, the size of the used tablespace of the destination database is larger than that of the source database.
  • We recommend that you do not use gh-ost or pt-online-schema-change to perform DDL operations on source tables during data synchronization. Otherwise, data synchronization may fail.
  • 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. 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.
  • If the number of tables for which CDC is enabled is greater than 1,000 in a data synchronization task, the precheck fails.

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

The following table describes the precautions and limits.

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 want to edit the tables (such as renaming tables or columns) in the destination database, 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 to up to 10 databases. If you want to synchronize data to more than 10 databases, we recommend that you configure multiple tasks to synchronize the data. Otherwise, the performance and stability of your data synchronization task may be compromised.
  • 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 logical backup must be performed.
    • If you perform only incremental data synchronization, the data logs of the source 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 must be stored for at least seven days. After full data synchronization is complete, you can set the retention period to more than 24 hours. Otherwise, DTS may fail to obtain the data logs and the task may fail. In extreme cases, data may be inconsistent or lost. Make sure that you set the retention period of data logs based on the preceding requirements. Otherwise, the service reliability or performance stated in the Service Level Agreement (SLA) of DTS may not be achieved.

  • If Change Data Capture (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.
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 SQL Server Incremental Synchronization Mode to Incremental Synchronization Based on Logs of Source Database in the Configure Objects and Advanced Settings step, the tables to synchronize must have clustered indexes that contain primary key columns. In addition, the table to synchronize cannot be compressed tables and cannot contain computed columns. Ignore the preceding limits in the mixed log-based parsing mode.
  • DTS does not support complicated DDL operations.
  • In the Incremental Synchronization Based on Logs of Source Database mode, DTS adds a heartbeat table named dts_log_heart_beat to the source database to ensure that the synchronization latency of incremental data is accurate. In the mixed log-based parsing incremental synchronization mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a storage table named dts_cdc_ddl_history by using DDL and enables CDC for the database and some tables. We recommend that you set the upper limit of the number of requests 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 read and write resources of the source and destination databases. This may increase the loads on the database servers.
  • During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. Therefore, after initial full data synchronization is complete, the size of the used tablespace of the destination database is larger than that of the source database.
  • We recommend that you do not use gh-ost or pt-online-schema-change to perform DDL operations on source tables during data synchronization. Otherwise, data synchronization may fail.
  • 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. 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.
  • If the number of tables for which CDC is enabled is greater than 1,000 in a data synchronization task, the precheck fails.

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 want to edit the tables (such as renaming tables or columns) in the destination database, 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 to up to 10 databases. If you want to synchronize data to more than 10 databases, we recommend that you configure multiple tasks to synchronize the data. Otherwise, the performance and stability of your data synchronization task may be compromised.
  • 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 logical backup must be performed.
    • If you perform only incremental data synchronization, the data logs of the source 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 must be stored for at least seven days. After full data synchronization is complete, you can set the retention period to more than 24 hours. Otherwise, DTS may fail to obtain the data logs and the task may fail. In extreme cases, data may be inconsistent or lost. Make sure that you set the retention period of data logs based on the preceding requirements. Otherwise, the service reliability or performance stated in the Service Level Agreement (SLA) of DTS may not be achieved.

  • If Change Data Capture (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.
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 ApsaraDB RDS for SQL Server and AnalyticDB for PostgreSQL are heterogeneous databases. The data types that they support do not have one-to-one correspondence. 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 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 SQL Server Incremental Synchronization Mode to Incremental Synchronization Based on Logs of Source Database in the Configure Objects and Advanced Settings step, the tables to synchronize must have clustered indexes that contain primary key columns. In addition, the table to synchronize cannot be compressed tables and cannot contain computed columns. Ignore the preceding limits in the mixed log-based parsing mode.
  • In the Incremental Synchronization Based on Logs of Source Database mode, DTS adds a heartbeat table named dts_log_heart_beat to the source database to ensure that the synchronization latency of incremental data is accurate. In the mixed log-based parsing incremental synchronization mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a storage table named dts_cdc_ddl_history by using DDL and enables CDC for the database and some tables. We recommend that you set the upper limit of the number of requests 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, if the disk space usage of the nodes in an AnalyticDB for MySQL cluster reaches 80%, the performance of data writing to the destination database is compromised and the DTS task is delayed. If the usage reaches 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. You must 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 read and write resources of the source and destination databases. This may increase the loads on the database servers.
  • During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. Therefore, after initial full data synchronization is complete, the size of the used tablespace of the destination database is larger than that of the source database.
  • We recommend that you do not use gh-ost or pt-online-schema-change to perform DDL operations on source tables during data synchronization. Otherwise, data synchronization may fail.
  • 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. 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.
  • If the number of tables for which CDC is enabled is greater than 1,000 in a data synchronization task, the precheck fails.

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

The following table describes the precautions and limits.
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 want to edit the tables (such as renaming tables or columns) in the destination database, 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 to up to 10 databases. If you want to synchronize data to more than 10 databases, we recommend that you configure multiple tasks to synchronize the data. Otherwise, the performance and stability of your data synchronization task may be compromised.
  • 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 logical backup must be performed.
    • If you perform only incremental data synchronization, the data logs of the source 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 must be stored for at least seven days. After full data synchronization is complete, you can set the retention period to more than 24 hours. Otherwise, DTS may fail to obtain the data logs and the task may fail. In extreme cases, data may be inconsistent or lost. Make sure that you set the retention period of data logs based on the preceding requirements. Otherwise, the service reliability or performance stated in the Service Level Agreement (SLA) of DTS may not be achieved.

  • If Change Data Capture (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.
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 ApsaraDB RDS for SQL Server and AnalyticDB for PostgreSQL are heterogeneous databases. The data types that they support do not have one-to-one correspondence. 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 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 SQL Server Incremental Synchronization Mode to Incremental Synchronization Based on Logs of Source Database in the Configure Objects and Advanced Settings step, the tables to synchronize must have clustered indexes that contain primary key columns. In addition, the table to synchronize cannot be compressed tables and cannot contain computed columns. Ignore the preceding limits in the mixed log-based parsing mode.
  • In the Incremental Synchronization Based on Logs of Source Database mode, DTS adds a heartbeat table named dts_log_heart_beat to the source database to ensure that the synchronization latency of incremental data is accurate. In the mixed log-based parsing incremental synchronization mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a storage table named dts_cdc_ddl_history by using DDL and enables CDC for the database and some tables. We recommend that you set the upper limit of the number of requests 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 read and write resources of the source and destination databases. This may increase the loads on the database servers.
  • During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. Therefore, after initial full data synchronization is complete, the size of the used tablespace of the destination database is larger than that of the source database.
  • We recommend that you do not use gh-ost or pt-online-schema-change to perform DDL operations on source tables during data synchronization. Otherwise, data synchronization may fail.
  • 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. 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.
  • If the number of tables for which CDC is enabled is greater than 1,000 in a data synchronization task, the precheck fails.

Synchronize data from an SQL Server database to DataHub

The following table describes the precautions and limits.
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 want to edit the tables (such as renaming tables or columns) in the destination database, 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 to up to 10 databases. If you want to synchronize data to more than 10 databases, we recommend that you configure multiple tasks to synchronize the data. Otherwise, the performance and stability of your data synchronization task may be compromised.
  • 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 logical backup must be performed.
    • If you perform only incremental data synchronization, the data logs of the source 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 must be stored for at least seven days. After full data synchronization is complete, you can set the retention period to more than 24 hours. Otherwise, DTS may fail to obtain the data logs and the task may fail. In extreme cases, data may be inconsistent or lost. Make sure that you set the retention period of data logs based on the preceding requirements. Otherwise, the service reliability or performance stated in the Service Level Agreement (SLA) of DTS may not be achieved.

  • If Change Data Capture (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.
Other limits
  • Only incremental synchronization and schema synchronization are supported. Full synchronization is not supported.
  • 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 ApsaraDB RDS for SQL Server and AnalyticDB for PostgreSQL are heterogeneous databases. The data types that they support do not have one-to-one correspondence. 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 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 the Incremental Synchronization Based on Logs of Source Database mode, DTS adds a heartbeat table named dts_log_heart_beat to the source database to ensure that the synchronization latency of incremental data is accurate. In the mixed log-based parsing incremental synchronization mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a storage table named dts_cdc_ddl_history by using DDL and enables CDC for the database and some tables. We recommend that you set the upper limit of the number of requests 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 read and write resources of the source and destination databases. This may increase the loads on the database servers.
  • During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. Therefore, after initial full data synchronization is complete, the size of the used tablespace of the destination database is larger than that of the source database.
  • We recommend that you do not use gh-ost or pt-online-schema-change to perform DDL operations on source tables during data synchronization. Otherwise, data synchronization may fail.
  • 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. 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.
  • If the number of tables for which CDC is enabled is greater than 1,000 in a data synchronization task, the precheck fails.