SQL Server
SQL Server's Change Data Capture (CDC) through capture tables enables reliable, low impact change capture for data integration. Supermetal builds on this foundation to deliver efficient data extraction and replication with minimal source database overhead.
This guide covers features and configuration steps required to connect SQL Server with Supermetal, focusing on CDC setup and permissions.
Features
| Feature | Notes |
|---|---|
| Initial Data Sync | |
| Change Data Capture | |
| CDC Processing Modes | |
| Configurable CDC Processing | |
| Catalog Support |
Prerequisites
Before you begin, ensure you have:
-
SQL Server Requirements:
- Version: SQL Server 2016 or higher
- Supported Editions:
Standard SQL Server(Enterprise, Standard, Personal, Azure SQL Managed Instance)Azure SQL DatabaseAmazon RDS for SQL Server
Unsupported SQL Server Editions
CDC is not supported on
Azure Synapse,Azure SQL Edge, orAzure Synapse Serverless. -
Setup Requirements:
- Database Permissions: Varies by replication type (see Setup for role-specific instructions)
- Network Connectivity: Ensure Supermetal can reach your SQL Server (default port: 1433)
Setup
Supermetal supports two replication types for SQL Server sources:
- Snapshot: One-time or periodic full table snapshots.
- Capture Table Replication: Real-time change data capture (CDC) with initial snapshot.
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) | Uses SQL Server precision and scale 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) | Second-level precision, minute-level accuracy (rounds to nearest minute). |
DATETIME | Timestamp(ms) | Millisecond precision, 3.33ms accuracy. |
DATETIME2(0) | Timestamp(s) | Second precision. |
DATETIME2(1-3) | Timestamp(ms) | Millisecond precision (1-3 fractional digits). |
DATETIME2(4-6) | Timestamp(μs) | Microsecond precision (4-6 fractional digits). |
DATETIME2(7) | Utf8 | Mapped to string to preserve full range (years 1-9999). |
DATETIMEOFFSET(0) | Timestamp(s, "UTC") | Second precision, normalized to UTC. |
DATETIMEOFFSET(1-3) | Timestamp(ms, "UTC") | Millisecond precision (1-3 fractional digits), normalized to UTC. |
DATETIMEOFFSET(4-6) | Timestamp(μs, "UTC") | Microsecond precision (4-6 fractional digits), normalized to UTC. |
DATETIMEOFFSET(7) | Utf8 | Mapped to string to preserve full range (years 0001-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). Use VARCHAR(MAX) instead. |
NTEXT | Utf8 | Legacy large Unicode text type (deprecated). Use NVARCHAR(MAX) instead. |
UNIQUEIDENTIFIER | Utf8 | UUID/GUID 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). Use VARBINARY(MAX) instead. |
| SQL Server Type(s) | Apache Arrow DataType |
|---|---|
XML | Utf8 |
GEOGRAPHY | Unsupported |
GEOMETRY | Unsupported |
HIERARCHYID | Unsupported |
Last updated on