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 Write to multiple streams Yes 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 that were defined when you enabled change data capture. Steps 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 Microsoft SQL Server CDC and complete the following fields. 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 from which you want to ingest data. 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 with the state discarded. Must be one of the following: initial (default): At startup, takes an initial snapshot of monitored database tables, then continuously reads the latest transaction log entries thereafter. latest-offset: Avoids taking an initial snapshot of monitored database tables upon startup. Instead, reads changes from the end of the transaction log, capturing only the modifications made since the connector was initiated or restarted. Starting Timestamp scan.startup.timestamp-millis The timestamp to start reading data from, specified in milliseconds since Unix 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 fields to use as the primary key. See Data types mapping for how SQL Server types map to Decodable types. 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. 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. 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 Connector starting state and offsets When you create a connection, or restart it and discard state, it will read from the database based on the configuration of the scan startup mode. By default this is initial and will therefore snapshot the set of monitored tables and read the transaction log thereafter. Learn more about starting state here. 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 isn’t 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 Resource specifier keys When using the connection scan command of the Decodable CLI to create a connection specification, the following resource specifier keys are available: Name Description database-name The database name schema-name The schema name table-name The table name Connection resource definition options When defining connection resources using connection scan for the MySQL CDC source the following options can be specified with the --opt argument: Name Description with-metadata-fields Inject columns with special metadata values. Will add _decodable_database, _decodable_table and _decodable_op_ts to the stream schemas when set to true. Useful when mapping multiple tables to a single Stream, and when tracking record update times. The _decodable_database and _decodable_table metadata fields are added to the Stream primary key as well. Note that _decodable_op_ts defaults to 1970-01-01 00:00:00.000 for all existing records in the upstream table, upon first starting the connection. All records inserted or updated following the initial connection activation will be populated with the UTC-based timestamp of the operation. Example usage: --opt with-metadata-fields=true