--1 - 开启cdc
USE Test;
EXECUTE sys.sp_cdc_enable_db;
GO
--2 - 查看是否设置成功,1代表开启cdc
SELECT is_cdc_enabled FROM SYS.databases WHERE name='Test'
--3 - 对表启用捕获
CREATE TABLE Student
(
StudentID VARCHAR(50) PRIMARY KEY,
StudentName NVARCHAR(200),
StudentAddress NVARCHAR(200),
UpdateTime DATETIME
)
--SELECT * FROM dbo.Student--需要设置主键
EXEC sys.sp_cdc_enable_table
@source_schema= 'dbo',
@source_name = 'Student',
@role_name = N'cdc_Admin',
@capture_instance = DEFAULT,
@supports_net_changes = 1,
@index_name = NULL,
--@captured_column_list = NULL,--所有列
@captured_column_list = '[StudentID],[StudentName], [StudentAddress]',--设置需要捕获所有列
@filegroup_name = DEFAULT
--3.1 - 检查是否成功
SELECT name, is_tracked_by_cdc,
CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述,*
FROM sys.tables
WHERE OBJECT_ID= OBJECT_ID('dbo.Student')
--3.2 - 返回某个表的变更捕获配置信息
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Student'
--3.3 - 查询捕获数据
--cdc.dbo_Student_CT 表是根据上述设置自动生成的
SELECT * FROM cdc.dbo_Student_CT
--3.4 关闭CDC
EXEC sys.sp_cdc_disable_table
@source_schema= 'dbo',
@source_name = 'Student',
@capture_instance = 'dbo_Student'
--4、创建同步配置表
CREATE TABLE Test..Student_CDC_Config
(
TableName VARCHAR(50) PRIMARY KEY,
Last_lsn BINARY(10)
);
--4.1 初始化同步配置表
--TRUNCATE TABLE dbo.Student_CDC_Config
--SELECT * FROM Student_CDC_Config
--DELETE FROM dbo.Student_CDC_Config WHERE TableName = 'Student';
INSERT INTO dbo.Student_CDC_Config
(
TableName,
Last_lsn
)
SELECT
'Student' AS TableName,
MAX(start_lsn) AS Last_lsn
FROM
Test.[cdc].[lsn_time_mapping];
--5.1测试
SELECT * FROM Student
--获取两条插入记录
INSERT INTO dbo.Student(StudentID,StudentName,StudentAddress,UpdateTime)
VALUES('03C06ED2-B4DB-43D6-BF23-201E85D6A1D0','学生1','地址1',GETDATE());
INSERT INTO dbo.Student(StudentID,StudentName,StudentAddress,UpdateTime)
VALUES('CCB4DBA6-F73D-4D06-9CD1-D15F2420A0AE','学生2','地址2',GETDATE());
--字段“__$operation”为“1”代表删除,“2”代表插入,“3”执行更新操作前的值,“4”执行更新操作后的值。
--字段“__$start_lsn”由于更改是来源于数据库的事务日志,所以这里会保存其事务日志的开始序列号(LSN)
SELECT * FROM cdc.dbo_Student_CT
--获取修改记录
--获取一条原始数据,一条新数据
UPDATE dbo.Student SET StudentName='学生11' WHERE StudentID='CCB4DBA6-F73D-4D06-9CD1-D15F2420A0AE'
SELECT * FROM cdc.dbo_Student_CT
--无法获取修改记录
--原因是UpdateTime不是捕获列
UPDATE dbo.Student SET UpdateTime=GETDATE() WHERE StudentID='03C06ED2-B4DB-43D6-BF23-201E85D6A1D0'
SELECT * FROM cdc.dbo_Student_CT