|
|
|
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 Integrate.io by executing
CREATE LOGIN flydata WITH PASSWORD = '<your_password>';
Grant necessary privileges
USE <enter database>
GO
CREATE USER flydata FOR LOGIN flydata WITH DEFAULT_SCHEMA=<enter schema>
GO
CREATE SCHEMA <enter schema> AUTHORIZATION flydata
GO
EXEC sp_addrolemember 'db_ddladmin', 'flydata';
EXEC sp_addrolemember 'db_datareader', 'flydata';
EXEC sp_addrolemember 'db_datawriter', 'flydata';
EXEC sp_addrolemember 'db_securityadmin', 'flydata';
EXEC sp_addsrvrolemember 'flydata', 'bulkadmin';
GO
Enable CDC for the database
- Go to AWS RDS dashboard
- Enable change data capture by running
EXEC sys.rds_cdc_enable_db
GO
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