justforu.team

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::
[一步一步] SQL Server 2008变更数据捕获

变更数据捕获

变更数据捕获使用异步进程读取事务日志，获取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, '<a>aa</a>'),
12 
13 (
14 
15     2,
16 
17     2, 'BB', 'BBB', 0x2, '<b/>'),
18 
19 (
20 
21     3,
22 
23     3, 'CC', 'CCC', 0x2, '<c/>');


查询并[附图]

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, '<d>dd</d>'),
7     (5,5, 'EE', 'EEE', 0x5, '<e/>'),
8     (6,6, 'FF', 'FFF', 0x6, '<f/>');


查询并[附图]

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 <a>1</a> 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, '<a>aa</a>');

 

查询并[附图]

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 --*/

 

posted on 2012-05-16 18:24  justforu.team  阅读(1528)  评论(0)  编辑  收藏  举报
MiniMSDN , MVC3个人站点，分享代码的地方， 欢迎您的光临