SQL Server

SQL Server is Microsoft's enterprise relational database management system with robust transaction processing and business intelligence capabilities.

Supported Versions and Architectures

• Versions: SQL Server 2008, 2008 R2, 2012, 2014, 2016, 2017, 2019, 2022
• Architectures: Single-node deployments

Supported Data Types

Category	Data Types
String	char, varchar, nchar, nvarchar, text, ntext, varchar(max), nvarchar(max)
Integer	tinyint, smallint, int, bigint
Decimal	decimal, numeric, money, real, float
Date/Time	date, time, datetime, datetime2, smalldatetime, datetimeoffset
Binary	binary, varbinary, varbinary(max), timestamp
Others	uniqueidentifier, bit, image, xml, geography

Limitations

• CDC constraints: Tables with CDC cannot use TRUNCATE TABLE or sp_rename
• DDL limitations: Limited DDL capture support; may cause data loss if DDL/DML occur simultaneously
• Large fields: CDC processes only up to 64KB by default for large fields
• Table-level CDC: CDC can only be enabled/disabled at table level
• SQL Server 2005: Not supported (no CDC capability)

Quick Setup Guide

1. Create Database User

-- Create login account
CREATE LOGIN xpipes WITH PASSWORD='your_password', DEFAULT_DATABASE=your_database;

-- Create database user
CREATE USER xpipes FOR LOGIN xpipes WITH DEFAULT_SCHEMA=dbo;

2. Grant Permissions

Full sync only

GRANT SELECT ON SCHEMA::dbo TO xpipes;

Full + incremental sync

GRANT SELECT ON SCHEMA::dbo TO xpipes;
GRANT SELECT ON SCHEMA::cdc TO xpipes;

3. Configure CDC (for incremental sync)

Enable SQL Server Agent, then enable CDC:

-- Enable CDC at database level
USE your_database;
GO
EXEC sys.sp_cdc_enable_db;
GO

-- Enable CDC for specific table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'your_table',
    @role_name = NULL,
    @supports_net_changes = 1;
GO

4. CDC Maintenance

Configure CDC cleanup job:

EXEC sys.sp_cdc_change_job
    @job_type = N'cleanup',
    @retention = 1440; -- 24 hours in minutes