[一步一步] SQL Server 2008变更数据捕获

go4321

　　变更数据捕获
　　变更数据捕获使用异步进程读取事务日志，获取DML更改实际数据做为数据捕获的结果。在捕获结果中，还包含更改相关的一些信息（例如更改的操作类型、更新操作影响的列等）。
　　应用程序可以从捕获结果中获取DML更改的全部数据，而无需查询数据变更的原始表。
Step1：创建测试数据库

-- ====================================================

-- 测试的数据库

MiniMSDN.com为您提供的代码


1 USE master;
2
3 GO
4
5 CREATE DATABASE DB_test;
6
7 GO
　　

-- 启用变更数据捕获

MiniMSDN.com为您提供的代码


1 USE DB_test;
2
3 EXEC sys.sp_cdc_enable_db;
4
5 GO
　　
[备注说明]
　　sys.sp_cdc_enable_db:
--无法对系统数据库和分发数据库启用变更数据捕获。
　　--sys.sp_cdc_enable_db 将创建以全数据库为作用域的变更数据捕获对象，包括元数据表和 DDL 触发器。它还会创建 cdc 架构和 cdc 数据库用户，并将 sys.databases 目录视图中的数据库条目的 is_cdc_enabled 列设置为 1。

[附图]

Step2：检查SQL Server Agent 服务的状态，如果未启动，则启动它


MiniMSDN.com为您提供的代码


1 -- ====================================================
2
3 -- 检查SQL Server Agent 服务的状态，如果未启动，则启动它
4
5 DECLARE
6     @agnt_service sysname;
7 SET @agnt_service = N'SQLServerAgent';
8
9  
10 DECLARE @tb_agent_status TABLE(
11     state varchar(50)
12 );
13
14 INSERT @tb_agent_status
15
16 EXEC master.sys.xp_servicecontrol
17     N'QUERYSTATE',
18     @agnt_service;
19  
20 IF NOT EXISTS(
21         SELECT * FROM @tb_agent_status
22         WHERE state = N'Running.')
23     EXEC master.sys.xp_servicecontrol
24         N'START',
25         @agnt_service;
26 GO
　　

Step3：创建测试表

Minimsdn.com为您提供的代码：


1 -- ====================================================
2
3 -- 测试的表
4
5 USE DB_test;
6
7 GO
8
9 CREATE TABLE dbo.tb(
10
11     id int
12
13         CONSTRAINT PK_tb_id PRIMARY KEY,
14
15     col1 int,
16
17     col2 varchar(10),
18
19     col3 nvarchar(max),
20
21     col4 varbinary(max),
22
23     col5 xml
24
25 );
26
27 GO
　　

-- 创建一个变更数据捕获实例- 所有列

-- 创建数据库中的第一个变更数据捕获实例的时候，数据捕获和清理的JOB 会自动创建

-- 可以通过sys.sp_cdc_change_job 这个存储过程去调整捕获和清理的相关设置

-- 也可以在创建第一个变更数据捕获实例前，使用sys.sp_cdc_add_job去创建数据捕获和清理Job，在创建时做好相关的设置

MINIMsdn.com为您提供的代码


EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'tb',
@capture_instance = N'dbo_tb',
@role_name = NULL;
　　

执行结果[附图]：

-- 创建一个变更数据捕获实例- 特定列

Minimsdn.com为您提供的代码


EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'tb',
@capture_instance = N'dbo_tb_col',
@role_name = NULL,
@captured_column_list = N'id,col1,col2';
GO
　　

执行结果[附图]：

Step４：数据测试

-- ====================================================

Step４.1：数据测试 　- 　插入
Step４.1.1：数据测试 　- 　第一次插入３行数据

-- a. 插入初始数据

MiniMsdn.com为您提供的代码


1 INSERT dbo.tb(
2
3     id,
4
5     col1, col2, col3, col4, col5)
6
7 VALUES(
8
9     1,
10
11     1, 'AA', 'AAA', 0x1, 'aa'),
12
13 (
14
15     2,
16
17     2, 'BB', 'BBB', 0x2, ''),
18
19 (
20
21     3,
22
23     3, 'CC', 'CCC', 0x2, '');
　　
查询并[附图]


