Microsoft SQL Server source connector

Features

Connector name

sqlserver-cdc

Delivery guarantee

Exactly once

Supported task sizes

S, M, L

Multiplex capability

A single instance of this connector can read from multiple tables from a single schema and database.

See Ingesting data from multiple tables.

Supported stream types

Change stream

Configuration properties

If you are using the CLI to create or edit a connection with this connector, you must use the declarative approach. You can generate the connection definition for the tables that you want to ingest using decodable connection scan.

Use the properties below to configure the connector. Refer to the documentation for instructions on creating and editing connections.

Property Description Required Default

Property	Description	Required	Default
Basic
`host`	The SQL Server host	Yes
`port`	The SQL Server port	—	`1433`
`database-name`	The name of the database	—
`username`	The username to use to authenticate to SQL Server	Yes
`password`	The password associated with the username. This must be provided as a secret resource.	Yes

Basic

host

The SQL Server host

Yes

port

The SQL Server port

—

1433

database-name

The name of the database

—

username

The username to use to authenticate to SQL Server

Yes

password

The password associated with the username. This must be provided as a secret resource.

Yes

Prerequisites

The SQL Server database must be accessible from the Decodable network. Connectivity options include AWS PrivateLink, SSH tunnels, and allowing connections from the Decodable published IP addresses.
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 that were defined when you enabled change data capture.

Ingesting data from multiple tables

A single instance of this connector can read from multiple tables from a single schema and database.

If you are using the CLI to create or edit a connection with this connector, you must use the declarative approach. You can generate the connection definition for the tables that you want to ingest using decodable connection scan.

Resource specifier keys

The following resource specifier keys are available:

Name Description

Name	Description
`database-name`	The database name
`schema-name`	The schema name
`table-name`	The table name

database-name

The database name

schema-name

The schema name

table-name

The table name

Metadata fields

If decodable connection scan is run with --opt with-metadata-fields=true then each of the following metadata fields will be added to the streams. These can be useful when mapping multiple tables to a single Stream, and when tracking record update times.

Name Description

Name	Description
`_decodable_database`	Source database
`_decodable_table`	Source table
`_decodable_op_ts`	Timestamp of ingestion. Defaults to `1970-01-01 00:00:00.000` for all existing records in the upstream table. All records inserted or updated following the initial connection activation will be populated with the UTC-based timestamp of the operation.

_decodable_database

Source database

_decodable_table

Source table

_decodable_op_ts

Timestamp of ingestion.

Defaults to 1970-01-01 00:00:00.000 for all existing records in the upstream table. All records inserted or updated following the initial connection activation will be populated with the UTC-based timestamp of the operation.

The _decodable_database and _decodable_table metadata fields are added to the Stream primary key as well.

Transaction Log retention

If the connection is stopped or in a failed state for longer than the transaction log’s retention period, the connection will fail when it’s restarted. This is because for CDC to work it needs a contiguous series of transaction log entries.

If you want to restart the connection in this situation you must discard its current state. By doing this, the initial snapshot of the required tables will be taken again and then the transaction log used for subsequent reads.

To do this do, one of the following:

In the Decodable Web UI, select Start and under Starting State select Reset current state and start from the initial state
In the Decodable CLI, do one of the following:
1. Use connection activate and add the --force flag, for example:
  decodable connection activate cef0e708 --force
  or
2. Use query with a suitable specifier for the connection (such as --name) and add the --operation reset-state argument, for example:
  decodable query --name customers-source --operation reset-state

Data types mapping

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

SQL Server Type Decodable Type

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`

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