sqlserver cdc 配置

(15条消息) Docker 安装SQL-SERVER2017 并启用CDC_保护我方胖虎的博客-CSDN博客

 

1、数据库开启cdc功能：EXECUTE sys.sp_cdc_enable_db

2、开启指定表的cdc

EXEC sys.sp_cdc_enable_table @source_schema= 'dbo', --源表架构
@source_name = 'db_table8', --源表
@role_name = NULL

3、查看哪些表开启了cdc

SELECT name,is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc = 1;

4、查看某一张表的哪些字段开启了cdc

SELECT [name] AS column_name
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.mytable')
AND is_replicated = 1;

5、如何增加一张表的cdc字段

a、先停掉这张表的cdc

EXEC sys.sp_cdc_disable_table 
 @source_schema = N'dbo',
 @source_name = 'test_amount2', 
 @capture_instance ='all'

b、增加一个字段

ALTER TABLE schema_name.table_name
ADD new_column_name data_type [NULL | NOT NULL] [CONSTRAINT constraint_name];

c、然后重新开启cdc就可以了

5、开启sqlagent。必须root用户登录docker

root@4d14bd7c81c7:/opt/mssql/bin# ./mssql-conf set sqlagent.enabled true

PS:否则flink tm会持续报如下错误：

2023-06-13 15:06:06,346 WARN  io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource [] - No maximum LSN recorded in the database; please ensure that the SQL Server Agent is running