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'
Watch video How CDC Works in Azure SQL Server Managed Instance | Change Data Capture In Managed Instance online without registration, duration hours minute second in high quality. This video was added by user MS SQL DBA Tech Support 17 May 2021, don't forget to share it with your friends and acquaintances, it has been viewed on our site 3,769 once and liked it 53 people.