Microsoft SQL Server

Extract loads source data into Microsoft SQL Server tables using SQL authentication and encrypted connections. The destination supports Append, Replace, Upsert, History, SoftDelete, Replica, and partition overwrite, plus Debug Queries for inspecting the loaded data.

Prerequisites

• An existing SQL Server database reachable from the Extract worker. Configure routing and firewall access to the server's TCP port, usually 1433.
• A dedicated SQL login and database user with permission to create and manage destination tables.
• A server certificate whose hostname matches SQL Server Host and whose certificate authority is trusted. For a private certificate authority, supply its PEM root certificate in TLS CA Certificate.

This connector uses a username and password. Windows/Kerberos authentication, Microsoft Entra authentication, and SSH tunneling are not supported by its current setup fields.

Setup guide

1. Create a login and database user

Ask your database administrator to create a dedicated SQL login and map it to the target database. For a SQL Server instance, the following example uses an existing database named extract:

USE [master];
GO
CREATE LOGIN [extract_user] WITH PASSWORD = '<replace-with-a-strong-password>';
GO
USE [extract];
GO
CREATE USER [extract_user] FOR LOGIN [extract_user];
GO

Replace the example names and password before running the statements. See Microsoft's SQL login setup and database user documentation for your deployment's authentication model.

2. Create a destination schema and grant permissions

Using a dedicated schema keeps Extract's tables separate from other application tables. Run these statements in the target database as an administrator:

CREATE SCHEMA [extract] AUTHORIZATION [dbo];
GO
GRANT CONNECT TO [extract_user];
GRANT CREATE TABLE TO [extract_user];
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER
    ON SCHEMA::[extract] TO [extract_user];
GO

For this configuration, enable Use an existing schema when setting up the destination and set the connection's Schema to extract.

If you want Extract to create schemas automatically, leave Use an existing schema disabled and grant the database user CREATE SCHEMA as well. The user still needs permission to manage tables in any existing schema it uses. See Microsoft's database permissions documentation.

3. Configure the destination in Extract

Create a Microsoft SQL Server destination and enter:

Setting	What to enter
SQL Server Host	Server hostname matching its TLS certificate.
Port	TCP port; defaults to 1433.
Username	SQL login name.
Password	SQL login password.
Database	Existing target database name.
Use an existing schema	Enable to prevent automatic schema creation; defaults to disabled.
TLS CA Certificate	Optional PEM root certificate for a private certificate authority, including its BEGIN/END markers.

Save the destination and check its login status. A successful login confirms access to the database; the first connection run also checks the permissions needed to write tables.

TLS certificate and hostname validation remain enabled when a custom CA is provided. Do not put a private key in the certificate field.

4. Create a connection and select streams

Choose a source and the SQL Server destination, then configure:

Setting	Scope	Behavior
Schema	Connection	Target schema; defaults to dbo.
Table Name Prefix	Connection	Optional prefix applied to every destination table name.
Schema Migration Policy	Connection	Controls automatic stream activation, added fields, safe type widening, and field deprecation. By default, new fields are added.
Table Name Override	Stream	Optional replacement for the stream's table name. The connection's prefix still applies.

Select the fields and a supported load mode for each stream. Start a run and use Debug Queries to verify the results.

Load modes

The available load modes depend on the source stream's extraction mode:

Extraction mode	Supported load modes
Full refresh	Append, Replace, SoftDelete
Incremental changes	Append, Upsert, History, Replica
Partition	Append, Replace

• Append adds incoming rows and preserves existing rows. Replaying the same input adds it again.
• Replace with full refresh replaces all rows in the table. A successful empty refresh clears the table.
• Upsert inserts new primary keys and updates existing keys, including composite keys. If a batch contains the same key more than once, the last incoming row wins.
• History preserves versions using _singular_active, _singular_start, and _singular_end. It requires a primary key and a source timestamp with microsecond precision. Replayed key/timestamp pairs are skipped; duplicate pairs within one batch and unknown out-of-order versions fail.
• SoftDelete updates or inserts incoming rows and marks missing rows with _singular_deleted. Returning rows are restored. An empty full refresh marks all existing rows deleted. Without a primary key, this mode falls back to full Replace.
• Replica applies compatible CDC events using _extract_deleted, _extract_cdc_lsn, and _extract_commit_timestamp. Deleted rows remain as tombstones. Older or replayed sequence numbers do not overwrite newer rows; this mode requires the source's CDC fields, not ordinary incremental rows alone.

