Debezium:数据库中未记录最大LSN；请确保SQL Server代理正在运行 [英] Debezium: No maximum LSN recorded in the database; please ensure that the SQL Server Agent is running

问题描述

此问题与以下问题有关:

This question is related to: Debezium How do I correctly register the SqlServer connector with Kafka Connect - connection refused

在Windows 10中，我让Debezium运行在Docker容器外部的Microsoft SQL Server实例上.我每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的代码，并且在代码注释中可以找到该警告的唯一地方指出，仅当代理未运行时才发出此警告.我已经确认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
      

      这篇关于Debezium:数据库中未记录最大LSN；请确保SQL Server代理正在运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！