Microsoft SQL Advanced Settings

Overview

In most cases, the advanced settings mentioned below aren't required; however, Matillion CDC does offer some additional configuration items when setting up your pipelines. Take a moment to understand what these advanced settings are and what they achieve before incorporating them within your pipeline.

You can add advanced settings at the end of the Add Pipeline journey via the Matillion Data Loader UI.

snapshot.mode

The snapshot.mode setting allows control over the snapshot stage of the CDC process when your pipeline begins. Its valid values are listed below:

• initial: Allows snapshots of the captured table data and their structures. This is the default value.
• schema_only: Enables snapshots of captured table schemas, but not data. Schema-only snapshots are commonly used when only wanting to capture changes happening after the snapshot occurs.

database.applicationIntent

The Microsoft SQL Server connector can capture changes from an always-on, read-only replica.

• ReadOnly: This is required to support the Microsoft SQL Server Always ON (Read Replica) structure. Please read SQL Server Always On.

Encryption

Microsoft SQL Server connections use encryptions by default. You can specify security settings with advanced settings including:

• encryption: Connect with encryption. true by default. Set to false to disable encryption.
• trustServerCertificate: Implicitly trust a certificate provided by the server. Set to true to trust (skip validation of) the Microsoft SQL Server TLS certificate.

Trusting the server certificate can allow users to connect with encryption on. Due to the CDC Agent being containerized, it is not currently possible to add certificates to the TrustStore.

For more information, see Microsoft's documentation on Connecting with encryption.