RDS SQL Source

[David_Fergen]

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