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
 
 
posted @ 2024-12-13 16:57  zjb480  阅读(289)  评论(0)    收藏  举报