tencent cloud

Tencent Cloud DataBuddy

DokumentasiTencent Cloud DataBuddyData IntegrationList of Data SourcesSQL Server Data Source

SQL Server Data Source

Unduh
Mode fokus
Ukuran font
Terakhir diperbarui: 2026-10-09 16:09:48
Diterjemahkan oleh AI

Overview

SQL Server is a relational database management system developed by Microsoft, widely used in enterprise applications and data management. DataBuddy data ingestion supports using SQL Server as a source, covering scenarios such as offline synchronization, real-time full-database synchronization, and sharded database and table synchronization. This document describes its connection configuration, supported capabilities, prerequisites, and common issues.

Supported Versions

Type
Supported Version
Self-Built SQL Server
2012 / 2014 / 2016 / 2017 / 2019 / 2022
TencentDB for SQL Server
2012 / 2014 / 2016 / 2017 / 2019 / 2022
JDBC Driver
mssql-jdbc-7.4.1.jre8

Read Capability

Capability
Offline read
Real-time full database read
Sharded database and table read
Supported
✓
✓
✓
For a complete comparison of data source capabilities, see Supported Data Sources and Read Capabilities.

Use Limits

Real-Time Sync Limitations

Limit
Description
CDC Feature
Enable CDC (Change Data Capture) on the database and tables.
Log Retention Period
CDC logs are retained for at least 72 hours.
Table without primary key
Tables without primary keys cannot guarantee exactly-once semantics.
Version Requirement
CDC is available only in Enterprise / Developer / Standard editions.
Lock Contention
Enable snapshot isolation to avoid lock contention.

Offline Sync Limitations

Limit
Description
View
Supports view reading
Split Key
Only integer fields are supported.

Supported Field Types

For complete field types, see SQL Server official documentation.
Field Type
Offline read
Real-time read
BIT / TINYINT / SMALLINT / INT / INTEGER / BIGINT
✅
✅
FLOAT / REAL / DECIMAL / NUMERIC / MONEY / SMALLMONEY
✅
✅
CHAR / NCHAR / VARCHAR / NVARCHAR / TEXT / NTEXT / XML
✅
✅
DATE / TIME / DATETIME / DATETIME2 / SMALLDATETIME / DATETIMEOFFSET
✅
✅
BINARY / VARBINARY / IMAGE / UNIQUEIDENTIFIER
✅
✅
GEOGRAPHY / GEOMETRY / HIERARCHYID / SQL_VARIANT
❌
❌

Supported DML/DDL for Real-Time Sync

DML

Operation
Whether Supported
INSERT / DELETE / UPDATE
✅

DDL

Operation
Whether Supported
Add column / Delete column / Rename column / Modify column type
❌
Add table / Rename table / Delete table / Clear table
❌
SQL Server captures changes based on the CDC mechanism and does not support automatic synchronization of DDL changes. After a Schema change, you need to re-enable CDC and may need to reconfigure the synchronization task. It is recommended to perform Schema changes during off-peak hours.

Prerequisites

1. Configuring Account Permissions

Offline synchronization:
CREATE LOGIN wedata_user WITH PASSWORD = 'your_password';
USE your_database;
CREATE USER wedata_user FOR LOGIN wedata_user;
GRANT SELECT ON SCHEMA::dbo TO wedata_user;
Real-time synchronization:
CREATE LOGIN wedata_cdc_user WITH PASSWORD = 'your_password';
USE your_database;
CREATE USER wedata_cdc_user FOR LOGIN wedata_cdc_user;
GRANT SELECT ON SCHEMA::dbo TO wedata_cdc_user;
GRANT SELECT ON SCHEMA::cdc TO wedata_cdc_user;
ALTER ROLE db_datareader ADD MEMBER wedata_cdc_user;

2. Enabling Database and Table CDC (Real-Time Sync Only)

USE your_database;
EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'your_table',
    @role_name = NULL,
    @supports_net_changes = 1;

3. Ensuring the SQL Server Agent Service Is Running

CDC relies on SQL Server Agent Jobs to capture changes:
EXEC sys.sp_cdc_help_jobs;

4. Enabling Snapshot Isolation (Recommended)

ALTER DATABASE your_database SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE your_database SET READ_COMMITTED_SNAPSHOT ON;

Creating a Data Source

Operation Steps

1. Log in to the DataBuddy console.
2. Switch to the target region and Workspace at the top.
3. Go to Data Integration > Task Management > Data Source Management (or Platform Management > Data Source Management).
4. Click Add Data Source and select SQL Server.
5. Enter the connection configuration and authentication information. For details, see Parameter Description.
6. Click Test Connection (see Data Source Connectivity Test).
7. After the test passes, click Save or Save & Create Task.

Parameter Description

Parameter
Description
Required
Data Source Name
Unique identifier of the data source within the workspace
Yes
JDBC URL
jdbc:sqlserver://host:port;DatabaseName=database
Yes
Username.
Database connection account
Yes
Password
Database connection password (SSM credential hosting supported)
Yes
Data Source Version
Yes
Example JDBC URL:
jdbc:sqlserver://10.0.0.1:1433;databaseName=mydb
jdbc:sqlserver://10.0.0.1:1433;databaseName=mydb;encrypt=true;trustServerCertificate=true
Common URL parameters:
Parameter
Description
databaseName
Database name.
encrypt / trustServerCertificate
Related to encrypted connections
loginTimeout
Login timeout (seconds)

Using in Data Ingestion Tasks

Real-Time Full Database Sync

Configuration Item
Description
Source Table Scope
Specified tables (only selected tables are synchronized; new tables require a restart) / regex matching (specify database name + table name regex; new tables that match the rule are automatically added).
Read Mode
Full + incremental / Incremental only

Offline Single Table Sync

Parameter
Description
Database / Schema / Table
Required. Schema defaults to dbo
Split Key
Only integer fields are supported.
Filtering Condition
Standard WHERE syntax

Best Practices

Account Permission Minimization: Use a dedicated account and avoid sa.
CDC Log Retention: Set it to at least 72 hours.
EXEC sys.sp_cdc_change_job
    @job_type = 'cleanup',
    @retention = 4320;  -- Unit: minutes (72 hours)
Split Key Selection: Primary key or index column.
Network Connectivity: Use a private network connection whenever possible.

FAQs

Q: Error Indicating CDC Is Not Enabled or CDC Table Is Not Found?

A: Enable CDC on the database and tables:
USE your_database;
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'your_table',
    @role_name = NULL;

Q: CDC Changes Not Captured?

A: The SQL Server Agent service is not started. Start the Agent service and check the CDC job status by running EXEC sys.sp_cdc_help_jobs;.

Q: Error Indicating CDC Change Data Not Found?

A: The CDC log retention period is too short, and the logs have been cleaned up. Increase the CDC retention period (see Best Practices).

Q: Connection Timeout or Refused?

A: Check network connectivity, confirm that SQL Server allows remote connections, and check the firewall port (1433 by default).

Q: Lock Wait or Deadlock During Synchronization?

A: Snapshot isolation is not enabled:
ALTER DATABASE your_database SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE your_database SET READ_COMMITTED_SNAPSHOT ON;

References

Add Data Source Connection
Data Source Connectivity Test
Supported Data Sources and Read Capabilities
Create a Single-Table Sync Task
Real-Time Whole-Database Multi-Table Task Configuration
Topik sebelumnya: Rest API Data SourceTopik selanjutnya: TDSQL-C MySQL Data Source

Bantuan dan Dukungan

Apakah halaman ini membantu?

masukan

Search
    Enter keywords to search documentation