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, read the precautions and limits before you configure the task.

Scenarios of synchronizing data from an SQL Server database

You can view the precautions and limits based on the following synchronization scenarios:

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 need to edit tables (such as rename tables or columns), up to 1,000 tables can be synchronized in a single data synchronization task. If you run a task to synchronize more than 1,000 tables, a request error occurs. In this case, we recommend that you split the tables to be synchronized, configure multiple tasks to synchronize the tables, or call DTS API operations to configure tasks.
  • 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.
    • Data logs are retained for at least 7 days during full data synchronization. You can wait until full data synchronization is complete, and then clear the data logs generated in the source database after the DTS task is run.
      Note To ensure data security, the DTS server stores only 50 GB of data logs or the data logs for the last 24 hours. If the limit is exceeded, the DTS server automatically clears the cached logs.
      Warning If you clear the data logs of the source database during full data synchronization, the data synchronization task may fail. For example, full data synchronization takes more than 24 hours due to the large data volume in the source database and abnormal writing in the destination database. In this case, if the data logs of the source database are cleared during full data synchronization, DTS cannot obtain the data logs generated 24 hours ago. Therefore, the data synchronization task may fail.
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 be synchronized must have clustered indexes that contain primary key columns.
  • To ensure that the latency of data synchronization is accurate, DTS adds a heartbeat table to the source database. The name of the heartbeat table is dts_log_heart_beat.
  • 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 full data synchronization, DTS uses read and write resources of the source and destination databases. This may increase the loads of the database servers.
  • During full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. After full data synchronization is complete, the 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 data definition language (DDL) operations on source tables during data synchronization. Otherwise, data synchronization may fail.

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

The following table describes the precautions and limits 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 tables (such as rename tables or columns), up to 1,000 tables can be synchronized in a single data synchronization task. If you run a task to synchronize more than 1,000 tables, a request error occurs. In this case, we recommend that you split the tables to be synchronized, configure multiple tasks to synchronize the tables, or call DTS API operations to configure tasks.
  • 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.
    • Data logs are retained for at least 7 days during full data synchronization. You can wait until full data synchronization is complete, and then clear the data logs generated in the source database after the DTS task is run.
      Note To ensure data security, the DTS server stores only 50 GB of data logs or the data logs for the last 24 hours. If the limit is exceeded, the DTS server automatically clears the cached logs.
      Warning If you clear the data logs of the source database during full data synchronization, the data synchronization task may fail. For example, full data synchronization takes more than 24 hours due to the large data volume in the source database and abnormal writing in the destination database. In this case, if the data logs of the source database are cleared during full data synchronization, DTS cannot obtain the data logs generated 24 hours ago. Therefore, the data synchronization task may fail.
Other limits
  • Requirements for the objects to be synchronized:
    • DTS supports schema synchronization for the following types of objects: database, schema, and table. \
    • 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 be synchronized must have clustered indexes that contain primary key columns.
  • DDL operations cannot be synchronized. If you perform DDL operations in the source database during data synchronization, the data synchronization task fails. If you need to perform DDL operations, we recommend that you perform the operations in the destination database and then perform the operations in the source database.
  • To ensure that the latency of data synchronization is accurate, DTS adds a heartbeat table to the source database. The name of the heartbeat table is dts_log_heart_beat.
  • 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 full data synchronization, DTS uses read and write resources of the source and destination databases. This may increase the loads of the database servers.
  • During full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. After full data synchronization is complete, the 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 data definition language (DDL) operations on source tables during data synchronization. Otherwise, data synchronization may fail.

Synchronize data from an SQL Server database to an AnalyticDB 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 need to edit tables (such as rename tables or columns), up to 1,000 tables can be synchronized in a single data synchronization task. If you run a task to synchronize more than 1,000 tables, a request error occurs. In this case, we recommend that you split the tables to be synchronized, configure multiple tasks to synchronize the tables, or call DTS API operations to configure tasks.
  • 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.
    • Data logs are retained for at least 7 days during full data synchronization. You can wait until full data synchronization is complete, and then clear the data logs generated in the source database after the DTS task is run.
      Note To ensure data security, the DTS server stores only 50 GB of data logs or the data logs for the last 24 hours. If the limit is exceeded, the DTS server automatically clears the cached logs.
      Warning If you clear the data logs of the source database during full data synchronization, the data synchronization task may fail. For example, full data synchronization takes more than 24 hours due to the large data volume in the source database and abnormal writing in the destination database. In this case, if the data logs of the source database are cleared during full data synchronization, DTS cannot obtain the data logs generated 24 hours ago. Therefore, the data synchronization task may fail.
Other limits
  • Requirements for the objects to be synchronized:
    • DTS supports schema synchronization for the following types of objects: schema, table, view, function, and procedure.
      Warning SQL Server and AnalyticDB for MySQL are heterogeneous databases. Their data types 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 be synchronized must have clustered indexes that contain primary key columns.
  • To ensure that the latency of data synchronization is accurate, DTS adds a heartbeat table to the source database. The name of the heartbeat table is dts_log_heart_beat.
  • 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 that you want to synchronize. You must ensure 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 full data synchronization, DTS uses read and write resources of the source and destination databases. This may increase the loads of the database servers.
  • During full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. After full data synchronization is complete, the 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 data definition language (DDL) operations on source tables during data synchronization. Otherwise, data synchronization may fail.

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

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 need to edit tables (such as rename tables or columns), up to 1,000 tables can be synchronized in a single data synchronization task. If you run a task to synchronize more than 1,000 tables, a request error occurs. In this case, we recommend that you split the tables to be synchronized, configure multiple tasks to synchronize the tables, or call DTS API operations to configure tasks.
  • 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.
    • Data logs are retained for at least 7 days during full data synchronization. You can wait until full data synchronization is complete, and then clear the data logs generated in the source database after the DTS task is run.
      Note To ensure data security, the DTS server stores only 50 GB of data logs or the data logs for the last 24 hours. If the limit is exceeded, the DTS server automatically clears the cached logs.
      Warning If you clear the data logs of the source database during full data synchronization, the data synchronization task may fail. For example, full data synchronization takes more than 24 hours due to the large data volume in the source database and abnormal writing in the destination database. In this case, if the data logs of the source database are cleared during full data synchronization, DTS cannot obtain the data logs generated 24 hours ago. Therefore, the data synchronization task may fail.
Other limits
  • Requirements for the objects to be synchronized:
    • DTS supports schema synchronization for the following types of objects: schema, table, view, function, and procedure.
      Warning SQL Server and AnalyticDB for MySQL are heterogeneous databases. Their data types 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 be synchronized must have clustered indexes that contain primary key columns.
  • To ensure that the latency of data synchronization is accurate, DTS adds a heartbeat table to the source database. The name of the heartbeat table is dts_log_heart_beat.
  • 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 full data synchronization, DTS uses read and write resources of the source and destination databases. This may increase the loads of the database servers.
  • During full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. After full data synchronization is complete, the 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 data definition language (DDL) operations on source tables during data synchronization. Otherwise, data synchronization may fail.