RDS SQL Source

David_Fergen — Fri, 05 Feb 2021 22:03:29 GMT

When using RDS SQL Source, there is a requirement to enable CDC in the Database and in the Tables:

To enable CDC in the Database:

exec msdb.dbo.rds_cdc_enable_db 'db_name'

To enable CDC for a single table:

exec sys.sp_cdc_enable_table _schema = N'db_name', _name = N'table_name', @role_name = NULL, @supports_net_changes = 1 GO

To enable CDC for all the tables in schema in the Database use the following procedure:

1 – Create the Store Procedure Below:

create procedure sp_enable_disable_cdc_all_tables(@dbname varchar(100), @enable bit, @schemaname varchar(100)) as BEGIN TRY DECLARE _name varchar(400) declare @sql varchar(1000) DECLARE the_cursor CURSOR FAST_FORWARD FOR SELECT table_name FROM INFORMATION_SCHEMA.TABLES where TABLE_CATALOG=@dbname and table_schema=@schemaname and table_name != 'systranschemas' OPEN the_cursor FETCH NEXT FROM the_cursor INTO _name WHILE @@FETCH_STATUS = 0 BEGIN if @enable = 1 set @sql =' Use '+ @dbname+ ';EXEC sys.sp_cdc_enable_table _schema = '+@schemaname+',@source_name = '+@source_name+' , @role_name = NULL,@supports_net_changes = 1' else set @sql =' Use '+ @dbname+ ';EXEC sys.sp_cdc_disable_table _schema = '+@schemaname+',@source_name = '+@source_name+', @capture_instance =''all''' exec(@sql) FETCH NEXT FROM the_cursor INTO _name END CLOSE the_cursor DEALLOCATE the_cursor SELECT 'Successful' END TRY BEGIN CATCH CLOSE the_cursor DEALLOCATE the_cursor SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH

2 – Execute the Store Procedure above using the command below:

exec sp_enable_disable_cdc_all_tables @dbname=DB_NAME, @enable=1, @schemaname=SCHEMA_NAME

* Replace DB_NAME and SCHEMA_NAME accordingly

In order to DISABLE CDC in all table, run the line above with @enable=0:

exec sp_enable_disable_cdc_all_tables @dbname=DB_NAME, @enable=0, @schemaname=SCHEMA_NAME

article RDS SQL Source in Official Support Articles

RDS SQL Source