ELT/CDC: Sources - Microsoft SQL Server

Documentation Index

Fetch the complete documentation index at: https://www.integrate.io/docs/llms.txt

Use this file to discover all available pages before exploring further.

​

Setting up Microsoft SQL Server CDC for ELT & CDC

​

Overview

​

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 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

EXEC sys.rds_cdc_enable_db

​

Enable CDC for the needed tables

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

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

​

Related