CDC setup in SQL Server

SQL Server CDC setup on Linux

• Create a User for Sprinkle using your own password

Use <databasename>
CREATE LOGIN sprinkle WITH PASSWORD = 'password';
CREATE USER sprinkle FOR LOGIN sprinkle; 

• Give select permission on the database

GRANT SELECT on DATABASE::<database> to sprinkle;

• Enable Change Data Capture. To turn on Change Data Capture first, you need to enable it at the database level.

USE [<database>];
EXEC sys.sp_cdc_enable_db

• Next, you need to enable it for each table you would like to integrate

EXEC sys.sp_cdc_enable_table
@source_schema = '<schema>',
@source_name   = <table>,
@role_name     = 'MyRole',
@supports_net_changes = 0

• Test by listing tables enabled with CDC (the below command should be run with the same user as used to connect)

EXEC sys.sp_cdc_help_change_data_capture;

• Start Sql server agent if not already running

Command definitions used above:

• USE- Changes the database context to the specified database or database snapshot in SQL Server.

• CREATE LOGIN - Creates a login for SQL Server, SQL Database, Azure Synapse Analytics, or Analytics Platform System databases.

• CREATE USER - Adds a user to the current database.

• EXEC - Executes a command string or character string within a Transact-SQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, CLR stored procedure, scalar-valued user-defined function, or extended stored procedure.

• GRANT - Grants permissions on a database in SQL Server.

AZURE SQL Server CDC Setup

Run the following commands to enable CDC in Azure:

• Enable Change Data CaptureTo turn on Change Data Capture first, you need to enable it at the database level.

USE [<database>];
EXEC sys.sp_cdc_enable_db

• Next, you need to enable it for each table you would like to integrate.

EXEC sys.sp_cdc_enable_table
@source_schema = '<schema>',
@source_name   = <table>,
@role_name     = 'MyRole',
@supports_net_changes = 0

• Test by listing tables enabled with CDC (the below command should be run with the same user as used to connect):

EXEC sys.sp_cdc_help_change_data_capture;

Column updates with CDC enabled

Adding new column

Adding a new column to table requires recreating cdc capture instance. Explaining the steps required with below example

We have a table named sample contains id and name as columns,

This block of code executes only when CDC is not enabled, if enabled then we don’t have to write this block(make sure capture_instance is there if it’s already enabled.)

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
 @source_name = N'sample',
  @role_name = NULL, @capture_instance = 'dbo_sample'

Query to add column

ALTER TABLE dbo.sample ADD TestColumn int

Code Block to create new capture instance for same table

EXEC sys.sp_cdc_enable_table 
     @source_schema = N'dbo',
     @source_name = N'sample', 
     @role_name = NULL,
     @capture_instance = 'dbo_sample2'

Shifting all data from older instance to newer instance

INSERT INTO cdc.dbo_sample2_CT 
($start_lsn, $end_lsn,$seqval,$operation,__$update_mask,id,name) 
   SELECT __$start_lsn, __$end_lsn, __$seqval, __$operation, __$update_mask, id, name 
   FROM cdc.dbo_sample_CT

Disabling the older instance, as 1 table can have only 2 capture instance

EXEC sys.sp_cdc_disable_table 
     @source_schema = N'dbo', 
     @source_name = N'sample', 
     @capture_instance = 'dbo_sample'

Dropping a column

Follow the above steps and reset the ingested table and run ingestion again, it will drop the column.(No boundation to follow above steps).

OR

Users can simply drop the column, using simple sql query and user can reset the ingested table and run ingestion again, it will drop the column. If not reset, then the values for column will come as null.

Updating column type

Users can simply update the data-type and reset the table, on running ingestion again,it will update the data type.