[一步一步] 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, '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/text() 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 --*/
页:
[1]