关于CDC的研究

为了实现数据库的实时监控，表数据的改变难以捕获，是我们目前所遇到的问题，而这几天我注意到一个可参考的方案，可以解决这一问题，CDC。

概念原理：

       CDC又称变更数据捕获（Change Data Capture），开启cdc的源表在插入INSERT、更新UPDATE和删除DELETE活动时会插入数据到日志表中。CDC通过

捕获进程将变更数据捕获到变更表中，通过cdc提供的查询函数，我们可以捕获这部分数据。

1.SQL server 2008版本以上的企业版、开发版和评估版中可用；

2.需要开启代理服务（作业）。

3.CDC需要业务库之外的额外的磁盘空间。

4.CDC的表需要主键或者唯一主键。

  正式设置：   

  一， 开启代理服务：

windows环境下，可以直接在服务中找到SQL server代理，设为自动打开即可。

Linux环境下：

   sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

   sudo docker restart <container ID>

二，创建测试环境：

1.

/******* Step1:创建示例数据库*******/
USE master GO
IF EXISTS(SELECT name FROM sys.databases WHERE name = ‘CDC_DB‘) DROP DATABASE CDC_DB GO
CREATE DATABASE CDC_DB GO

2.

/******* Step2:开启数据库CDC *******/
--查看数据库是否启用CDC
SELECT name,is_cdc_enabled FROM sys.databases WHERE name = ‘CDC_DB‘

--启用数据库CDC
USE CDC_DB GO
EXECUTE sys.sp_cdc_enable_db; GO

--检查启用是否成功
SELECT is_cdc_enabled,CASE WHEN is_cdc_enabled=0 THEN ‘CDC功能禁用‘ ELSE ‘CDC功能启用‘ END 描述 FROM sys.databases WHERE NAME = ‘CDC_DB‘

3.

/******* Step3:对表启用变更捕获*******/
--创建测试表
USE CDC_DB GO
CREATE TABLE [dbo].[Department]( [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](200) NULL, [GroupName] [nvarchar](50) NOT NULL,

 [ModifiedDate] [datetime] NOT NULL, [AddName] [nvarchar](120) NULL, CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED ( [DepartmentID] ASC ) ON [PRIMARY] ) ON [PRIMARY]
GO

--对表启用捕获
EXEC sys.sp_cdc_enable_table @source_schema= ‘dbo‘, @source_name = ‘Department‘, @role_name = N‘cdc_Admin‘, @capture_instance = DEFAULT,

 @supports_net_changes = 1, @index_name = NULL, @captured_column_list = NULL, @filegroup_name = DEFAULT

--检查是否成功
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.Department‘)

/******* Step4:测试DML变更捕获*******/
--测试插入数据
INSERT  INTO dbo.Department( Name , GroupName , ModifiedDate )VALUES(‘Marketing‘,‘Sales and Marketing‘,GETDATE()) --测试更新数据
UPDATE dbo.Department SET Name = ‘Marketing Group‘,ModifiedDate = GETDATE() WHERE Name = ‘Marketing‘

--测试删除数据
DELETE FROM dbo.Department WHERE Name=‘Marketing Group‘

--查询捕获数据
SELECT * FROM cdc.dbo_Department_CT

对于insert/delete操作，会有对应的一行记录，而对于update，会有两行记录。__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）；

如图：

关于CDC的研究

原文：https://www.cnblogs.com/ffhaha/p/11937252.html