SQL Server 变更数据捕获 (Change Data Capture)

SQL Server 变更数据捕获，简称 CDC，捕获对表所做的更改。例如，如果您想存储有关 UPDATE、INSERT 和 DELETE 操作的审计信息，请在该表上启用 CDC。

本章将通过一个示例解释如何启用 CDC、禁用 CDC 以及在单个列上启用 CDC。对于此 SQL Server 变更数据捕获演示，我们将使用 CDC 测试数据。从下面的屏幕截图可以看出，它只有一个表，即 Employee。

下面的屏幕截图将显示 Employee 表中的数据。

执行 SQL Server 变更数据捕获 (CDC)

实施 CDC 或变更数据捕获是一个两步法。首先，您必须在数据库上启用 CDC，然后才能在表上启用 CDC。

在数据库上实施 CDC

下面的代码片段将在数据库级别启用 CDC。

USE [CDC Testing]
GO
EXEC sys.sp_cdc_enable_db

Messages
--------
Command(s) completed successfully.

如果您观察下面的屏幕截图，启用 SQL Server 变更数据捕获会自动在系统表中创建六个 CDC 表。

以及一些系统存储过程，用于在服务器中实现变更数据捕获。

在表上实施 CDC

下面的 CDC 代码片段将在表级别启用 SQL 变更数据捕获。这里指的是 Employee 表。

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo', -- Schema Name. Example, HR, Sales etc
@source_name = N'Employee', -- Table Name
@role_name = NULL -- Any specified roles
GO

Messages
-------
Job 'cdc.CDC Testing_capture' started successfully.
Job 'cdc.CDC Testing_cleanup' started successfully.

从上面的屏幕截图可以看出，我们已成功在 Employee 表上实现了变更数据捕获。

假设您进入数据库。在系统表下，您将找到新表 cdc.dbo.Employee_CT（在您的情况下，Employee 将替换为表名）。

此表将存储审计信息（或捕获表更改）。

让我使用 INSERT 语句向表中插入新行。

INSERT INTO [dbo].[Employee] (
	[FirstName], [LastName], [Occupation], [YearlyIncome])
VALUES ('Tutorial', 'Gateway', 'Admin', 10000)

Messages
-------
(1 row(s) affected)

如果您检查 dbo_Employee_CT 表，您可以看到记录的操作号为 2（插入）。

接下来，我们将使用 UPDATE 语句执行更新

UPDATE [dbo].[Employee]
    SET [FirstName] = 'Dave'
    WHERE [EmpID] = 3

Messages
-------
(1 row(s) affected)

让我向您展示 Employee 表

如果您检查 dbo_Employee_CT 表，您可以看到操作号为 3 的旧记录（更新前的值）和操作号为 4 的新记录（已更新）。

最后，我们将使用 DELETE 语句执行删除操作

DELETE FROM [dbo].[Employee]
	WHERE [EmpID] = 4

Messages
-------
(1 row(s) affected)

您可以从下面的屏幕截图中看到，没有 Emp Id 为 4 的记录。

如果您检查 dbo_Employee_CT 表，您可以看到操作号为 1 的已删除记录（已删除）。

禁用 SQL Server 变更数据捕获 (CDC)

您必须遵循相同的步骤来禁用变更数据捕获 CDC。

在表上禁用 CDC

如果您知道捕获实例 (Schema.table_name)，请跳过此步骤并应用下一个代码。下面的存储过程将提供有关 CDC 变更数据捕获的信息。

EXEC sys.sp_cdc_help_change_data_capture 
GO

下面的代码片段将禁用数据库级别的变更数据捕获。我们需要该 capture_instance 来禁用它。

EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'Employee',
@capture_instance = dbo_Employee
GO

Messages
-------
Command(s) completed successfully.

禁用数据库上的变更数据捕获

在表级别禁用 CDC 后，您必须在数据库级别禁用变更数据捕获。下面的代码将执行相同的操作。

EXEC sys.sp_cdc_disable_db

Messages
-------
Command(s) completed successfully.

现在您可以看到所有系统生成的（CDC 生成的系统表）都已消失。

在多个列上启用 CDC

SQL Server 允许您在单个列上启用 CDC，而不是在整个表上启用变更数据捕获。在此示例中，我们将在 Emp ID 和 First Name 列上启用 CDC。

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Employee',
@role_name = NULL,
@captured_column_list = '[EmpID], [FirstName]'
GO

Messages
-------
Job 'cdc.CDC Testing_capture' started successfully.
Job 'cdc.CDC Testing_cleanup' started successfully.

让我向员工表插入一条新记录。

INSERT INTO [dbo].[Employee] (
	[FirstName], [LastName], [Occupation], [YearlyIncome])
VALUES ('SQL', 'Server', 'Tutorial', 20000)

Messages
--------
(1 row(s) affected)

从下面的屏幕截图可以看出，我们为名字、姓氏、职业和年收入插入了一条新记录。但是，它只捕获有关 Emp Id 和名字的信息。

在结束本文之前，让我向您展示 Captured_Columns 中的数据。正如您所见，它存储了变更数据捕获捕获的所有列。