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
SELECT is_tracked_by_cdc FROM sys.tables WHERE name='t2'
先关闭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