Partition overwrite uses Replace with partition extraction. It replaces only partitions present in the incoming data, including a null partition key. Other partitions remain unchanged; empty input replaces no partitions.

Extract stages each stream before applying its target changes in a transaction. Source or loading failures roll back uncommitted target changes. If a connection is lost during commit and the outcome is unknown, inspect the destination before retrying an Append run to avoid duplicates.

Data types and existing tables

Text is stored as Unicode NVARCHAR; integers use BIGINT, booleans use BIT, dates use DATE, and timestamps use DATETIMEOFFSET(7). Numbers use FLOAT, or DECIMAL(38,18) when specified by the source schema. JSON, objects, and arrays are serialized into NVARCHAR(MAX), preserving JSON strings and booleans. Missing and null values become SQL NULL.

Values that would exceed a column's supported length, integer range, decimal precision, or timestamp precision fail rather than being silently truncated or rounded. Exponent notation is supported for decimals when the value fits exactly within 20 integer and 18 fractional digits.

Primary keys and collation

Upsert, History, Replica, and keyed SoftDelete require selected, non-null primary-key fields. Existing target rows with null key components must be corrected before loading.

New keyed tables use unique nonclustered indexes; History enforces uniqueness only for active versions. Incoming composite keys must fit SQL Server's 1,700-byte nonclustered key limit. Text keys without a bounded source length use NVARCHAR(850). The combined actual key size is checked on every run, so later growth can fail even if earlier values fit. Pre-created tables with narrower columns or clustered keys can have stricter limits.

Text columns created, added, or widened by Extract use Latin1_General_100_BIN2 collation. This keeps keys such as ABC and abc distinct. Existing text primary-key columns used by keyed modes, and text partition-key columns used by partition Replace, must use that same collation.

If an existing table has an incompatible key collation, the run fails before changing its rows. Have your administrator migrate the key columns and their dependent indexes, or recreate the destination table and fully reload it from the source. Changing collation cannot recover rows already combined by earlier case-insensitive loads. SQL Server's trailing-space comparison rules still apply: keys differing only in trailing spaces are not distinguished.

Schema changes

Extract applies enabled schema-migration policies for added, renamed, deprecated, and changed fields. Automatic type changes allow safe widening; potentially lossy conversions require an explicit migration. Existing NOT NULL constraints are preserved.

Identifiers are limited to 128 UTF-16 code units, and selected field names cannot differ only by case. On a case-sensitive database, pre-created column names must match the source catalog's spelling. Switching load modes may require migrating managed columns such as History or SoftDelete fields before the next run.

Debug Queries

Use the existing Debug Queries screen to select fields, apply filters and date windows, group rows, calculate aggregations, preview results, download results, and retrieve row counts from the destination. This feature uses the existing query controls; the destination does not add an arbitrary-SQL editor.

Troubleshooting

Error or symptom	What to check
UnknownIssuer or another certificate validation error	Configure a trusted server certificate or supply the correct PEM root CA. The host must still match the certificate.
Login succeeds but a run fails with a permissions error	Check table-creation and schema permissions, plus permission to modify existing target tables.
Incompatible destination key collation	Migrate text keys and dependent indexes to Latin1_General_100_BIN2, or recreate and fully reload the table.
Composite key exceeds the index limit	Review the combined key size and source key design. Do not truncate identifiers to make them fit.
A decimal or timestamp cannot be represented exactly	Check source precision and the destination type; correct or explicitly transform the source value before retrying.
Commit outcome may be unknown	Verify the destination state before replaying the run, especially for Append.

For a development database, follow Microsoft's SQL Server container quickstart. Microsoft supports those container images on Intel/AMD x86-64 Linux hosts. The database must be reachable from the Extract worker: localhost refers to the worker's own host, not your laptop when using a hosted worker.