Configuring Connection Details

This section provides instructions for configuring the connection details required to establish communication between the Micro-Integration and your third-party system.

For information about configuring the connection to the event broker, see Connecting to Your Event Broker.

This Micro-Integration supports workflows only in the direction of:

  • Microsoft SQL Server to Solace

The name of the binder for Microsoft SQL Server CDC is sqlservercdc.

Microsoft SQL Server Connection Details

To manually configure the Microsoft SQL Server connection details, set the following properties in application.yml:

spring:
  cloud:
    stream:
      sqlservercdc:
        bindings:
          input-0:
            consumer:
              endpoint:
                query-parameters:
                  databaseHostname: <hostname>
                  databasePort: <port>
                  databaseUser: <username>
                  databasePassword: <password>
                  databaseNames: <dbname>
                  ...

Where:

  • databaseHostname is the hostname of the Microsoft SQL Server database server.

  • databasePort is the port of the Microsoft SQL Server database server.

  • databaseNames is the name of the Microsoft SQL Server database.

  • databaseUser is the user to authenticate with Microsoft SQL Server database.

  • databasePassword is the password to use for authentication.

The following table lists additional configuration parameters:

Parameter Type Description
databaseInstance String The hostname of the Microsoft SQL Server instance.
topicPrefix String

A logical namespace for the Microsoft SQL Server database server or cluster. The topicPrefix is an internal identifier that helps distinguish between different Microsoft SQL Server sources when multiple Micro-Integrations are running. It must contain only alphanumeric characters, hyphens, dots, and underscores. If not configured, it defaults to the binding name.

The topicPrefix does not become an SMF topic.
snapshotMode String

The criteria for running a snapshot upon startup of the Micro-Integration. The allowed values are:

  • initial—The default. Takes a snapshot on first startup, then streams changes.

  • always—Performs a snapshot on every startup.

  • initial_only—Takes a snapshot but doesn't transition to streaming.

  • no_data—Schema structure only, no data.

  • never—Never takes snapshots.

  • recovery—Takes a snapshot when no offsets are available.

  • when_needed—Takes a snapshot when the Micro-Integration determines it's needed.

  • configuration_based—Uses a Micro-Integration-specific configuration.

  • custom—Uses a custom snapshotter implementation.
skippedOperations String

A comma-separated list of operations to skip during streaming. The allowed values are: 

  • c—Skip create/insert operations.

  • u—Skip update operations.

  • d—Skip delete operations.

  • t—Skip truncate operations (default).

  • none—Skip nothing (process all operations).

For example, skippedOperations=t,d indicates to skip truncations and deletions.
tombstonesOnDelete boolean Indicates whether delete operations should be represented by a delete event and a subsequent tombstone event. When tombstonesOnDelete=true, a delete operation generates two events: a delete event and a subsequent tombstone event (key with null value). This allows message brokers with log compaction to completely remove all events for a deleted row's key.
timePrecisionMode String

Times, dates, and timestamps can be represented with different kinds of precision, including:

  • adaptive—The default. Captures time and timestamp values exactly as in the database using millisecond, microsecond, or nanosecond precision values based on the database column's type.
  • adaptive_time_microseconds—Always uses microsecond precision for time fields.
  • connect—Always represents time and timestamp values using millisecond precision regardless of the database column's precision.
decimalHandlingMode String

Specifies how decimal and numeric columns should be represented in change events. Valid options include:

  • precise—Represents numbers precisely in binary form using java.math.BigDecimal values.
  • double—Represents numbers using Java double values, which may result in a loss of precision but are easier to use.
  • string—Encodes numbers as formatted strings. Semantic information about the real type is lost.
skipDeleteEvents boolean Indicates whether delete events will be skipped. Defaults to true.

Checkpoint Store Configuration

The Micro-Integration for Microsoft SQL Server CDC stores the information about the current progress of processing files in a checkpoint store backed by a Last Value Queue (LVQ) on a Solace event broker. The checkpoint store contains information about the files that have been processed and the files that are currently being processed.

For example, add the following settings to the application.yml file:

solace:
  sqlservercdc:
    checkpoint:
      lvqName: lvq-checkpoint
      autoProvisionLvq: true

The following table lists the configuration options for the Checkpoint Store.

Parameter Type Default Description
solace.sqlservercdc.checkpoint.lvqName String - Required. The name of the Solace LVQ to be used for checkpointing.
solace.sqlservercdc.checkpoint.autoProvisionLvq boolean false Indicates whether to auto-provision the LVQ queue if it does not exist.

Microsoft SQL Server CDC Consumer Configuration Options

The following configuration options are available for Microsoft SQL Server CDC consumers.

Config Option Type Valid Values Description
spring.cloud.stream.bindings.<input-x>.destination String - The source table from which to consume CDC events, in the format: schema.tableName. For example, public.products.
spring.cloud.stream.bindings.<input-x>.binder String sqlservercdc Must be set to sqlservercdc.

Headers

The Micro-Integration injects the following CDC-related headers in each message:

Header Name Type Description
cdc_operation String Values: c (create/insert), u (update), d (delete), r (read)
cdc_db_name String The name of the database
cdc_schema String The name of the schema
cdc_table_name String The name of the table
cdc_commit_lsn String Commit log sequence number
cdc_change_lsn String Change log sequence number
cdc_key String The key of the event
cdc_before String State of the row before the event occurred
cdc_timestamp Long The time at which the Micro-Integration processed the event