Usage Instructions

Last updated: 2025-12-03 18:02:36

Usage Instructions

Last updated: 2025-12-03 18:02:36

Feasibility of Data Synchronization
Category
Description
Source/Target database type
Source database type:
Self-built databases (IDC self-built databases and self-built databases on CVM): SQL Server 2008 R2, 2012, 2014, 2016, 2017, 2019, and 2022.
Databases of third-party vendors (such as Alibaba, AWS, and Google): SQL Server 2008 R2, 2012, 2014, 2016, 2017, 2019, and 2022.
TencentDB for SQL Server (same-account and cross-account): SQL Server 2008 R2, 2012, 2014, 2016, 2017, 2019, and 2022.
Target database type:
TencentDB for SQL Server (same-account and cross-account): SQL Server 2008 R2, 2012, 2014, 2016, 2017, 2019, and 2022.
Synchronization type
Synchronization of structures and full and incremental data is supported.
Synchronization object
Synchronization of database-level data, table-level (user table) data, database and table structures, primary keys, and indexes is supported.
Synchronization of views, storage process, custom functions, custom data types, plan guides, and architectures is supported.
Synchronization of triggers is not supported.
Data type
Supported data types:
Integer types: tinyint, smallint, int, and bigint.
Floating-point number types: real, float, decimal, and numeric.
Bit type: bit.
Character types: char, varchar, nchar, and nvarchar.
Date and time types: date, time, datetime, datetime2, smalldatetime, and datetimeoffset.
Binary types: binary and varbinary.
Unique identifier: uniqueidentifier.
Currency type: money.
Large object types: text and ntext.
Unsupported data types:
Currency type: smallmoney (bulk insert limitations).
Large object type: image.
MAX types: nvarchar(max) and varbinary(max).
Special types: sql_variant, hierarchyid, geometry, geography, xml, and timestamp.
Impact on the source database
When Data Transfer Service (DTS) is used to perform data synchronization, it occupies certain resources of the source instance, which may cause the source instance load to increase and add pressure to the database. If your database configuration is low, it is recommended to perform synchronization during off-peak hours.
Impacts related to enabling CDC:
A brief table lock is caused by enabling CDC since the creation of CDC-related tables and SQL Agent jobs results in certain resource consumption.
When CDC is enabled for a long time, note that the incremental data stored by the CDC component is retained for 3 days by default, which may consume a large amount of storage space. It is recommended to use the command exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; to set the retention time.
Enabling CDC for a long time can also impact transaction log truncation.
Due to CDC limitations, if the number of tables exceeds 1000 and the number of databases exceeds 10, it is recommended to use the data migration or backup and recovery capability.
The default maximum size for a single field that CDC jobs can handle is 64 KB. If any table with CDC enabled contains a single field larger than 64 KB, the command exec sp_configure 'max text repl size', -1; must be used in advance to adjust the source database configurations.
Impact on the target database
It is recommended not to perform data write operations on the target database when a data synchronization task is executed, as this may affect the progress of the synchronization task and impose the risk of data inconsistency.
Permission
Both the source and target databases must have SA permissions. If the source and target databases use TencentDB for SQL Server instances, this permission requirement can be ignored.
Supported SQL statements
DML: INSERT, UPDATE, and DELETE.
Limitation
The data synchronization feature is not suitable for scenarios with a large volume of DML operations within a short period due to the latency.
Foreign key migration is not supported. If tables contain foreign keys, they will not be migrated to the target instance. It is recommended to recreate foreign keys after migration.
DDL operations are not supported. Modifying table structures or deleting tables may cause the synchronization task to fail.
Structure migration cannot be performed independently, and structure and data are required to be migrated together.
Do not modify or delete user information (including usernames, passwords, and permissions) and port numbers in the source and target databases during synchronization, as this may cause the synchronization task to fail.
If the source table is large with long-running transactions during the full synchronization phase, it is recommended to enable READ_COMMITTED_SNAPSHOT.
Supported Capabilities
Feature Category
Sub-feature or Description
Supported Capability
Resumable transfer
-
Resumable transfer is partially supported during the full export (it is not supported for structure export or export of tables without primary keys).
Resumable transfer is supported for full and incremental imports.
Synchronization direction
One-to-one
Supported.
﻿
Many-to-one (multiple instances merged to one/multiple tables merged to one)
Not supported.
﻿
One-to-many
Not supported.
﻿
Bidirectional synchronization
Not supported.
﻿
Synchronization within the same instance (only one-to-one mode is supported)
Not supported.
HA Switch
A switch of the source SQL Server will cause the DTS synchronization task to fail.
Business Impact
When DTS is used to perform full data synchronization, it reads all data of the source database once, which increases the load on the source database. If your database specification is low, it is recommended to execute the synchronization task during off-peak hours or reduce the DTS rate before the task is started.
The impact varies depending on the source database specifications. For example, taking the specification of 8 cores and 16 GB as an example, DTS uses 8 concurrent threads (adjustable) by default. Assuming that there is no network bottleneck, the impact of DTS tasks on the performance of the source database is as follows.
DTS full export phase: The tasks consume about 18%–45% CPU on the source database, increase about 40–60 MB/s of query pressure, and occupy about 8 active session connections.
DTS incremental export phase: The tasks put minimal pressure on the source database and establish only one database connection to continuously listen to the CDC tables of the source database. 
Business Migration After Data Synchronization
DTS provides a data consistency check feature for data synchronization. This feature employs the static data consistency check to help users verify data consistency and facilitate a smoother business migration. For details, see Business Migration Description After Data Synchronization.

