DataInLong provides SQL Server reading and writing capabilities. This article introduces the pre-environment configuration for real-time data synchronization using SQL Server and the current capability support.
Supported Versions
Currently, DataInLong supports SQL Server single table and database-level real-time reading and single table writing. Using real-time synchronization capabilities requires adherence to the following version limitations:
Data Source Type
Edition
SQL Server
2008,2008R2,2012,2014,2016,2017,2019
Use Limits
SQL Server data source can only correspond to one database, so a real-time database-level task only supports synchronizing tables under one database. Cross-database table selection is not supported.
Tables without primary keys may have data duplication since exactly once cannot be guaranteed. Therefore, it is best to ensure the table has a primary key for real-time sync tasks.
SQL Server currently only supports automatic table creation in the full volume phase; the incremental phase is temporarily unsupported.
@role_name=NULL,-- role_name specifies a role. You can add users to this role to grant SELECT permission for the capture column of the source table. Users in the sysadmin or db_owner roles can also access the specified change table. Set the value of @role_name to NULL to allow only members of sysadmin or db_owner to have full access to the captured information.
@supports_net_changes=0
END
Replace 'mytable' with the actual table name that needs CDC configuration.
2. Execute the following command to check the execution result. A result of "1" indicates success.
SELECT is_tracked_by_cdc FROM sys.tablesWHERE name='mytable'
Other Operations
If the table structure changes, you need to reactivate the CDC configuration. The configuration steps are as follows:
1. First, disable the CDC configuration, and fill in the schema and name according to the actual situation.
EXEC sys.sp_cdc_disable_table
@source_schema= N'dbo',
@source_name='mytable',
@capture_instance='all'
2. Reactivate the CDC configuration, and fill in the schema and name according to the actual situation.
@role_name=NULL,-- role_name specifies a role. You can add users to this role to grant SELECT permission for the capture column of the source table. Users in the sysadmin or db_owner roles can also access the specified change table. Set the value of @role_name to NULL to allow only members of sysadmin or db_owner to have full access to the captured information.
@supports_net_changes=0
END
Full Database Read Node Configuration
Data source settings
Parameter
Description
Data Denstination
Select the SQL Server data source to be synchronized.
Source Table
SQL Server only supports specifying tables and databases; it does not support all databases and tables
Specify Table: Under this option, you need to specify the exact table name. Once set, the task will only synchronize the specified table and can only select tables within one database; cross-database selection is not supported. If you need to add more tables for synchronization, you must pause and restart the task.
Specify Database: Under this option, you need to specify the exact database name and a table name regular expression. The database name must be the same, and multiple databases are not supported. Once set, the newly added tables that match the table name expression will be synchronized to the target during task operation.
Read Mode
Full + Increment: Data synchronization is divided into full and increment phases. After the full phase is completed, the task enters the increment phase. The full phase will synchronize historical data in the database, and the incremental phase starts synchronizing from the binlog cdc location after the task starts.
Increment: Synchronize data only from the binlog cdc location after the task starts.
Consistency Semantics
Only represents the consistency semantics of the reading end. Supports At-least-once and Exactly-once.
At-least-once: Data may be read more than once, relying on the destination end's deduplication to ensure data consistency. Suitable for scenarios with large data volumes in the full phase and using non-numeric primary keys, with high synchronization performance requirements.
Exactly-once: Data is read strictly once, with some performance losses. Tables without a primary key and unique index column are not supported. Suitable for general scenarios where the source table has a numeric primary key or unique index column.
The current version's two modes are state-incompatible. If the mode is changed after task submission, stateful restart is not supported.
Advanced Settings (optional)
You can configure parameters according to business needs.
Single Table Read Node Configuration
Parameter
Description
Data Source
Select the SQL Server data source.
Database
Support selecting or manually entering the name of the required database.
By default, the database bound to the data source is used as the default database. Other databases need to be manually entered.
If the data source network is not connected and the database information cannot be fetched directly, you can manually enter the database name. Data synchronization can still be performed when the Data Integration network is connected.
Table
Support selecting or manually entering the table name to be read.
In the case of table partitioning, you can support selecting or entering multiple table names at the SQL Server source end, ensuring the tables have consistent structures.
In the case of partitioned tables, you can configure the table index range. For example, 'table_[0-99]' indicates reading 'table_0','table_1','table_2' up to 'table_99'. If your table number suffix length is consistent, such as 'table_000','table_001','table_002' up to 'table_999', you can configure it as '"table": ["table_00[0-9]", "table_0[10-99]", "table_[100-999]"]'.
If the data source network is not connected and the table information cannot be fetched directly, you can manually enter the table name. Data synchronization can still be performed when the Data Integration network is connected.
Add Shared Database/Table
Apply to database sharding scenarios. After clicking, you can configure multiple data sources, databases, and table information. Ensure all table structures are consistent for database sharding and partitioning scenarios. The task configuration will default to displaying and using the structure of the first table for data retrieval.
Read Mode
Full + Increment: Data synchronization is divided into full and increment phases. After the full phase is completed, the task enters the increment phase. The full phase will synchronize historical data in the database, and the incremental phase starts synchronizing from the binlog cdc location after the task starts.
Increment Only: Only synchronize data from the binlog cdc position after the task starts.
Filter Operation
Once set, data for the specified operation type will not be synchronized; supports filtering insert, update, and delete operations.
Time Zone
Set the timezone for log time, default is Shanghai.
Parameter (optional)
You can configure parameters according to business needs.
Single Table Writing Node Configuration
Parameter
Description
Data Denstination
SQL Server Data Source to be written to.
Database
Supports selection or manual input of the database name to write to
If the data source network is not connected and the database information cannot be fetched directly, you can manually enter the database name. Data synchronization can still be performed when the Data Integration network is connected.
Schema
Support selecting or manually entering the SQL Server schema to be written.
Table
Support selection or manual entry of the table name to be written to.
If the data source network is not connected and the table information cannot be fetched directly, you can manually enter the table name. Data synchronization can still be performed when the Data Integration network is connected.
Primary key
Select a field as the primary key for the target table.
Advanced Settings (Optional)
You can configure parameters according to business needs.
Log collection write node configuration
Parameter
Description
Data Destination
Choose an available SQL Server data source in the current project.
Database
Supports selection or manual input of the database name to write to
If the data source network is not connected and the database information cannot be fetched directly, you can manually enter the database name. Data synchronization can still be performed when the Data Integration network is connected.
schema
Support selecting or manually entering the schema name to be read.
Table
Support selection or manual entry of the table name to be written to.
If the data source network is not connected and the table information cannot be fetched directly, you can manually enter the table name. Data synchronization can still be performed when the Data Integration network is connected.
Primary key
Select a field as the primary key for the data table
Advanced Settings (optional)
You can configure parameters according to business needs.
Data type conversion support
Read
Supported SQL Server Data Types and Conversion Relationships (when processing SQL Server, SQL Server data source types are mapped to data processing engine types):
SQLServer Type
Internal Types
char(n)
CHAR(n)
varchar(n),nvarchar(n),nchar(n)
VARCHAR(n)
text,ntext,xml
STRING
decimal(p, s),money,smallmoney
DECIMAL(p, s)
numeric
NUMERIC
REAL,FLOAT
FLOAT
bit
BOOLEAN
int
INT
tinyint
TINYINT
smallint
SMALLINT
time (n)
TIME (n)
bigint
BIGINT
date
DATE
datetime2,datetime,smalldatetime
TIMESTAMP(n)
datetimeoffset
TIMESTAMP_LTZ(3)
Write
Supported SQL Server Write Data Types and Conversion Relationships:
