Microsoft SQL Server source connector

Use the Microsoft SQL Server CDC (Change Data Capture) Connector to get data from Microsoft SQL Server into Decodable. The SQL Server CDC Connector is powered by Debezium and able to extract and send change events (each INSERT, UPDATE, and DELETE) through Decodable.

Features

Delivery guarantee

Exactly once

Prerequisites

Before you can get data from SQL Server, the following requirements must be met:

The SQL Server database must be accessible from the Decodable network.
The SQL Server database must be configured to produce change records.
The SQL Server user configured during connection creation must belong to a role with SELECT permissions on the capture tables defined during change-data enablement.

Steps: Create a connection

If you want to use the Decodable CLI or API to create the connection, you can refer to the Property Name column for information about what the underlying property names are. The connector name is sqlserver-cdc.

From the Connections page, select SQL Server CDC and complete the following fields.

UI Field Property Name Description

UI Field	Property Name	Description
Connection Type	N/A	Select Source to use this connector to get data into Decodable.
Host	`hostname`	The host name for your SQL Server database. For example, `sql-server`.
Port	`port`	Optional. The port number to use when connecting to the host. Defaults to `1433`.
Database	`database-name`	The name of the database containing the tables that you want to ingest data from. If empty, this connection connects to all existing databases.
Username	`username`	The username to use to authenticate to SQL Server.
Password	`password`	The password associated with the username. This must be provided as a secret resource. If you are using the Decodable CLI, run `decodable secret list` to view available secrets or `decodable secret --help` for help with creating a new secret. Note: For security purposes, Decodable will never display secret values in plaintext. You can manage which users have permissions to create, delete, or modify secrets in the Access Control management view. See Roles, groups, and permissions for more information.
Scan Startup Mode	`scan.startup.mode`	Optional. Specifies where in the collection to start reading data when the connection is first started or when it’s restarted. Must be one of the following: `initial`: At startup, takes an initial snapshot of monitored database tables, then continuously reads the latest binlog entries thereafter. `latest-offset`: Avoids taking an initial snapshot of monitored database tables upon startup. Instead, reads changes from the end of the binlog, capturing only the modifications made since the connector was initiated or restarted. `timestamp`: Bypasses the snapshot phase and initiates reading binlog events directly from a specified timestamp. Defaults to `initial`.
Starting Timestamp	`scan.startup.timestamp-millis`	The timestamp to start reading data from, specified in milliseconds since Epoch time. Only applicable if `scan.startup.mode` is `timestamp`.

Connection Type

N/A

Select Source to use this connector to get data into Decodable.

Host

hostname

The host name for your SQL Server database. For example, sql-server.

Port

port

Optional. The port number to use when connecting to the host.

Defaults to 1433.

Database

database-name

The name of the database containing the tables that you want to ingest data from. If empty, this connection connects to all existing databases.

Username

username

The username to use to authenticate to SQL Server.

Password

password

The password associated with the username. This must be provided as a secret resource. If you are using the Decodable CLI, run decodable secret list to view available secrets or decodable secret --help for help with creating a new secret.

Note: For security purposes, Decodable will never display secret values in plaintext. You can manage which users have permissions to create, delete, or modify secrets in the Access Control management view. See Roles, groups, and permissions for more information.

Scan Startup Mode

scan.startup.mode

Optional. Specifies where in the collection to start reading data when the connection is first started or when it’s restarted. Must be one of the following:

initial: At startup, takes an initial snapshot of monitored database tables, then continuously reads the latest binlog entries thereafter.
latest-offset: Avoids taking an initial snapshot of monitored database tables upon startup. Instead, reads changes from the end of the binlog, capturing only the modifications made since the connector was initiated or restarted.
timestamp: Bypasses the snapshot phase and initiates reading binlog events directly from a specified timestamp.

Defaults to initial.

Starting Timestamp

scan.startup.timestamp-millis

