SQL Server Heartbeats

In low-traffic or intermittent databases, the log can become stale due to inactivity, which may cause the Connector to miss change events.

To prevent this, the heartbeat process — which runs within the Connector — periodically inserts or updates a record in the heartbeat table. This triggers a change event, keeping the logs active and ensuring that all changes are detected and captured by the Connector.

Setup Heartbeats

-- Create the heartbeat table with id, text, and last_update fields
CREATE TABLE streamkap.streamkap_heartbeat (
  id INT IDENTITY(1,1) PRIMARY KEY,
  text NVARCHAR(MAX),
  last_update DATETIME2 DEFAULT SYSUTCDATETIME()
);

-- Enable change tracking on the heartbeat table
EXEC sys.sp_cdc_enable_table  
@source_schema        = N'streamkap',
@source_name          = N'steamkap_heartbeat',
@role_name            = N'streamkap_role',
@filegroup_name       = N'Streamkap_ChangeTracking', -- Not applicable for Azure SQL Databases
@supports_net_changes = 0
GO

-- Grant permission on the heartbeat table to the Streamkap role
GRANT SELECT, INSERT, UPDATE ON streamkap.streamkap_heartbeat TO streamkap_role;

-- Insert the first row into the heartbeat table
INSERT INTO streamkap.streamkap_heartbeat (text) VALUES ('test_heartbeat');

Verify

Look for an entry in the heartbeat table with an updated timestamp after some minutes to verify this is working.