import ChangeLog from '../changelog/connector-cdc-sqlserver.md';

SQL Server CDC

Sql Server CDC source connector

Support SQL Server Version

• server:2019 (Or later version for information only)

Support Those Engines

SeaTunnel Zeta

Flink

Key Features

• batch
• stream
• exactly-once
• column projection
• parallelism
• support user-defined split

Description

The Sql Server CDC connector allows for reading snapshot data and incremental data from SqlServer database. This document describes how to setup the Sql Server CDC connector to run SQL queries against SqlServer databases.

:::tip

When discovering table columns via JDBC metadata, SeaTunnel filters metadata rows by the exact schema/table identifier to avoid mixing columns from other tables (some drivers treat schemaPattern/tableNamePattern as SQL LIKE patterns). For case-sensitive databases, make sure the configured identifier case matches the database.

:::

Supported DataSource Info

Using Dependency

Install Jdbc Driver

For Spark/Flink Engine

1. You need to ensure that the jdbc driver jar package has been placed in directory ${SEATUNNEL_HOME}/plugins/.

For SeaTunnel Zeta Engine

1. You need to ensure that the jdbc driver jar package has been placed in directory ${SEATUNNEL_HOME}/lib/.

Data Type Mapping

                                             | BOOLEAN             |

Source Options

Enable Sql Server CDC

1. Check whether the CDC Agent is enabled

EXEC xp_servicecontrol N'querystate', N'SQLServerAGENT';

If the result is running, prove that it is enabled. Otherwise, you need to manually enable it

2. Enable the CDC Agent

/opt/mssql/bin/mssql-conf setup

3. The result is as follows

1. Evaluation (free, no production use rights, 180-day limit)
2. Developer (free, no production use rights)
3. Express (free)
4. Web (PAID)
5. Standard (PAID)
6. Enterprise (PAID)
7. Enterprise Core (PAID)
8. I bought a license through a retail sales channel and have a product key to enter.

4. Set the CDC at the library level Set the library level below to enable CDC. At this level, all tables under the libraries of the enabled CDC automatically enable CDC

USE TestDB; -- Replace with the actual database name

EXEC sys.sp_cdc_enable_db;

SELECT name, is_tracked_by_cdc FROM sys.tables WHERE name = 'table'; -- table Replace with the name of the table you want to check

Task Example

initiali read Simple

This is a stream mode cdc initializes read table data will be read incrementally after successful read The following sql DDL is for reference only

env {
  # You can set engine configuration here
  parallelism = 1
  job.mode = "STREAMING"
  checkpoint.interval = 5000
}

source {
  # This is a example source plugin **only for test and demonstrate the feature source plugin**
  SqlServer-CDC {
    plugin_output = "customers"
    username = "sa"
    password = "Y.sa123456"
    startup.mode="initial"
    database-names = ["column_type_test"]
    table-names = ["column_type_test.dbo.full_types"]
    url = "jdbc:sqlserver://localhost:1433;databaseName=column_type_test"
  }
}

transform {
}

sink {
  console {
    plugin_input = "customers"
  }
}

increment read Simple

This is an incremental read that reads the changed data for printing

env {
  # You can set engine configuration here
  parallelism = 1
  job.mode = "STREAMING"
  checkpoint.interval = 5000
}

source {
  # This is a example source plugin **only for test and demonstrate the feature source plugin**
  SqlServer-CDC {
   # Set up accurate one read
    exactly_once=true 
    plugin_output = "customers"
    username = "sa"
    password = "Y.sa123456"
    startup.mode="latest"
    database-names = ["column_type_test"]
    table-names = ["column_type_test.dbo.full_types"]
    url = "jdbc:sqlserver://localhost:1433;databaseName=column_type_test"
  }
}

transform {
}

sink {
  console {
    plugin_input = "customers"
  }
}

Support custom primary key for table

env {
  parallelism = 1
  job.mode = "STREAMING"
  checkpoint.interval = 5000
}

source {
  SqlServer-CDC {
    url = "jdbc:sqlserver://localhost:1433;databaseName=column_type_test"
    username = "sa"
    password = "Y.sa123456"
    database-names = ["column_type_test"]
    
    table-names = ["column_type_test.dbo.simple_types", "column_type_test.dbo.full_types"]
    table-names-config = [
      {
        table = "column_type_test.dbo.full_types"
        primaryKeys = ["id"]
      }
    ]
  }
}

sink {
  console {
  }
}

Changelog