SQL Server on Amazon RDS

​

Prerequisites

• SQL Server version ≥ 2016 Service Pack 1 Standard or Enterprise Edition
• A database user with sufficient privileges to configure the database, including enabling CDC, CT and creating users

​

SQL Server Setup

​

1. Grant Database Access

• Configure one of the Connection Options to ensure Streamkap can reach your database.

​

2. Enable Change Data Capture

EXEC msdb.dbo.rds_cdc_enable_db '{database}';
GO

-- Replace { ... } placeholders as required
ALTER DATABASE [{database}] ADD FILEGROUP Streamkap_ChangeTracking;
ALTER DATABASE [{database}] ADD FILE (
	NAME      = Streamkap_ChangeTracking_Data,
	FILENAME  = N'D:\rdsdbdata\DATA\Streamkap_ChangeTracking_Data.ndf'
) TO FILEGROUP Streamkap_ChangeTracking;
GO

-- Replace { ... } placeholders as required
EXEC sys.sp_cdc_enable_table  
@source_schema        = N'{schema}',
@source_name          = N'{table}',
@role_name            = N'streamkap_role',
@filegroup_name       = N'Streamkap_ChangeTracking',
@supports_net_changes = 0
GO

​

3. Create Database User

-- Replace {database}, {schema} and {password} placeholders as required
USE {database};
GO
CREATE LOGIN streamkap_user WITH PASSWORD = '{password}';
CREATE USER streamkap_user FOR LOGIN streamkap_user;

CREATE ROLE streamkap_role;
ALTER ROLE streamkap_role ADD MEMBER streamkap_user;

GRANT SELECT ON SCHEMA::{schema} TO streamkap_role;
GRANT SELECT ON SCHEMA::cdc TO streamkap_role;
GO

USE master;
GO
GRANT VIEW SERVER STATE TO streamkap_user;
GO

​

4. Enable Snapshots

CREATE SCHEMA streamkap;

-- Create the table within the schema
CREATE TABLE streamkap.streamkap_signal (
  id VARCHAR(255) PRIMARY KEY, 
  type VARCHAR(32) NOT NULL, 
  data VARCHAR(2000) NULL
);

-- Enable change tracking on the signal table
EXEC sys.sp_cdc_enable_table  
@source_schema        = N'streamkap',
@source_name          = N'streamkap_signal',
@role_name            = N'streamkap_role',
@filegroup_name       = N'Streamkap_ChangeTracking',
@supports_net_changes = 0
GO

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

​

5. Heartbeats

​

Configure source database 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'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');

​

Streamkap Setup

​

1. Create the Source

• Navigate to Add Connectors.
• Choose SQL Server.

​

2. Connection Settings

• Name: Enter a name for your connector.
• Endpoint: Specify the endpoint of the SQL Server database.
• Port: Default is 1433.
• Connect via SSH Tunnel: The Connector will connect to an SSH server in your network which has access to your database. This is necessary if the Connector cannot connect directly to your database.
  • See SSH Tunnel for setup instructions.
• Username: Username to access the database. By default, Streamkap scripts use streamkap_user.
• Password: Password to access the database.
• Source Database: Specify the database to stream data from.
• Heartbeats: Crucial for low and intermittent traffic databases. Enabled by default.
  • Heartbeat Table Schema: If configured, this allows the connector to receive and acknowledge changes from low and intermittent traffic databases and prevent offsets from become stale. See Heartbeats for setup instructions.

​

3. Snapshot Settings

• Signal Table Schema: The Connector will use a table in this schema to manage snapshots. See Enable Snapshots for setup instructions.

​

4. Advanced Parameters

• Represent binary data as: Specifies how the data for binary columns e.g. blob, binary, varbinary should be interpreted. Your destination for this data can impact which option you choose. Default is bytes.
• Capture Only Captured Databases DDL (Default false) - Used to control whether the connector records schema structures from all databases defined in the server (the default) or only those databases for which you’ve explicitly configured the connector. Specify true to capture schema history only for the specific databases you’ve configured. This is particularly valuable when databases are large, to reduce the volume of DDL stored in the schema history topic. It also improves startup times when the connector restarts or recovers from failures. See Schema History Optimization for details.
• Capture Only Captured Tables DDL (Default false) - Used to control whether the connector records the schema structure for all tables in the configured databases (the default) or only the tables whose changes the connector captures. Specify true to capture schema history only for the specific tables you’ve configured. This is particularly valuable when tables are large, to reduce the volume of DDL statements stored in the schema history topic. It also improves startup times when the connector restarts or recovers from failures. See Schema History Optimization for details.

​

5. Schema and Table Capture

• Add Schemas/Tables: Specify the schema(s) and table(s) for capture
  • You can bulk upload here. The format is a simple list of schemas and tables, with each entry on a new row. Save as a .csv file without a header.