MiniMSDN.com为您提供的代码：


1 WITH
2
3 LSN AS(
4
5     SELECT
6
7         from_lsn = sys.fn_cdc_get_min_lsn(N'dbo_tb'),
8
9         to_lsn = sys.fn_cdc_get_max_lsn()
10
11 ),
12
13 CHG_ALL AS(
14
15     SELECT
16
17         CHG.*
18
19     FROM LSN
20
21         CROSS APPLY cdc.fn_cdc_get_all_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL UPDATE OLD') CHG
22
23 ),
24
25 CHG_NET AS(
26
27     SELECT
28
29         CHG.*
30
31     FROM LSN
32
33         CROSS APPLY cdc.fn_cdc_get_net_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL') CHG
34
35 )
36
37 SELECT * FROM CHG_ALL;
　　

Step４.1.２：数据测试 　- 　第二次插入３行数据

Minimsdn.com为您提供的代码：


1 INSERT dbo.tb(
2     id, col1, col2, col3, col4, col5)
3
4 VALUES
5
6     (4,4, 'DD', 'DDD', 0x4, 'dd'),
7     (5,5, 'EE', 'EEE', 0x5, ''),
8     (6,6, 'FF', 'FFF', 0x6, '');

查询并[附图]

Minimsdn.com为您提供的代码


1 WITH
2
3 LSN AS(
4
5     SELECT
6
7         from_lsn = sys.fn_cdc_get_min_lsn(N'dbo_tb'),
8
9         to_lsn = sys.fn_cdc_get_max_lsn()
10
11 ),
12
13 CHG_ALL AS(
14
15     SELECT
16
17         CHG.*
18
19     FROM LSN
20
21         CROSS APPLY cdc.fn_cdc_get_all_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL UPDATE OLD') CHG
22
23 ),
24
25 CHG_NET AS(
26
27     SELECT
28
29         CHG.*
30
31     FROM LSN
32
33         CROSS APPLY cdc.fn_cdc_get_net_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL') CHG
34
35 )
36
37 SELECT * FROM CHG_ALL;
　　

Step　4.2：数据测试 　- 　　更新

Step　4.2.1：数据测试 　- 　　更新整数型列值

Minimsdn.com为您提供的代码：


1     UPDATE dbo.tb SET
2
3         col1 = 11
4
5     WHERE id = 1;
　　

查询并[附图]

Minimsdn.com为您提供的代码


1 WITH
2
3 LSN AS(
4
5     SELECT
6
7         from_lsn = sys.fn_cdc_get_min_lsn(N'dbo_tb'),
8
9         to_lsn = sys.fn_cdc_get_max_lsn()
10
11 ),
12
13  
14
15 CHG_ALL AS(
16
17     SELECT
18
19         CHG.*
20
21     FROM LSN
22
23         CROSS APPLY cdc.fn_cdc_get_all_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL UPDATE OLD') CHG
24
25 ),
26
27  
28
29 CHG_NET AS(
30
31     SELECT
32
33         CHG.*
34
35     FROM LSN
36
37         CROSS APPLY cdc.fn_cdc_get_net_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL') CHG
38
39 )
40
41 SELECT * FROM CHG_ALL;
　　

Step　4.2.1：数据测试 　- 　　通过事务更新整数型列两次

Minimsdn.com为您提供的代码


1 BEGIN TRAN;
2
3     UPDATE dbo.tb SET
4
5         col1 = 44
6
7     WHERE id = 4;
8
9  
10
11     UPDATE dbo.tb SET
12
13         col1 = 444
14
15     WHERE id = 4;
16
17 COMMIT TRAN;
　　

查询并[附图]

Minimsdn.com为您提供的代码：


1 WITH
2
3 LSN AS(
4
5     SELECT
6
7         from_lsn = sys.fn_cdc_get_min_lsn(N'dbo_tb'),
8
9         to_lsn = sys.fn_cdc_get_max_lsn()
10
11 ),
12
13 CHG_ALL AS(
14
15     SELECT
16
17         CHG.*
18
19     FROM LSN
20
21         CROSS APPLY cdc.fn_cdc_get_all_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL UPDATE OLD') CHG
22
23 ),
24
25 CHG_NET AS(
26
27     SELECT
28
29         CHG.*
30
31     FROM LSN
32
33         CROSS APPLY cdc.fn_cdc_get_net_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL') CHG
34
35 )
36
37 SELECT * FROM CHG_ALL;
　　

