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 resources 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 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 resources that you want to ingest using decodable connection scan. Resource specifier keys The following resource specifier keys are available: Name Description 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 _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: Use connection activate and add the --force flag, for example: decodable connection activate cef0e708 --force or 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 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