Was this page helpful?

You can also Contact Sales or Submit a Ticket for help.

Yes

Feedback

Category	Description
Source/Target database type	Source database type: Self-built databases (IDC self-built databases and self-built databases on CVM): SQL Server 2008 R2, 2012, 2014, 2016, 2017, 2019, and 2022. Databases of third-party vendors (such as Alibaba, AWS, and Google): SQL Server 2008 R2, 2012, 2014, 2016, 2017, 2019, and 2022. TencentDB for SQL Server (same-account and cross-account): SQL Server 2008 R2, 2012, 2014, 2016, 2017, 2019, and 2022. Target database type: TencentDB for SQL Server (same-account and cross-account): SQL Server 2008 R2, 2012, 2014, 2016, 2017, 2019, and 2022.
Synchronization type	Synchronization of structures and full and incremental data is supported.
Synchronization object	Synchronization of database-level data, table-level (user table) data, database and table structures, primary keys, and indexes is supported. Synchronization of views, storage process, custom functions, custom data types, plan guides, and architectures is supported. Synchronization of triggers is not supported.
Data type	Supported data types: Integer types: tinyint, smallint, int, and bigint. Floating-point number types: real, float, decimal, and numeric. Bit type: bit. Character types: char, varchar, nchar, and nvarchar. Date and time types: date, time, datetime, datetime2, smalldatetime, and datetimeoffset. Binary types: binary and varbinary. Unique identifier: uniqueidentifier. Currency type: money. Large object types: text and ntext. Unsupported data types: Currency type: smallmoney (bulk insert limitations). Large object type: image. MAX types: nvarchar(max) and varbinary(max). Special types: sql_variant, hierarchyid, geometry, geography, xml, and timestamp.
Impact on the source database	When Data Transfer Service (DTS) is used to perform data synchronization, it occupies certain resources of the source instance, which may cause the source instance load to increase and add pressure to the database. If your database configuration is low, it is recommended to perform synchronization during off-peak hours. Impacts related to enabling CDC: A brief table lock is caused by enabling CDC since the creation of CDC-related tables and SQL Agent jobs results in certain resource consumption. When CDC is enabled for a long time, note that the incremental data stored by the CDC component is retained for 3 days by default, which may consume a large amount of storage space. It is recommended to use the command `exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>;` to set the retention time. Enabling CDC for a long time can also impact transaction log truncation. Due to CDC limitations, if the number of tables exceeds 1000 and the number of databases exceeds 10, it is recommended to use the data migration or backup and recovery capability. The default maximum size for a single field that CDC jobs can handle is 64 KB. If any table with CDC enabled contains a single field larger than 64 KB, the command `exec sp_configure 'max text repl size', -1;` must be used in advance to adjust the source database configurations.
Impact on the target database	It is recommended not to perform data write operations on the target database when a data synchronization task is executed, as this may affect the progress of the synchronization task and impose the risk of data inconsistency.
Permission	Both the source and target databases must have SA permissions. If the source and target databases use TencentDB for SQL Server instances, this permission requirement can be ignored.
Supported SQL statements	DML: INSERT, UPDATE, and DELETE.
Limitation	The data synchronization feature is not suitable for scenarios with a large volume of DML operations within a short period due to the latency. Foreign key migration is not supported. If tables contain foreign keys, they will not be migrated to the target instance. It is recommended to recreate foreign keys after migration. DDL operations are not supported. Modifying table structures or deleting tables may cause the synchronization task to fail. Structure migration cannot be performed independently, and structure and data are required to be migrated together. Do not modify or delete user information (including usernames, passwords, and permissions) and port numbers in the source and target databases during synchronization, as this may cause the synchronization task to fail. If the source table is large with long-running transactions during the full synchronization phase, it is recommended to enable READ_COMMITTED_SNAPSHOT.

Feature Category	Sub-feature or Description	Supported Capability
Resumable transfer	-	Resumable transfer is partially supported during the full export (it is not supported for structure export or export of tables without primary keys). Resumable transfer is supported for full and incremental imports.
Synchronization direction	One-to-one	Supported.
		Many-to-one (multiple instances merged to one/multiple tables merged to one)	Not supported.
		One-to-many	Not supported.
		Bidirectional synchronization	Not supported.
		Synchronization within the same instance (only one-to-one mode is supported)	Not supported.

tencent cloud

Feasibility of Data Synchronization

Supported Capabilities

HA Switch

Business Impact

Business Migration After Data Synchronization