-- 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'streamkap_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');