Microsoft SQL Server 2008 - 更改数据捕获 (CDC)
4.80/5 (14投票s)
CDC 是一项用于记录 DML 更改的新功能。本文将介绍如何使用 CDC 以及它如何高效地实现我们的目的。
引言
Microsoft SQL Server 2008 引入了一项非常激动人心的功能，用于记录 DML 更改。以前，每当需要跟踪 ETL 作业或手动 DML 对关键表所做的所有修改时，开发人员都需要创建触发器并将更改记录在显式表中。随着 2008 的推出，所有更改跟踪都在产品层面实现了自动化。变更数据捕获报告可满足所有此类需求。本文将介绍如何使用 CDC 以及它如何高效地实现我们的目的。
注意：变更数据捕获仅在 SQL Server 2008 企业版、开发人员版和评估版中可用。
本文将引导读者完成 CDC 配置设置，并使用设置的 CDC 监控修改间隔之间的更改。本文的后半部分将为读者提供有关 CDC 实用性的见解，即 CDC 如何帮助找出 ETL 作业修改的对象记录集上的更改。例如，假设一个 SSIS 包每天东部时间凌晨 4 点运行，并转换表 ABC 上的数据。此作业在 2011 年 3 月 4 日运行所修改的记录（插入/更新/删除）可以通过 CDC 有效地记录下来以供分析。实用性部分将详细介绍这一点。
配置设置
CDC 默认禁用，必须在数据库级别启用，然后在表上启用。要启用 CDC，请执行 EXECUTE sys.sp_cdc_enable_db; 如果 CDC 成功启用，它将返回 0，如果出现任何故障，则返回 1。CDC 无法在任何系统数据库上启用。要验证 CDC 是否已在数据库上启用，请查询 sys.databases。只有用户数据库才能参与 CDC。为了能够启用 CDC，用户的服务器角色必须是 SYSADMIN。
我们将通过一个示例紧密跟踪这一点。
CREATE DATABASE TestDB;
Use TestDB
GO
CREATE TABLE Employee(
EID INT IDENTITY(1,1)PRIMARY KEY,
ENAME VARCHAR(50),
DEPT VARCHAR(20)
);
INSERT INTO Employee
Values ('Rambo','IT'),
('Jason','Finance'),
('Brad','HR');
/*This is a new feature in SQL Server 2008 for constructing your
Insert query to be able to handle multiple inserts in one go. */
SELECT * FROM Employee;
/*The output of our select statement shows three records inserted.
EID ENAME DEPT
----------- ----------------------------------------------- -----------
1 Rambo IT
2 Jason Finance
3 Brad HR
(3 row(s) affected)*/
/*Enabling CDC on the Database TestDB*/
Use TestDB
GO
EXEC sys.sp_cdc_enable_db
--This would create a User 'CDC' and Schema 'CDC'
SELECT name,is_cdc_enabled FROM sys.databases WHERE name='TestDB'
/*
name is_cdc_enabled
-------- --------------
TestDB 1
(1 row(s) affected)
CDC enabled successfully on the DB TestDB
*/
CDC 已在 TestDB 数据库上启用；此时，将创建用户“CDC”和架构“CDC”。接下来，需要在需要捕获更改的特定表上启用 CDC。在此示例中，Employee 表是 CDC 的候选表，因此在 Employee 上启用 CDC。
EXEC sys.sp_cdc_enable_table
@source_schema ='dbo',
@source_name ='Employee',
@role_name ='EmpCDCRole',
@supports_net_changes = 1
/*Messages Returned
Job 'cdc.TestDB_capture' started successfully.
Job 'cdc.TestDB_cleanup' started successfully.*/
SELECT name,is_tracked_by_cdc FROM sys.tables WHERE name='Employee'
/*
name is_tracked_by_cdc
-------- -----------------
Employee 1
(1 row(s) affected)*/
sys.sp_cdc_enable_table 命令在表上启用 CDC。需要注意的重要一点是，需要为 CDC 创建一个新角色“EmpCDCRole”。不与 db_owner 角色关联但需要访问 CDC 详细信息的用户应与此角色关联。其次，如果需要将记录上的所有更改汇总为净更改，则需要将 @supports_net_changes 参数设置为 1。如果设置为 1，那就很好。
此命令成功执行后，将在 SQL 代理中创建两个新作业来捕获和清理更改。为确保 CDC 已在表上启用，请验证 sys.tables 中 name='Employee' 的 is_tracked_by_cdc 列是否已设置为 1。
要禁用表上的 CDC，需要执行 sys.sp_cdc_disable_table。
EXEC sys.sp_cdc_disable_table
@source_schema ='dbo',
@source_name ='Employee',
@capture_instance ='dbo_Employee'
同样，要在数据库级别禁用 CDC，我们需要 EXEC sys.sp_cdc_disable_db。
请记住检查 sys.databases 和 sys.tables，并在禁用查询完成执行后验证 CDC 是否已正确设置为 0（禁用）。
演示
本节演示了更改数据的捕获。已在数据库 TestDB 和表 Employee 上配置 CDC。现在，触发一些 DML，以便 CDC 能够捕获更改。
DELETE FROM Employee WHERE EID=3
INSERT INTO Employee VALUES('Mary','HR')
UPDATE Employee SET ENAME='Nichole' WHERE EID=2
UPDATE Employee SET ENAME='EMMA' WHERE EID=2
SELECT * FROM Employee
/*EID ENAME DEPT
----------- ----------------------------------- --------------
1 Rambo IT
2 EMMA Finance
4 Mary HR
(3 row(s) affected)*/
已触发一次删除、一次插入和两次更新，最终表如上所示。要查看捕获到的更改，SQL 提供了两个函数
cdc.fn_cdc_get_net_changes_dbo_Employee和cdc.fn_cdc_get_all_changes_dbo_Employee
顾名思义，“净更改”函数给出记录的净更改，“所有更改”函数提供 DML 执行之前和之后的所有更改。
DECLARE @Begin_LSN binary(10), @End_LSN binary(10)
-- get the first LSN
SELECT @Begin_LSN =sys.fn_cdc_get_min_lsn('dbo_Employee')
-- get the last LSN
SELECT @End_LSN =sys.fn_cdc_get_max_lsn()
-- returns net changes
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Employee(
@Begin_LSN, @End_LSN,'ALL');
/*__$start_lsn __$operation __$update_mask EID ENAME DEPT
---------------------- ------------ ---------------- ----------- ----- -----
0x0000001C000000610004 1 NULL 3 Brad HR
0x0000001C000000620004 2 NULL 4 Mary HR
0x0000001C0000006E0004 4 NULL 2 EMMA Finance
(3 row(s) affected)*/
LSN
所有更改都以 LSN（日志序列号）的形式记录。SQL 通过日志序列号明确标识 DML 的每个操作。对任何表进行的任何已提交修改都会在数据库的事务日志中记录，并带有 SQL Server 提供的特定 LSN。__$operation 列的值为：1 = 删除，2 = 插入，3 = 更新（更新前的值），4 = 更新（更新后的值）。
cdc.fn_cdc_get_net_changes_dbo_Employee 为我们提供了落在我们函数中提供的 LSN 之间的所有记录的净更改。net_change 函数返回了三条记录；有一次删除、一次插入和两次更新，但都在同一条记录上。对于更新的记录，它简单地显示两次更新完成后净更改的值。
要获取所有更改，请执行 cdc.fn_cdc_get_all_changes_dbo_Employee；可以选择传递“ALL”或“ALL UPDATE OLD”。“ALL”选项提供所有更改，但对于更新，它提供更新后的值。因此，我们找到两条更新记录。我们有一条记录显示第一次更新，当 Jason 更新为 Nichole 时，以及一条记录，当 Nichole 更新为 EMMA 时。
DECLARE @Begin_LSN binary(10), @End_LSN binary(10)
-- get the first LSN
SELECT @Begin_LSN =sys.fn_cdc_get_min_lsn('dbo_Employee')
-- get the last LSN
SELECT @End_LSN =sys.fn_cdc_get_max_lsn()
-- returns all changes only after update values for ‘UPDATE’
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Employee(
@Begin_LSN, @End_LSN,'ALL');
/*
__$start_lsn __$seqval __$opera __$update EID ENAME DEPT
tion _mask
---------------------- ---------------------- --------- --------- --- ------- -----
0x0000001C000000610004 0x0000001C000000610002 1 0x07 3 Brad HR
0x0000001C000000620004 0x0000001C000000620003 2 0x07 4 Mary HR
0x0000001C0000006D0004 0x0000001C0000006D0002 4 0x02 2 Nichole Finance
0x0000001C0000006E0004 0x0000001C0000006E0002 4 0x02 2 EMMA Finance
(4 row(s) affected)
*/
“ALL UPDATE OLD”选项更进一步，在更新的情况下提供记录的之前和之后图像。更新之前（__$operation code=3）和之后（__$operation code=4）的记录已被 CDC 捕获。最初，Jason 更新为 Nichole，然后 Nichole 再次更新为 EMMA。
DECLARE @Begin_LSN binary(10), @End_LSN binary(10)
-- get the first LSN
SELECT @Begin_LSN =sys.fn_cdc_get_min_lsn('dbo_Employee')
-- get the last LSN
SELECT @End_LSN =sys.fn_cdc_get_max_lsn()
-- returns all changes both before & after update values for ‘UPDATE’
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Employee(
@Begin_LSN, @End_LSN,'ALL UPDATE OLD');
/*
__$start_lsn __$seqval __$oper __$update EID ENAME DEPT
ation _mask
---------------------- ---------------------- --------- --------- --- ------- --------
0x0000001C000000610004 0x0000001C000000610002 1 0x07 3 Brad HR
0x0000001C000000620004 0x0000001C000000620003 2 0x07 4 Mary HR
0x0000001C0000006D0004 0x0000001C0000006D0002 3 0x02 2 Jason Finance
0x0000001C0000006D0004 0x0000001C0000006D0002 4 0x02 2 Nichole Finance
0x0000001C0000006E0004 0x0000001C0000006E0002 3 0x02 2 Nichole Finance
0x0000001C0000006E0004 0x0000001C0000006E0002 4 0x02 2 EMMA Finance
(6 row(s) affected)
*/
要获取被捕获列的详细信息，可以使用 sys.sp_cdc_get_captured_columns 函数。这会返回指定捕获实例跟踪的捕获源列的变更数据捕获元数据信息。
EXEC sys.sp_cdc_get_captured_columns
@capture_instance ='dbo_Employee';
EXEC sys.sp_cdc_help_change_data_capture
要报告表的捕获实例，请运行 sys.sp_cdc_help_change_data_capture 存储过程。
公用事业
CDC 的精髓可以有效利用。让我们看一个非常简单的实用性演示。如果有一些 ETL 作业正在转换（插入/更新/删除）CDC 扫描下的目标表，则可以制定一个计划来监控 ETL 作业在每次特定运行期间进行的更改。例如，如果 SISS 包每天东部时间凌晨 4 点运行，则可以设置一个配置，以便能够识别作业在 2011 年 3 月 4 日东部时间凌晨 4 点对数据所做的更改。
CREATE TABLE PROCESS
(Process_ID INTIDENTITY (1,1),
Process_Date DATETIME,
BEGIN_LSN BINARY(10),
END_LSN BINARY(10))
已创建一个表，用于记录每次作业运行期间的最小和最大 LSN。当 ETL 作业运行时，它将使用 Process_ID（为了演示目的，我已将 Process_ID 声明为标识；它也可以采用正在运行作业的 job_id）、其运行的日期时间戳以及作业修改的记录的最小和最大 LSN 进行条目。为了在作业运行时将详细信息记录到创建的表中，已创建以下存储过程。作业可以在退出之前的最后一步调用并执行该过程。
IF EXISTS(SELECT 1 FROM TestDB.sys.objects
WHERE name='ProcessLogEntry'AND [type]='P')
DROP PROCEDURE ProcessLogEntry
GO
CREATE PROCEDURE dbo.ProcessLogEntry
AS
SET NOCOUNTON
SET QUOTED_IDENTIFIERON
SET ANSI_NULLSON
BEGIN
DECLARE @BEGIN_LSN BINARY(10)
IF ((SELECT COUNT(1)FROM PROCESS)>0)
BEGIN
SELECT @BEGIN_LSN=MAX(END_LSN)FROM PROCESS
INSERT INTO PROCESS SELECT GETDATE(),
@BEGIN_LSN,sys.fn_cdc_get_max_lsn();
END
ELSE
INSERT INTO PROCESS SELECT GETDATE(),
sys.fn_cdc_get_min_lsn('dbo_employee'),sys.fn_cdc_get_max_lsn();
END;
在此演示会话中，我们手动执行该过程；它为我们之前执行的 DML（即：一次插入、一次删除和两次更新）创建 LSN 条目。
EXEC ProcessLogEntry
SELECT * FROM PROCESS
Process_ID Process_Date BEGIN_LSN END_LSN
----------- ----------------------- ---------------------- ----------------------
1 2011-03-04 17:33:04.667 0x0000001800000020003A 0x0000001E000000370001
(1 row(s) affected)
如日志所示，让我们执行更多 DML
INSERT INTO Employee VALUES('Sachin','CEO')
UPDATE Employee SET DEPT='Services' WHERE EID=2
我们已插入一条记录并更新了 EID 2 的部门。要记录更改，我们再次执行 ProcessLogEntry 过程。
EXEC ProcessLogEntry
SELECT * FROM PROCESS
Process_ID Process_Date BEGIN_LSN END_LSN
----------- ----------------------- ---------------------- ----------------------
1 2011-03-04 17:33:04.667 0x0000001800000020003A 0x0000001E000000370001
2 2011-03-04 17:38:32.490 0x0000001E000000370001 0x0000001E000000A80004
(2 row(s) affected)
第二个条目已在我们的 Process 表中创建。现在，这项工作使我们能够获取作业在任何特定日期所做的更改。下面的代码获取 process_id =2 所做的净更改。
DECLARE @Begin_LSN BINARY(10), @End_LSN BINARY(10)
SELECT @Begin_LSN = BEGIN_LSN FROM PROCESS WHERE Process_ID=2
SELECT @End_LSN = END_LSN FROM PROCESS WHERE Process_ID=2
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Employee(
@Begin_LSN, @End_LSN,'ALL');
__$start_lsn __$operation __$update_mask EID ENAME DEPT
---------------------- ------------ -------------- --- -------- -------
0x0000001E000000A40004 2 NULL 5 Sachin CEO
0x0000001E000000A80004 4 NULL 2 EMMA Services
(2 row(s) affected)
激动人心，不是吗？我们准确地获取了进程 2 所做的更改。
同样，下面的查询简单地获取了 process_id 1 所做的净更改。
DECLARE @Begin_LSN BINARY(10), @End_LSN BINARY(10)
SELECT @Begin_LSN = BEGIN_LSN FROM PROCESS WHERE Process_ID=1
SELECT @End_LSN = END_LSN FROM PROCESS WHERE Process_ID=1
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Employee(
@Begin_LSN, @End_LSN,'ALL');
/*__$start_lsn __$operation __$update_mask EID ENAME DEPT
---------------------- ------------ ---------------- ----------- ----- -----
0x0000001C000000610004 1 NULL 3 Brad HR
0x0000001C000000620004 2 NULL 4 Mary HR
0x0000001C0000006E0004 4 NULL 2 EMMA Finance
(3 row(s) affected)
希望这个演示在提供 CDC 见解方面有所帮助，并且实用性部分帮助了解如何使用 CDC。
结论
SQL Server 2008 通过引入审计功能，更进一步发展了这一点，这将在我的下一篇文章中讨论。审计功能在有效检查细粒度活动方面非常方便。DBA 可以非常有效地利用它来跟踪试图获取任何未经授权访问或执行 DML（更新/删除）的更精明的人。所有详细信息，例如谁触发了查询、时间戳和实际查询，都可以记录下来，以帮助 DBA 人员报告服务器上的任何不道德活动。这将大大提高服务器的安全性，并帮助组织保护其业务最重要的方面，即严格保密的信息。现在，借助 CDC 和审计这两个出色的功能，可以在非常细粒度的级别设计和实施安全策略。