sqlserver开启cdc

参考文档https://support.huaweicloud.com/intl/zh-cn/usermanual-roma/fdi-ug-202009081.html

首先必须开启agent:(要不然FLINK的TM一直打印以下日志)

2024-12-13 16:16:48,387 WARN io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource [] - No maximum LSN recorded in the database; please ensure that the SQL Server Agent is running

2024-12-13 16:16:48,409 WARN io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource [] - No maximum LSN recorded in the database; please ensure that the SQL Server Agent is running

如果是docker.操作步骤。

A.root进去：

docker exec -it --user root <container id> /bin/bash

B.执行命令: 

/opt/mssql/bin/mssql-conf set sqlagent.enabled true

C.重启docker:

docker restart xxx

 

1、确认数据库是否开启了cdc---更换数据库名。如果未开启使用命令开启

if exists(select 1 from sys.databases where name='flink' and is_cdc_enabled=0)
begin
      exec sys.sp_cdc_enable_db
end

2、执行以下命令查询CDC是否开启成功，结果为“1”表示成功

select is_cdc_enabled from sys.databases where name='flink'

3、开启表级别的日志--更换表名，如果未开启，则执行开启命令

IF EXISTS(SELECT 1 FROM sys.tables WHERE name='t2' AND is_tracked_by_cdc = 0)
BEGIN
    EXEC sys.sp_cdc_enable_table
        @source_schema = 'dbo', -- source_schema
        @source_name = 't2', -- table_name
        @capture_instance = NULL, -- capture_instance
        @supports_net_changes = 1, -- supports_net_changes
        @role_name = NULL -- role_name
END

 4、执行以下命令查询执行结果, 结果为“1”表示成功。

SELECT is_tracked_by_cdc FROM sys.tables WHERE name='t2'

 6、后续操作。如果系统表结构发生了变化或者有表级别调整，则需要重新开启CDC配置，配置步骤如下

先关闭CDC配置，请根据实际情况填写schema和name

EXEC sys.sp_cdc_disable_table 
 @source_schema = N'dbo',
 @source_name = 't2', 
 @capture_instance ='all'
重新开启CDC配置，请根据实际情况填写schema和name。开启表级别配置

IF EXISTS(SELECT 1 FROM sys.tables WHERE name='baris' AND is_tracked_by_cdc = 0) 
 BEGIN 
     EXEC sys.sp_cdc_enable_table 
         @source_schema = 'dbo', -- source_schema 
         @source_name = 'baris', -- table_name 
         @capture_instance = NULL, -- capture_instance 
         @supports_net_changes = 1, -- supports_net_changes 
         @role_name = NULL -- role_name 
 END