Microsoft SQL Server source for ELT & CDC


Description	SQL Server
Type	CDC/Binlog Replication
Supported Replication	Initial Sync, Continuous Sync
Authentication Type	Password Authentication

Description

SQL Server

Type

CDC/Binlog Replication

Supported Replication

Initial Sync, Continuous Sync

Authentication Type

Password Authentication

Setting up Microsoft SQL Server CDC for ELT & CDC

Overview

ELT & CDC uses change data capture (CDC) for Microsoft SQL Server.

Requirements

Microsoft SQL Server 2008 (10.0) version or above.

Sysadmin permissions are required for SQL Server or Azure SQL Managed.

The db_owner role is required to enable change data capture for Azure SQL Database.

CDC must be enabled for the database.

CDC must be enabled for the needed tables.

Supported providers

RDS SQL Server

Azure SQL Database

Others (self hosted)

Setting up Microsoft SQL Server for Integrate.io

Create role for sync

Create a sync user for ELT & CDC by executing

CREATE LOGIN integrateio WITH PASSWORD = '<your_password>';

Grant the privileges for the database and schema by running the following queries

USE <enter database>
GO
CREATE USER integrateio FOR LOGIN integrateio WITH DEFAULT_SCHEMA=<enter schema>
GO
CREATE SCHEMA <enter schema> AUTHORIZATION integrateio
GO
EXEC sp_addrolemember 'db_ddladmin', 'integrateio';
EXEC sp_addrolemember 'db_datareader', 'integrateio';
EXEC sp_addrolemember 'db_datawriter', 'integrateio';
EXEC sp_addrolemember 'db_securityadmin', 'integrateio';
EXEC sp_addsrvrolemember 'integrateio', 'bulkadmin';
GO

Enable CDC for the database

Go to AWS RDS dashboard

Enable change data capture by running

EXEC sys.rds_cdc_enable_db

Enable CDC for the needed tables

Go to AWS RDS/Azure dashboard (you may omit this step for others/self hosted)

Enable change data capture by running

EXEC sys.sp_cdc_enable_table  
@source_schema = N'MySchema',
@source_name   = N'MyTable',
@role_name     = N'flydata',
@supports_net_changes = 1
GO

or enable change data capture for all tables by running

DECLARE @Schema AS VARCHAR(300)
DECLARE @Table AS VARCHAR(300)
DECLARE @cdc_Role AS VARCHAR(300)
SET @cdc_Role = 'flydata';
DECLARE cdc_cursor CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';
OPEN cdc_cursor
FETCH NEXT FROM cdc_cursor INTO @Schema, @Table
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sys.sp_cdc_enable_table
@source_schema = @Schema,
@source_name = @Table,
@role_name = @cdc_Role;
PRINT 'CDC enabled for ' + @Schema + '.' +@Table + ' for ' + @cdc_Role;
FETCH NEXT FROM cdc_cursor INTO @Schema, @Table
END
CLOSE cdc_cursor
DEALLOCATE cdc_cursor

Getting Started

Pipelines

Sources

Destinations

Monitoring

Security

Transforms

Data Observability

Microsoft SQL Server source for ELT & CDC

Setting up Microsoft SQL Server CDC for ELT & CDC

Overview

Requirements

Supported providers

Setting up Microsoft SQL Server for Integrate.io

Create role for sync

Grant the privileges for the database and schema by running the following queries

Enable CDC for the database

Enable CDC for the needed tables

SSH Tunnel

IP Allowlist

Hooks

​Setting up Microsoft SQL Server CDC for ELT & CDC

​Overview

​Requirements

​Supported providers

​Setting up Microsoft SQL Server for Integrate.io

​Create role for sync

​Grant the privileges for the database and schema by running the following queries

​Enable CDC for the database

​Enable CDC for the needed tables

​Related

SSH Tunnel

IP Allowlist

Hooks

Setting up Microsoft SQL Server CDC for ELT & CDC

Overview

Requirements

Supported providers

Setting up Microsoft SQL Server for Integrate.io

Create role for sync

Grant the privileges for the database and schema by running the following queries

Enable CDC for the database

Enable CDC for the needed tables

Related