# SQL Server CDC Patterns

> For the complete documentation index, see [llms.txt](https://docs.redpanda.com/llms.txt). Component-specific: [connect-full.txt](https://docs.redpanda.com/connect-full.txt)

---
title: SQL Server CDC Patterns
latest-connect-version: 4.105.0
latest-operator-version: v26.2.1
latest-console-tag: v3.10.0
latest-redpanda-tag: v26.2.1
docname: microsoft_sql_server_cdc
page-component-name: connect
page-version: master
page-component-version: master
page-component-title: Connect
page-relative-src-path: microsoft_sql_server_cdc.adoc
page-edit-url: https://github.com/redpanda-data/rp-connect-docs/edit/main/modules/cookbooks/pages/microsoft_sql_server_cdc.adoc
description: Learn how to capture, filter, transform, and route Microsoft SQL Server change data capture (CDC) events with Redpanda Connect.
page-topic-type: cookbook
personas: streaming_developer, data_engineer
learning-objective-1: Apply reusable patterns for capturing SQL Server CDC events
learning-objective-2: Adapt integration patterns to route CDC data to Redpanda, S3, and other destinations
learning-objective-3: Identify patterns for filtering and transforming change events
page-git-created-date: "2026-06-17"
page-git-modified-date: "2026-06-17"
---

<!-- Source: https://docs.redpanda.com/connect/cookbooks/microsoft_sql_server_cdc.md -->

The `microsoft_sql_server_cdc` input captures row-level changes from SQL Server tables using SQL Server’s built-in CDC change tables. Use these patterns to filter, transform, and route SQL Server CDC events to Redpanda, S3, and other destinations.

Use this cookbook to:

-   Apply reusable patterns for capturing SQL Server CDC events

-   Adapt integration patterns to route CDC data to Redpanda, S3, and other destinations

-   Identify patterns for filtering and transforming change events


> 📝 **NOTE**
>
> The `microsoft_sql_server_cdc` input is in beta. The API is subject to change.

## [](#prerequisites)Prerequisites

Before using these patterns, configure the following.

### [](#redpanda-cli)Redpanda CLI

Install the Redpanda CLI (`rpk`) to run Redpanda Connect. See [rpk installation](https://docs.redpanda.com/connect/get-started/quickstarts/rpk/) for installation instructions.

### [](#sql-server-cdc)SQL Server CDC

CDC must be enabled on both the database and each table you want to capture. Run the following as a user with `db_owner` or `sysadmin` privileges:

```sql
-- Enable CDC on the database
EXEC sys.sp_cdc_enable_db;

-- Enable CDC on a table
EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name = N'orders',
  @role_name = NULL;
```

For cloud-managed SQL Server, see:

-   [Azure SQL Database CDC](https://learn.microsoft.com/en-us/azure/azure-sql/database/change-data-capture-overview)

-   [SQL Server CDC documentation](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture)


### [](#checkpoint-storage)Checkpoint storage

By default, the `microsoft_sql_server_cdc` input creates a table (`rpcn.CdcCheckpointCache`) and stored procedure in your SQL Server database to store the last processed Log Sequence Number (LSN). The pipeline user must have `CREATE TABLE` and `CREATE PROCEDURE` permissions.

To use an external cache instead, set the `checkpoint_cache` field to a cache label defined in `cache_resources`. See the [connector reference](https://docs.redpanda.com/connect/components/inputs/microsoft_sql_server_cdc/#_checkpoint_cache_table_name) for details.

### [](#environment-variables)Environment variables

The examples in this cookbook use environment variables for configuration:

```bash
export MSSQL_CONN="sqlserver://user:password@localhost:1433?database=mydb" (1)
export REDPANDA_BROKERS=localhost:9092 (2)
export S3_BUCKET=cdc-archive (3)
```

| 1 | The SQL Server connection string in sqlserver://user:password@host:port?database=dbname format. |
| --- | --- |
| 2 | The Redpanda broker addresses (for Redpanda output examples). |
| 3 | The Amazon S3 bucket name (for S3 output examples). |

## [](#capture-cdc-events)Capture CDC events

The simplest pattern captures all change events from SQL Server tables and outputs them with metadata:

```yaml
input:
  microsoft_sql_server_cdc:
    connection_string: ${MSSQL_CONN}
    include:
      - dbo.orders
    stream_snapshot: true

pipeline:
  processors:
    - mapping: |
        root.operation = meta("operation")
        root.schema = meta("database_schema")
        root.table = meta("table")
        root.lsn = meta("lsn")
        root.data = this
        root.timestamp = now()

output:
  stdout:
    codec: lines
```

For details on the CDC event message structure and available metadata fields, see the [metadata](https://docs.redpanda.com/connect/components/inputs/microsoft_sql_server_cdc/#_metadata) section in the connector reference.

> 📝 **NOTE**
>
> SQL Server CDC generates two events for each row update: `update_before` (the previous row values) and `update_after` (the new row values). Most downstream use cases only need the `update_after` event.

## [](#filter-cdc-events)Filter CDC events

Filter events to process only the current row state after each change, using the `operation` metadata field:

```yaml
input:
  microsoft_sql_server_cdc:
    connection_string: ${MSSQL_CONN}
    include:
      - dbo.orders
    stream_snapshot: true

pipeline:
  processors:
    - mapping: |
        root = if meta("operation") != "insert" && meta("operation") != "update_after" {
          deleted()
        }
    - mapping: |
        root.operation = meta("operation")
        root.table = meta("table")
        root.data = this
        root.timestamp = now()

output:
  stdout:
    codec: lines
```

This pattern:

-   Keeps only `insert` and `update_after` operations, dropping `delete`, `update_before`, and `read` events

-   Transforms the event to a simplified format with a timestamp


## [](#route-to-redpanda)Route to Redpanda

Stream SQL Server changes to Redpanda for real-time processing:

```yaml
input:
  microsoft_sql_server_cdc:
    connection_string: ${MSSQL_CONN}
    include:
      - dbo.orders
      - dbo.customers
    stream_snapshot: true

pipeline:
  processors:
    - mapping: |
        meta topic = meta("database_schema") + "." + meta("table")

output:
  redpanda:
    seed_brokers:
      - ${REDPANDA_BROKERS}
    topic: ${! meta("topic") }
    key: ${! json("id") }
    batching:
      count: 100
      period: 1s
```

This pattern:

-   Uses `database_schema.table` as the Redpanda topic name

-   Batches messages for efficient delivery

-   Sets the message key to the primary key field (update `json("id")` to match your table’s primary key column)


## [](#route-to-s3)Route to S3

Archive CDC events to S3 for long-term storage and analytics:

```yaml
input:
  microsoft_sql_server_cdc:
    connection_string: ${MSSQL_CONN}
    include:
      - dbo.orders
    stream_snapshot: true

pipeline:
  processors:
    - mapping: |
        root.operation = meta("operation")
        root.schema = meta("database_schema")
        root.table = meta("table")
        root.data = this
        root.timestamp = now()

output:
  aws_s3:
    bucket: ${S3_BUCKET}
    path: >-
      cdc/${! meta("database_schema") }/${! meta("table") }/${! timestamp_unix().format_timestamp("2006/01/02/15") }/${! uuid_v4() }.ndjson
    batching:
      count: 1000
      period: 5m
      processors:
        - archive:
            format: lines
```

This pattern:

-   Organizes files by database schema, table, and time-based partitions (year/month/day/hour)

-   Batches events and archives them as newline-delimited JSON

-   Uses UUID file names to prevent collisions


## [](#route-by-event-type)Route by event type

Route different event types to different destinations:

```yaml
input:
  microsoft_sql_server_cdc:
    connection_string: ${MSSQL_CONN}
    include:
      - dbo.orders
    stream_snapshot: true

output:
  switch:
    cases:
      - check: meta("operation") == "insert"
        output:
          redpanda:
            seed_brokers:
              - ${REDPANDA_BROKERS}
            topic: sqlserver.orders.inserts
      - output:
          redpanda:
            seed_brokers:
              - ${REDPANDA_BROKERS}
            topic: sqlserver.orders.changes
```

This pattern:

-   Routes `insert` events to `sqlserver.orders.inserts`, and `update_before`, `update_after`, and `delete` events to `sqlserver.orders.changes`

-   Supports specialized downstream consumers per operation type


## [](#configure-replication-mode)Configure replication mode

The `microsoft_sql_server_cdc` input supports two replication modes controlled by the `stream_snapshot` field:

-   `stream_snapshot: true`: Captures a full snapshot of existing table data before streaming live changes. Use this when you need a complete initial load.

-   `stream_snapshot: false`: Skips the snapshot and streams only changes from the current LSN position. Use this when you only need new changes going forward.


```yaml
input:
  microsoft_sql_server_cdc:
    connection_string: ${MSSQL_CONN}
    include:
      - dbo.orders
      - dbo.customers
    stream_snapshot: true
    max_parallel_snapshot_tables: 4 (1)
    snapshot_max_batch_size: 1000 (2)
```

| 1 | Number of tables to snapshot in parallel. |
| --- | --- |
| 2 | Number of rows to read per batch during snapshot processing. |

## [](#filter-tables-with-patterns)Filter tables with patterns

The `include` field accepts regular expressions, so you can capture multiple tables without listing each one:

```yaml
input:
  microsoft_sql_server_cdc:
    connection_string: ${MSSQL_CONN}
    include:
      - dbo.* (1)
    exclude:
      - dbo.audit_log (2)
    stream_snapshot: false
```

| 1 | Captures all tables in the dbo schema. |
| --- | --- |
| 2 | Excludes a specific table matched by the include pattern. |

## [](#troubleshoot-common-issues)Troubleshoot common issues

Use these steps to diagnose and fix the most common problems with the `microsoft_sql_server_cdc` input.

### [](#no-events-received)No events received

If no events arrive:

1.  Verify CDC is enabled on the database and table:

    ```sql
    SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'mydb';
    SELECT name, is_tracked_by_cdc FROM sys.tables WHERE name = 'orders';
    ```

2.  Confirm CDC agent jobs are running:

    ```sql
    EXEC sys.sp_cdc_help_jobs;
    ```

3.  Check that the table pattern in `include` matches the target table, using `schema.table` format.


### [](#checkpoint-table-creation-fails)Checkpoint table creation fails

If the pipeline fails to start because it cannot create the checkpoint table:

-   Grant `CREATE TABLE` and `CREATE PROCEDURE` permissions to the pipeline user, or

-   Create the schema manually (`CREATE SCHEMA rpcn`) and grant the user permissions on it, or

-   Use an external `checkpoint_cache` to avoid creating a table in SQL Server.


### [](#duplicate-events)Duplicate events

The `microsoft_sql_server_cdc` input provides at-least-once delivery. If the pipeline fails between checkpoints, events may be re-read on restart. To handle duplicates:

-   Use idempotent processing in downstream systems.

-   Deduplicate using the `lsn` metadata field.

-   Lower `checkpoint_limit` to reduce the window of possible duplicates.


## [](#next-steps)Next steps

-   [SQL Server CDC input reference](https://docs.redpanda.com/connect/components/inputs/microsoft_sql_server_cdc/)

-   [Redpanda output](https://docs.redpanda.com/connect/components/outputs/redpanda/)


## Suggested labs

-   [Stream Jira Issues to Redpanda for Real-Time Metrics](https://docs.redpanda.com/labs/docker-compose/jira-metrics-pipeline/)

[Search all labs](https://docs.redpanda.com/labs)