SQL Server when_needed: do not accept a NULL LSN as a recovery position
Separate an offset record from a usable LSN and verify the required snapshot actually starts.
An offset row can hide an unknown position
An offset record is different from a usable commit LSN. Do not approve a NULL LSN as a recovery position after CDC recreation. The useful Truthound comparison is the required recovery route against the route actually selected; inventing a valid LSN for an unknown value can hide missing initial data. [1, 2, 3]
Separate test evidence from causal hypotheses
Debezium SQL Server #2986 reports a failing when_needed integration test in which a snapshot saved a NULL commit LSN and a later restart after CDC recreation skipped a needed snapshot. The report marks two explanations for how the LSN arose as unconfirmed. The test and logs do not establish a customer loss total or a confirmed root cause. [1]
Check the snapshot that recovery requires
Isolate runs with a stored NULL commit LSN after a valid source minimum LSN has been confirmed following CDC recreation. Withhold recovery completion until resnapshotting and source-row reconciliation finish. Record the exact snapshot.mode=when_needed version and CDC history, then distinguish route selection, actual startup and landed data. [1, 3]
What one selected route establishes
The illustrative policy requires a new snapshot when no usable LSN exists, so required_snapshot_flag=1 for the NULL case. selected_snapshot_flag=1 meets only the route-selection condition. A value of 0 triggers investigation of offset-presence-based skipping. A selected 1 still does not prove snapshot completeness or completion; source reconciliation remains necessary. [1, 3]
Compare required and actual routes
Truthound Core Python SDK 3.3.2 can compare prepared required_snapshot_flag and selected_snapshot_flag using ColumnPairValidator with relationship="==". The operator verifies NULL state and the available minimum LSN, then supplies non-missing 0 or 1 policy flags. The SDK does not change SQL Server log retention or connector offsets. [1, 2, 3]
Even forced snapshots need a condition
Use a verified target version and the operating recovery procedure, then reconcile source rows after resnapshotting. Forcing a snapshot when a valid resume LSN exists can create unnecessary duplication and load. Keep unverifiable source scope unresolved instead of declaring recovery complete. [1, 3]
Application conditions
Confirm the exact snapshot.mode=when_needed contract and CDC recreation history, distinguishing an available source minimum LSN from stored NULL.
Alternatives
Resnapshot with a verified target version and the operating recovery procedure.
Limitations
Truthound does not change SQL Server log retention or offsets; test loss counts do not establish customer harm.
Evidence
- Event checked
- Subject checked
- Modified
