sql server教程:SQL的变更数据捕获

介绍:SQL Server 2008变更数据捕获

SQL Server 2008的CDC函数读取激活了CDC的每个表所关联的事务日志来记录系统表中的变更。它将这些文件写到同一个数据库的系统表中，这些系统表是可以通过直接查询或系统函数来访问的。

让我们看些示例代码并看看每一步有什么改变。

使用示例代码来跟踪可变部分

首先，我们从一个全新的数据库开始。要跟踪这些变更，打开对象浏览器并展开这些文件夹：

· Databases

· SQLServer Agent | Jobs

· (数据库创建之后)Security | Roles | Database Roles

· (数据库创建之后)Programmability | Stored Procedures | System Stored Procedures

· (数据库创建之后)Programmability | Functions | Table-valued Functions

创建数据库

CREATE DATABASE TestCdc

这为我们创建了新数据库，但是没有新作业。展开Tables文件夹和System Tables文件夹。现在是空的。

在数据库TestCdc上激活变更数据捕获

USE TestCdc

-- Turn on Change Data Capture at database level

EXEC sys.sp_cdc_enable_db

这个步骤需要一些时间。现在刷新System Tables文件夹。现在它下面有6个系统表：

· cdc.captured_columns

· cdc.change_tables

· cdc.ddl_history

· cdc.index_columns

· cdc.lsn_time_mapping

· dbo.systranschemas

注意，其中的5个是“cdc” schema 部分的。

在sys.all_objects表中还有四个新的看起来很奇怪的“SQL inline table-valued function”登录：

· fn_cdc_get_all_changes_◦...◦

· fn_cdc_get_all_changes_...

· fn_cdc_get_net_changes_◦...◦

· fn_cdc_get_net_changes_...

(我使用了非比例字体，用“◦”来标记空格，使得很清楚地看到在这些名称中有嵌入的空格。)

下面是会返回这些函数的查询：

SELECT [name] FROM sys.all_objects WHERE [name] LIKE '%...%'

还有更多的新存储过程

· sp_batchinsert_lsn_time_mapping

· sp_ins_dummy_lsn_time_mapping

· sp_ins_instance_enabling_lsn_time_mapping

· sp_ins_lsn_time_mapping

现在还没有新的作业或角色。

创建新的Schema和用户表

CREATE SCHEMA MySchema

CREATE TABLE MySchema.Person (

PersonID int IDENTITY PRIMARY KEY,

FirstName varchar(32) NOT NULL,

LastName varchar(32) NOT NULL,

UpdateCt int NOT NULL DEFAULT 0

)

上面的代码创建了一个新的用户表，但是这个表还没有激活CDC。

这个步骤不影响我们所跟踪的函数/存储过程/角色列表。

为新的用户表激活CDC

EXEC sys.sp_cdc_enable_table

@source_schema = N'MySchema',

@source_name = N'Person',

@role_name = N'ChangeDataAccessRole',

@supports_net_changes = 1

这会为新的用户表激活CDC。刷新上面所列的文件夹。我们现在有了

· 一个新的系统表(cdc.MySchema_Person_CT)

· 两个新的作业(cdc.TestCdc_capture和cdc.TestCdc_cleanup)

· 一个新的数据库角色(ChangeDataAccessRole)

· 两个新的表值型函数

o fn_cdc_get_all_changes_MySchema_Person

o fn_cdc_get_net_changes_MySchema_Person

· 三个新的存储过程：

o sp_batchinsert_389576426

o sp_insdel_389576426

o sp_upd_389576426

你可以获得这些存储过程(sp_helptext 'cdc.sp_batchinsert_389576426' 【你的数字可能不一样】)的文本。很明显这个存储过程被生成了，而且是专用于这个用户表的。

如果你为第二个表激活了CDC，那么你将获得另一组的sp_batchinsert_n、sp_insdel_n和sp_upd_n存储过程。

系统表cdc.MySchema_Person_CT

这个表具有以下字段：

· __$start_lsn

· __$end_lsn

· __$seqval

· __$operation

· __$update_mask

· PersonID

· FirstName

· LastName

· UpdateCt

作业cdc.TestCdc_capture

当你研究这个捕捉作业时，你会发现它是很有趣的。从sysjobs，我们可以看出这个作业是属于sa的，是在“REPL-LogReader”分类中，并有一个“CDC Log Scan Job”的描述。

从sysjobsteps，我们看到它有两个步骤：

读过这些细节之后，接下来有两个步骤，但是我们将保留第二个，直到我们插入了一些数据之后再执行

sp_helptext N'sys.sp_MScdc_capture_job'

SELECT * FROM sys.dm_cdc_log_scan_sessions

存储过程Psys.sp_MScdc_capture_job和sys.sp_cdc_scan

sys.sp_MScdc_capture_job是开始整个CDC过程的存储过程。它首先进行一些安全检查，然后调用sys.sp_cdc_scan，这将进行实质的工作。sys.sp_cdc_scan执行变更数据捕获日志扫描操作。

默认情况下，sys.sp_cdc_scan在每个循环间进行五秒钟的延迟。在每个常规的循环中，它调用sp_replcmds。(这是个复制存储过程。使用默认(1)调用这个存储过程会返回等待分配的下一个事务。在这个存储过程中，默认@maxtrans是500。)

最后，它从sys.dm_cdc_log_scan_sessions读取来准备调用sp_sqlagent_log_jobhistory，从而记录作业历史信息。

数据库角色ChangeDataAccessRole

如果你打开新的ChangeDataAccessRole角色的属性窗口，那么你会看到这个角色属于“cdc”，并且具有对上面所列的两个表值型函数的SELECT权限，除此之外没有其它权限。

总结

研究这些细节使我们了解了Server 2008的CDC是怎样执行的。