Step　4.2.3：数据测试 　- 　　更新XML数据列

Minimsdn.com为您提供的代码：


1 UPDATE dbo.tb SET
2
3     col5.modify('replace value of /a[1]/text()[1] with "replace"')
4
5 WHERE id = 1;
6
7  
8
9 UPDATE dbo.tb SET
10
11     col5.modify('insert 1 as last into /')
12
13 WHERE id = 2;
　　

查询并[附图]

Minimsdn.com为您提供的代码：


1 WITH
2
3 LSN AS(
4
5     SELECT
6
7         from_lsn = sys.fn_cdc_get_min_lsn(N'dbo_tb'),
8
9         to_lsn = sys.fn_cdc_get_max_lsn()
10
11 ),
12
13 CHG_ALL AS(
14
15     SELECT
16
17         CHG.*
18
19     FROM LSN
20
21         CROSS APPLY cdc.fn_cdc_get_all_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL UPDATE OLD') CHG
22
23 ),
24
25 CHG_NET AS(
26
27     SELECT
28
29         CHG.*
30
31     FROM LSN
32
33         CROSS APPLY cdc.fn_cdc_get_net_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL') CHG
34
35 )
36
37 SELECT * FROM CHG_ALL;
　　

Step　4.2.3：数据测试 　- 　　更新binary(max) 数据列

Minimsdn.com为您提供的代码：


1 UPDATE dbo.tb SET
2
3     col4 = col4 + 0x12345
4
5 WHERE id = 3;
　　

查询并[附图]

Minimsdn.com为您提供的代码：


1 WITH
2
3 LSN AS(
4
5     SELECT
6
7         from_lsn = sys.fn_cdc_get_min_lsn(N'dbo_tb'),
8
9         to_lsn = sys.fn_cdc_get_max_lsn()
10
11 ),
12
13 CHG_ALL AS(
14
15     SELECT
16
17         CHG.*
18
19     FROM LSN
20
21         CROSS APPLY cdc.fn_cdc_get_all_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL UPDATE OLD') CHG
22
23 ),
24
25 CHG_NET AS(
26
27     SELECT
28
29         CHG.*
30
31     FROM LSN
32
33         CROSS APPLY cdc.fn_cdc_get_net_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL') CHG
34
35 )
36
37 SELECT * FROM CHG_ALL;
　　

Step　4.2.4：数据测试 　- 　　更新主键

Minimsdn.com为您提供的代码：


1 UPDATE dbo.tb SET
2
3     id = 11
4
5 WHERE id = 1;
6
7 INSERT dbo.tb(
8
9     id,
10
11     col1, col2, col3, col4, col5)
12
13 VALUES(
14
15     1,
16
17     1, 'AA', 'AAA', 0x1, 'aa');
　　

查询并[附图]

Minimsdn.com为您提供的代码：


1 WITH
2
3 LSN AS(
4
5     SELECT
6
7         from_lsn = sys.fn_cdc_get_min_lsn(N'dbo_tb'),
8
9         to_lsn = sys.fn_cdc_get_max_lsn()
10
11 ),
12
13 CHG_ALL AS(
14
15     SELECT
16
17         CHG.*
18
19     FROM LSN
20
21         CROSS APPLY cdc.fn_cdc_get_all_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL UPDATE OLD') CHG
22
23 ),
24
25 CHG_NET AS(
26
27     SELECT
28
29         CHG.*
30
31     FROM LSN
32
33         CROSS APPLY cdc.fn_cdc_get_net_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL') CHG
34
35 )
36
37 SELECT * FROM CHG_ALL;
　　

　　[最新表结果]

-- ====================================================

-- 删除测试

Minimsdn.com为您提供的代码：


1 /*--
2
3 USE master;
4
5 GO
6
7 ALTER DATABASE DB_test SET
8
9     SINGLE_USER
10
11     WITH
12
13         ROLLBACK AFTER 0;
14
15 GO
16
17 DROP DATABASE DB_test;
18
19 --*/