您当前的位置: 首页 » MySQL/MSSQL编程 » SQL 2008的变更数据捕获——跟踪可变部分
SQL 2008的变更数据捕获——跟踪可变部分
论坛链接
介绍: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 部分的。
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 部分的。
#2楼发布者：superadmin时间：2009-03-21 10:59:05
在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)
· 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)
#3楼发布者：superadmin时间：2009-03-21 10:59:37
· 两个新的表值型函数
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是怎样执行的。
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是怎样执行的。