CDC Sink for SQL Server: Overview

• CDC Sink supports SQL Server as a source database using native SQL Server Change Data Capture.

• SQL Server CDC captures row-level changes from the transaction log and stores them in CDC change tables.
  The CDC Sink task polls SQL Server CDC for new changes and applies INSERTs, UPDATEs, and DELETEs
  to RavenDB documents.

• In this article:

  • Connection string
  • Prerequisites
  • Polling behavior
  • Task configuration

Connection string

Use a SqlConnectionString with FactoryName set to "Microsoft.Data.SqlClient":

• Sync
• Async

store.Maintenance.Send(new PutConnectionStringOperation<SqlConnectionString>(
    new SqlConnectionString
    {
        Name = "SqlServerConnection",
        FactoryName = "Microsoft.Data.SqlClient",
        ConnectionString =
            "Server=myserver;Database=mydb;" +
            "User Id=ravendb_cdc;Password=...;" +
            "TrustServerCertificate=True;"
    }));

await store.Maintenance.SendAsync(new PutConnectionStringOperation<SqlConnectionString>(
    new SqlConnectionString
    {
        Name = "SqlServerConnection",
        FactoryName = "Microsoft.Data.SqlClient",
        ConnectionString =
            "Server=myserver;Database=mydb;" +
            "User Id=ravendb_cdc;Password=...;" +
            "TrustServerCertificate=True;"
    }));

Microsoft.Data.SqlClient defaults to Encrypt=True.
Unless SQL Server presents a certificate trusted by the RavenDB server, add TrustServerCertificate=True
(as above) or Encrypt=False to the connection string. Otherwise, the CDC Sink task cannot connect.

Prerequisites

Before CDC Sink can capture changes,
SQL Server CDC must be enabled on the source database and on every source table configured in the task.

If the SQL Server user in the CDC Sink connection string has permission to enable CDC,
RavenDB enables it automatically when the task starts. Otherwise, ask a database administrator to run:

• 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;

The SQL Server Agent service must be running for CDC change capture to work.
The Agent runs the capture jobs that populate the change tables.

On-premises, start the SQL Server Agent service; in Docker containers, start with -e 'MSSQL_AGENT_ENABLED=true'.
If the Agent is stopped, the task can still start and raise a warning, but no CDC changes are delivered until the Agent is running.

Azure SQL Database

Azure SQL Database has no classic SQL Server Agent.
Its CDC capture runs on a managed scheduler, so this requirement does not apply there.

Polling behavior

Unlike PostgreSQL and MySQL, SQL Server CDC Sink uses polling rather than a streaming replication connection.
The CdcSink.SqlServer.PollIntervalInSec server configuration key controls how frequently the task polls for new change rows.

Lower values reduce latency, but increase load on the source database.
See Server Configuration.

Task configuration

SQL Server CDC Sink uses the same CdcSinkConfiguration table, mapping, patching, and delete-handling settings as the other source engines. SQL Server does not use the PostgreSQL-specific Postgres settings.

See Configuration Reference for full details.

When SourceTableSchema is omitted, SQL Server uses "dbo" as the default schema.