SQL Server

TwinCommit captures changes from Microsoft SQL Server by reading the active transaction log and log backups directly. You do not need to enable SQL Server CDC, change tables or SQL Server Agent jobs.

Supported: SQL Server 2016 through 2025 on Windows and Linux; Azure SQL Managed Instance.

Prerequisites

FULL recovery model

ALTER DATABASE Sales SET RECOVERY FULL;
BACKUP DATABASE Sales TO DISK = N'/var/opt/mssql/backup/Sales.bak';

Permissions

CREATE LOGIN twincommit WITH PASSWORD = '********';
GRANT VIEW SERVER STATE TO twincommit;
USE Sales;
CREATE USER twincommit FOR LOGIN twincommit;
ALTER ROLE db_owner ADD MEMBER twincommit;

Log backup access

If log backups run more often than TwinCommit can fall behind, give TwinCommit read access to the backup location (log_backup_dirs) so it can continue from backed-up log.

Primary keys

Every replicated table needs a primary key.

Configuration

source:
  type: sqlserver
  dsn: sqlserver://twincommit:${MSSQL_PASSWORD}@mssqlhost:1433?database=Sales
  log_backup_dirs: [/mnt/sqlbackup/Sales]

Limitations

• Memory-optimized tables and columnstore-only tables are not supported.
• sql_variant, hierarchyid and spatial types must be excluded.
• On Azure SQL Database (single database), use the Managed Instance or a VM instead.