How CDC Works in Azure SQL Server Managed Instance | Change Data Capture In Managed Instance

--CDC: Change Data capture
--CDC records INSERT, UPDATE,and DELETE operations performed on a table


--How to know on which databases cdc is enabled?
select name,is_cdc_enabled from sys.databases where name = 'test'
--If is_cdc_enabled is 0 then CDC is not enabled for the database
--If is_cdc_enabled is 1 then CDC is already enabled for the database name

--How to know on which tables cdc is enabled?
select name,is_tracked_by_cdc from sys.tables

----CDC is a two Step process
--1.Database level
--2.Table level

--Enable CDC on database level
EXEC sys.sp_cdc_enable_db
GO

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'test',
@role_name = Null

--The column __$operation records the operation that is associated with the change:

--1 = Delete
--2 = Insert
--3 = Update (old value)
--4 = Update (new value)


--CDC is depenedened on sql server agent and when cdc is enable two jobs will be created.
--[cdc.test_capture]

--[cdc.test_cleanup]

select * from test

insert into test values (1,'harsha','Pass')
insert into test values (2,'praveen','pass')

delete from test where id = 1

UPDATE test SET name = 'ravi' WHERE id = 2



--Clean Up
--Disable on Table Level
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'test',
@capture_instance = N'dbo_test'

--Disable CDC on database level
EXEC sys.sp_cdc_Disable_db
GO

select name,is_tracked_by_cdc from sys.tables
select name,is_cdc_enabled from sys.databases where name = 'test'