MSSQL

Key Features

Offers both Full Refresh and Incremental (Append) synchronization modes.
Includes Change Data Capture (CDC) to replicate inserts, updates, and deletions.
Supports syncing of incremental deletes.
Ensures secure data transfer through SSL and SSH Tunnel options.
Namespaces are turned on by default.
Implements checkpointing and chunking to ensure consistent replication, even with large datasets.

Compatible Microsoft SQL Platforms

Azure SQL Database
Azure Synapse Analytics
Azure SQL Managed Instance
SQL Server 2012, 2014, 2016, 2017, 2019, and 2022

Prerequisites

An MSSQL Server instance that is reachable from the xGen environment.
A dedicated read-only user for xGen (recommended) with access to all required tables.
For CDC functionality: additional configuration is needed, and the user must have at least db_owner rights.

Getting Started

Create a Read-Only User
- While an existing user can be used, it’s best practice to create a new user with read-only access for better control and tracking.
Configure CDC (Optional)
- Activate CDC at the database level for change tracking.

CDC Limitations

CDC support begins with SQL Server 2016 SP1 and later versions.
Unsupported data types: hierarchyid and sql_variant.
CDC cannot be used with tables that have clustered columnstore indexes.
Master database does not support CDC.

Required Setup Parameters

Host: IP address or domain of the MSSQL server
Port: Listening port of the server (default: 1433)
Database: Target database for replication
Username: Ideally a read-only account for authentication
Password: Associated password for the username

Additional Optional Parameters

SSL Configuration: Select if a secure connection is required
SSH Tunnel Options: Use tunneling if direct access is unavailable (options: No Tunnel, SSH Key, or Password-based)
JDBC URL Settings: Use advanced JDBC flags like ApplicationIntent=ReadOnly and MultiSubnetFailover=True for scenarios such as read replicas or multi-subnet environments.