Kafka Connect-Debezium连接MS SQL快照隔离随机故障排查

• 环境：MS SQL <> Debezium <> Kafka Connect
• 问题：数据库连接器随机故障（间隔数天触发）
• 当前临时解决方法：在数据库启用快照隔离后重启连接器
• 错误详情：
  连接器状态JSON：

  {
    "name": "debezium-sql-connector",
    "connector": {
      "state": "RUNNING",
      ...
    },
    "tasks": [
      {
        "id": 0,
        "state": "FAILED",
        "trace": <pasted below formatted>
      }
    ],
    "type": "source"
  }
  

  异常栈信息：

  org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped.
      at io.debezium.connector.base.ChangeEventQueue.throwProducerFailureIfPresent(ChangeEventQueue.java:171)
      at io.debezium.connector.base.ChangeEventQueue.poll(ChangeEventQueue.java:151)
      at io.debezium.connector.sqlserver.SqlServerConnectorTask.poll(SqlServerConnectorTask.java:162)
      at org.apache.kafka.connect.runtime.WorkerSourceTask.poll(WorkerSourceTask.java:244)
      at org.apache.kafka.connect.runtime.WorkerSourceTask.execute(WorkerSourceTask.java:220)
      at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:175)
      at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:219)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      at java.util.concurrent.FutureTask.run(FutureTask.java:266)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at java.lang.Thread.run(Thread.java:748)
  Caused by: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Snapshot isolation transaction failed accessing database 'sqldb1' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.
      at io.debezium.relational.RelationalSnapshotChangeEventSource.execute(RelationalSnapshotChangeEventSource.java:179)
      at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:87)
      ... 5 more
  Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Snapshot isolation transaction failed accessing database 'sqldb1' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.
      at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
      at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:5427)
      at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1758)
      at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1016)
      at io.debezium.connector.sqlserver.SqlServerConnection.lambda$singleResultMapper$7(SqlServerConnection.java:254)
      at io.debezium.jdbc.JdbcConnection.queryAndMap(JdbcConnection.java:540)
      at io.debezium.jdbc.JdbcConnection.queryAndMap(JdbcConnection.java:408)
      at io.debezium.connector.sqlserver.SqlServerConnection.getMaxLsn(SqlServerConnection.java:119)
      at io.debezium.connector.sqlserver.SqlServerSnapshotChangeEventSource.determineSnapshotOffset(SqlServerSnapshotChangeEventSource.java:154)
      at io.debezium.relational.RelationalSnapshotChangeEventSource.execute(RelationalSnapshotChangeEventSource.java:137)
      ... 6 more
  

1. 随机故障的根本原因 #

从异常栈可以明确：故障触发时Debezium正在尝试获取数据库的最大LSN（日志序列号），该操作需要在快照隔离事务中执行，但此时目标数据库的快照隔离被禁用了。

随机触发的原因大概率是两种情况：

• 数据库的快照隔离设置被人为操作或自动化运维脚本（如配置管理工具、定时任务）在连接器运行期间修改关闭；
• 连接器因外部波动（Kafka集群故障、任务重启、心跳超时等）触发重新初始化流程，而此时快照隔离已经被关闭，导致初始化失败。

2. 是否需要始终启用快照隔离？ #

是的，必须始终保持数据库的快照隔离处于启用状态。Debezium SQL Server连接器不止在初始化快照阶段需要它，在日常获取LSN、处理增量变更的部分一致性场景中都依赖快照隔离来保证数据的完整性，不是一次性需求。

至于其他连接器正常运行，可能是那些连接器还没触发需要快照隔离的操作（比如长期稳定运行未重启，暂时没执行LSN重新获取流程），或者运行阶段与故障连接器不同，但这绝不代表快照隔离可以被关闭。

3. 故障预防措施 #

• 锁定快照隔离配置：通过数据库权限控制禁止非授权用户修改该配置；若有自动化工具，确保不会误关闭选项。执行以下命令永久启用并加固：

  ALTER DATABASE sqldb1 SET ALLOW_SNAPSHOT_ISOLATION ON;
  -- 可选：限制非授权角色修改数据库配置
  DENY ALTER ANY DATABASE TO [非授权角色/用户];
  

• 配置连接器重试机制：在Debezium连接器配置中添加失败重试参数，短暂异常时自动重试避免直接失败：

  errors.retry.timeout=3600000
  errors.retry.delay.max.ms=5000
  

• 监控快照隔离状态：添加监控告警规则，当数据库ALLOW_SNAPSHOT_ISOLATION变为OFF时立即告警。可通过以下SQL查询状态：

  SELECT name, is_snapshot_isolation_on FROM sys.databases WHERE name = 'sqldb1';
  

• 排查配置变更源头：检查数据库操作日志，定位关闭快照隔离的操作主体，从根源杜绝误操作。

内容的提问来源于stack exchange，提问作者Saad