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'
Смотрите видео How CDC Works in Azure SQL Server Managed Instance | Change Data Capture In Managed Instance онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь MS SQL DBA Tech Support 17 Май 2021, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 3,769 раз и оно понравилось 53 людям.