SQL Server

Connect SQL Server as a source for an initial full load and CDC into operational state or analytics targets. Complete the database preparation that matches the read mode you plan to use.

Before you begin

Connector availability and licensing

The SQL Server connector artifact bundles the Microsoft JDBC Driver under the MIT License; commercial connector use remains subject to the applicable agreement. It is distributed as an official preview artifact separate from the base server image. Before applying SQL Server connections on a new or upgraded server, register the connector:

connectors
register sqlserver

In production and air-gapped environments, you can also mount the connector JAR into TAPSTATE_CONNECTORS_SEED_DIR for automatic boot-time registration. See When registration is required.

Source

Choose the outcome you need. Each tab is a complete preparation path; you do not need to combine steps from both tabs.

CREATE LOGIN tapstate
  WITH PASSWORD = 'replace-with-a-secret',
       DEFAULT_DATABASE = database_name;
GO

USE database_name;
GO
CREATE USER tapstate FOR LOGIN tapstate WITH DEFAULT_SCHEMA = dbo;
GRANT CONNECT TO tapstate;
GRANT SELECT ON SCHEMA::dbo TO tapstate;
GO

CREATE LOGIN tapstate
  WITH PASSWORD = 'replace-with-a-secret',
       DEFAULT_DATABASE = database_name;
GO

USE database_name;
GO
CREATE USER tapstate FOR LOGIN tapstate WITH DEFAULT_SCHEMA = dbo;
GRANT CONNECT TO tapstate;
GRANT SELECT ON SCHEMA::dbo TO tapstate;
GRANT SELECT ON SCHEMA::cdc TO tapstate;
GO

USE database_name;
GO
EXEC sys.sp_cdc_enable_db;
GO

EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name = N'orders',
  @capture_instance = NULL,
  @role_name = N'cdc_reader',
  @supports_net_changes = 1;
GO

ALTER ROLE cdc_reader ADD MEMBER tapstate;
GO

SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = N'database_name';

EXEC sys.sp_cdc_help_change_data_capture
  @source_schema = N'dbo',
  @source_name = N'orders';

EXEC sys.sp_cdc_help_jobs;

EXEC sys.sp_cdc_change_job
  @job_type = N'cleanup',
  @retention = 2880;

Create a connection

Tapstate stores each SQL Server connection as a .tap.yml file in the workspace.

Create tapstate-work/source/orders_sqlserver.tap.yml. The connection's mode controls its read capability. For a full-load-only source, use mode: snapshot instead of mode: cdc.

version: tapstate/v1
kind: source
id: orders_sqlserver
connector: sqlserver
config:
  host: sqlserver.internal
  port: 1433
  database: orders
  schema: dbo
  user: ${SQLSERVER_READER_USER}
  password: ${SQLSERVER_READER_PASSWORD}
mode: cdc
tables:
  - orders
  - /customers_.*/

For a CDC-capable source, use mode: cdc after enabling database and table CDC and confirming that the cleanup window covers the recovery objective.

Connect SQL Server source to an operational target such as MongoDB:

version: tapstate/v1
kind: pipeline
id: orders_to_operational_state
source: orders_sqlserver
serve:
  from: /.*/
  sync:
    - id: write_operational_state
      source: operational_state
      write_mode: upsert
      ddl: fail

SQL Server is certified as a source in the current product path. Writing to SQL Server as a pipeline sync target is not supported.

Validate the configuration

tapstate validate --workdir tapstate-work

Validation does not resolve the host, credentials, or a SQL Server instance. To verify the connection against the database, register the SQL Server connector, apply the workspace, and run test:

tapstate -w tapstate-work

connect http://127.0.0.1:8080
login admin
register sqlserver -o json
apply
test orders_sqlserver -o json

Then verify in a staging table that:

1. the account can discover and snapshot the table;
2. the capture instance produces insert, update, and delete rows;
3. the cleanup window exceeds the tested recovery time;
4. data types match downstream consumer expectations.

Limitations

• CDC must be enabled and retained for every participating table. If cleanup removes the saved position, recovery requires a new snapshot.
• Tables with CDC enabled cannot use TRUNCATE TABLE, and renaming columns with sp_rename is not supported.
• After a schema change, SQL Server does not automatically rebuild the capture instance. Disable and re-enable table CDC during a controlled window before resuming dependent DML.
• DDL and DML close together can create a gap because some implicitly committed DDL is not represented in cdc.ddl_history.
• SQL Server CDC processes up to 64 KB for an inserted or updated large field by default. Capture only the required columns when large fields are involved, and verify the effective limit with representative values in staging.
• A task that polls hundreds of capture tables can accumulate latency and load. Split very large table sets and measure the polling interval in staging.
• SQL Server 2005 does not provide native CDC and is not supported for CDC.
• Configure forced encryption on SQL Server when required, then use the connection settings supplied by your tapstate deployment.

Recreate CDC after a table change

Pause dependent writes or coordinate a maintenance window, then discover the current capture instance, disable it, and enable it again:

EXEC sys.sp_cdc_help_change_data_capture
  @source_schema = N'dbo',
  @source_name = N'orders';
GO

EXEC sys.sp_cdc_disable_table
  @source_schema = N'dbo',
  @source_name = N'orders',
  @capture_instance = N'dbo_orders';
GO

EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name = N'orders',
  @role_name = N'cdc_reader',
  @supports_net_changes = 1;
GO

Recreating a capture instance changes the available change-table history. Verify the pipeline's saved position and run an end-to-end reconciliation before resuming production traffic.

Reference

Connection settings

The documented current source path uses the fields below.

Additional catalog fields

The catalog recognizes the fields below for encryption and advanced connection properties:

Database-side reference

This table summarizes SQL Server data behavior for the connector. Test uncommon types with representative data before migration.

Data compatibility