The timestamp to start reading data from, specified in milliseconds since Epoch time.

Only applicable if scan.startup.mode is timestamp.

Select Next. The next screen shows a list of tables that Decodable has detected from the SQL Server host.
Select the tables that you want to ingest data from. Decodable automatically creates a stream for each table selected. Each stream’s schema is automatically translated from SQL Server, including the field names, the data types of those fields, and which field(s) to use as the primary key. See Data Types Mapping for how SQL Server types map to Decodable types.
1. By default, Decodable uses the same name for the stream as the input table. If a stream already exists with that name and the schema of the stream matches the schema of the input table, Decodable uses that stream instead.
(Optional) If you want to rename the stream created, you can override the generated stream name.
1. For example, if you want to limit access to certain streams, you might choose to prefix specific streams and use role-based access control to grant access to certain prefixes. See Roles, groups, and permissions.
Select Next.
Give the newly created connection a Name and Description. Then, select Save.

Data Types Mapping

The following table shows the Decodable data types that are generated from the corresponding SQL Server data types.

If you are creating this connection with Decodable Web, then you will see a preview of how the SQL Server data types are translated to Decodable data types before your connection is created. This preview is not shown when you are using the Decodable CLI, so use this table as a reference for what your SQL Server data types will look like in Decodable.

SQL Server Type	Decodable Type
TINYINT SMALLINT	SMALLINT
INT	INT
BIGINT	BIGINT
REAL FLOAT(≤24)	FLOAT
FLOAT FLOAT(≥25)	DOUBLE
DECIMAL(p, s) NUMERIC(p, s)	DECIMAL(p,s)
BIT	BOOLEAN
MONEY	DECIMAL(19, 4)
SMALLMONEY	DECIMAL(10, 4)
DATE	DATE
SMALLDATETIME	TIMESTAMP(0)
DATETIME	TIMESTAMP(3)
DATETIME2	TIMESTAMP(7)
DATETIME2(p)	TIMESTAMP(p)
DATETIMEOFFSET	TIMESTAMP(7) WITH LOCAL TIME ZONE
DATETIMEOFFSET(p)	TIMESTAMP(p) WITH LOCAL TIME ZONE
TIME	TIME(7)
TIME(p)	TIME(p)
CHAR(n) NCHAR(n)	CHAR(n)
VARCHAR(n) NVARCHAR(n)	VARCHAR(n)
VARCHAR(MAX) NVARCHAR(MAX)	STRING
BINARY(n)	BINARY(n)
VARBINARY(n)	VARBINARY(n)
VARBINARY(MAX) IMAGE	BYTES
TEXT NTEXT XML	STRING

SQL Server Type

Decodable Type

TINYINT

SMALLINT

INT

BIGINT

REAL

FLOAT(≤24)

FLOAT

FLOAT(≥25)

DOUBLE

DECIMAL(p, s)

NUMERIC(p, s)

DECIMAL(p,s)

BIT

BOOLEAN

MONEY

DECIMAL(19, 4)

SMALLMONEY

DECIMAL(10, 4)

DATE

SMALLDATETIME

TIMESTAMP(0)

DATETIME

TIMESTAMP(3)

DATETIME2

TIMESTAMP(7)

DATETIME2(p)

TIMESTAMP(p)

DATETIMEOFFSET

TIMESTAMP(7) WITH LOCAL TIME ZONE

DATETIMEOFFSET(p)

TIMESTAMP(p) WITH LOCAL TIME ZONE

TIME

TIME(7)

TIME(p)

CHAR(n)

NCHAR(n)

CHAR(n)

VARCHAR(n)

NVARCHAR(n)

VARCHAR(n)

VARCHAR(MAX)

NVARCHAR(MAX)

STRING

BINARY(n)

VARBINARY(n)

VARBINARY(MAX)

IMAGE

BYTES

TEXT

NTEXT

XML

STRING