SQL Server
Supermetal replicates from SQL Server using its native CDC capture tables for change data capture and parallel snapshots for initial loads. The SQL Server Agent populates the capture tables asynchronously from the transaction log.
Prerequisites
- SQL Server 2016 or higher (Enterprise, Standard, Personal, Azure SQL Managed Instance), Azure SQL Database, or Amazon RDS for SQL Server.
- Network connectivity from the Supermetal agent to the server (default port 1433).
Unsupported SQL Server editions
CDC is not supported on Azure Synapse, Azure SQL Edge, or Azure Synapse Serverless.
Setup
Supermetal supports two replication types for SQL Server sources.
- Capture Table Replication. Real time change data capture (CDC) with an initial snapshot.
- Snapshot. One time or scheduled full table snapshots.
Transactional Consistency
SQL Server CDC writes changes into a separate capture table per source table, splitting a multi table transaction's changes across capture tables. Supermetal reads them in one of two modes.
- Standard (default). Processes each capture table independently for the lowest latency. Suitable when consistency within each table is enough.
- Transactional (opt in). Reconstructs multi table transactions across capture tables, so related changes, such as an order and its line items, arrive together exactly as they were committed at the source.
Data Types Mapping
| SQL Server Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
TINYINT | UInt8 | |
SMALLINT | Int16 | |
INT | Int32 | |
BIGINT | Int64 | |
SMALLMONEY | Decimal128(10, 4) | Fixed precision with 4 decimal places. |
MONEY | Decimal128(19, 4) | Fixed precision with 4 decimal places. |
DECIMAL(p,s) | Decimal128(p, s) / Decimal256(p, s) | Precision and scale carry over directly. Decimal256 for precision > 38. |
NUMERIC(p,s) | Decimal128(p, s) / Decimal256(p, s) | Same as DECIMAL. Default precision is 18. |
REAL | Float64 | |
FLOAT | Float64 |
| SQL Server Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
BIT | Boolean |
| SQL Server Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
DATE | Date32 | Days since UNIX epoch. |
TIME | Time64(ns) | Nanoseconds since midnight. |
SMALLDATETIME | Timestamp(s) | Rounds to the nearest minute. |
DATETIME | Timestamp(ms) | Millisecond precision, 3.33ms accuracy. |
DATETIME2(0) | Timestamp(s) | Second precision. |
DATETIME2(1-3) | Timestamp(ms) | Millisecond precision. |
DATETIME2(4-6) | Timestamp(µs) | Microsecond precision. |
DATETIME2(7) | Utf8 | Mapped to string to preserve the full range (years 1 to 9999). |
DATETIMEOFFSET(0) | Timestamp(s, "UTC") | Second precision, normalized to UTC. |
DATETIMEOFFSET(1-3) | Timestamp(ms, "UTC") | Millisecond precision, normalized to UTC. |
DATETIMEOFFSET(4-6) | Timestamp(µs, "UTC") | Microsecond precision, normalized to UTC. |
DATETIMEOFFSET(7) | Utf8 | Mapped to string to preserve the full range (years 1 to 9999). |
| SQL Server Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
CHAR, VARCHAR | Utf8 | |
NCHAR, NVARCHAR | Utf8 | Unicode character strings. |
TEXT | Utf8 | Legacy large text type (deprecated). |
NTEXT | Utf8 | Legacy large Unicode text type (deprecated). |
UNIQUEIDENTIFIER | Utf8 | UUID represented as a string. |
| SQL Server Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
BINARY, VARBINARY | Binary | |
ROWVERSION | Binary | Automatically incremented binary identifier. See rowversion. |
IMAGE | Binary | Legacy large binary type (deprecated). |
| SQL Server Type(s) | Apache Arrow DataType |
|---|---|
XML | Utf8 |
GEOGRAPHY | Unsupported |
GEOMETRY | Unsupported |
HIERARCHYID | Unsupported |
Changelog
Last updated on