首页 > 数据库技术 > Microsoft SQL Server > SQL Server 2008变更数据捕获
2014
08-11

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,我们看到它有两个步骤:

从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是怎样执行的。


出自IT专家网

最后编辑:
作者:sleepyan
这个作者貌似有点懒,什么都没有留下。
捐 赠如果您觉得这篇文章有用处,请支持作者!鼓励作者写出更好更多的文章!