Debezium:数据库中没有记录最大LSN；请确保 SQL Server 代理正在运行

2021-11-12 00:00:00 sql-server apache-kafka apache-kafka-connect debezium

这个问题与:Debezium 如何使用 Kafka Connect 正确注册 SqlServer 连接器 - 连接被拒绝

在 Windows 10 中，我在 Docker 容器外部的 Microsoft SQL Server 实例上运行 Debezium.我每 390 毫秒收到以下警告:

In Windows 10, I have Debezium running on an instance of Microsoft SQL Server that is outside of a Docker container. I am getting the following warning every 390 milliseconds:

数据库中没有记录最大LSN；请确保 SQL服务器代理正在运行
[io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource]

No maximum LSN recorded in the database; please ensure that the SQL Server Agent is running
[io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource]

我在 Github 上检查了 Debezium 的代码，唯一能找到此警告的地方在代码注释中指出，只有在 Agent 未运行时才应抛出此警告.我已确认 SQL Server 代理正在运行.

I checked Debezium's code on Github and the only place that I can find this warning states in the code comments that this warning should only be thrown if the Agent is not running. I have confirmed that the SQL Server Agent is running.

为什么会出现此警告，我该如何解决?

Why is this warning showing up and how do I fix it?

注意:

我当前的解决方案似乎只适用于非生产环境 - 根据 Docker 的文档.

My current solution appears to only work in a non-production environment - per Docker's documentation.

推荐答案

LSN 是与 SQL Server 更改相关的片段"信息.如果您没有 LSN，则可能是您的 CDC 未运行或未正确配置.Debezium 使用 LSN 进行复制，因此您的 SQL Server 需要生成它.

LSN is the "pieces" of information related about your SQL Server changes. If you don't have LSN, is possible that your CDC is not running or not configured properly. Debezium consumes LSNs to replicate so, your SQL Server need to generate this.

一些方法:

  1. 您是否检查过您的表是否启用了 CDC?这将列出启用 CDC 的表:

SELECT s.name AS Schema_Name, tb.name AS Table_Name
, tb.object_id, tb.type, tb.type_desc, tb.is_tracked_by_cdc
FROM sys.tables tb
INNER JOIN sys.schemas s on s.schema_id = tb.schema_id
WHERE tb.is_tracked_by_cdc = 1

  1. 您的 CDC 数据库已启用并运行?(参见 这里)

检查是否启用:

SELECT * 
FROM sys.change_tracking_databases 
WHERE database_id=DB_ID('MyDatabase')

并检查是否正在运行:

EXECUTE sys.sp_cdc_enable_db;  
GO

  1. 您的 CDC 服务是否在 SQL Server 上运行?请参阅 在文档中

EXEC sys.sp_cdc_start_job;  
GO

  1. 在 CDC 中启用表时，我在使用角色名称时遇到了一些问题.就我而言，在 null 处配置解决了我的问题(更多详细信息 此处)
  1. On enabling table in CDC, I had some issues with rolename. For my case, configuring at null solved my problem (more details here)

    EXEC sys.sp_cdc_enable_table
        @source_schema=N'dbo',
        @source_name=N'AD6010',
        @capture_instance=N'ZZZZ_AD6010',
        @role_name = NULL,
        @filegroup_name=N'CDC_DATA',
        @supports_net_changes=1
     GO

相关文章