- 浏览: 2166969 次
文章分类
最新评论
使用SQLServer 2008的CDC功能实现数据变更捕获
最近由于工作需要,研究了一下2008 CDC功能,觉得还不错,下面整理了一下研究过程,虽然比较粗略,但是基本上能用了,如果有补充请大家回复,我视情况修改原帖,谢谢。
背景:
在SQLServer2008之前,对数据变更的捕获通常使用触发器、时间戳等低效高成本的功能来实现,所以很多系统都没有做数据变更或者仅仅对核心表做监控。
自从SQLServer2008引入了数据变更捕获功能(后面简称为CDC)后,解决了很多烦恼问题,如客户2个月前更改了一个数据,但是由于服务器空间不足,经常只能保留一个月的备份。此时客户为了逃避因为更改数据带来的较大负面影响,往往会把责任推给软件开发商或者运维商,如果此时有变更捕获的话,就有证据了,当然分析日志也能实现,但是比较痛苦。适用环境:
仅在SQLServer2008(含)以后的企业版、开发版和评估版中可用。
详解:
CDC功能主要捕获SQLServer指定表的增删改操作,由于任何操作都会写日志(哪怕truncate),所以CDC的捕获来源于日志文件。日志文件会把更改应用到数据文件中,同时也会标记符合要求的数据标记为需要添加跟踪的项。然后通过一些配套函数,最后写入到数据仓库中。大概流程:
步骤:本文中以:AdventureWorks为例
第一步、对目标库显式启用CDC:
在当前库使用sys.sp_cdc_enable_db。返回0(成功)或1(失败)。注意,无法对系统数据库和分发数据库启用该功能。且执行者需要用sysadmin角色权限。
该存储过程的作用域是整个目标库。包含元数据、DDL触发器、cdc架构和cdc用户。
使用以下代码启用:
USE AdventureWorks GO EXECUTE sys.sp_cdc_enable_db; GO |
在一开始直接执行时,出现了报错信息:
消息22830,级别16,状态1,过程sp_cdc_enable_db_internal,第193 行 无法更新元数据来指示已对数据库AdventureWorks 启用了变更数据捕获。执行命令'SetCDCTracked(Value = 1)' 时失败。返回的错误为15517: '无法作为数据库主体执行,因为主体"dbo" 不存在、无法模拟这种类型的主体,或您没有所需的权限。'。请使用此操作和错误来确定失败的原因并重新提交请求。 |
这里引出了另外一个知识点:错误号 15517 的错误
这种错误会在很多地方出现,如还原数据库的时候也会有可能出现。共同点是:某个/些存储过程使用了具有WITHEXECUTE AS 的选项。使其在当前库具有了某个架构,但是当在别的地方执行时,由于没有这个架构,所以就报错,解决方法:
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa] |
经过检查,uspUpdateEmployeeHireInfo这个存储过程的确有:WITH EXECUTE AS CALLER |
使用sa的原因是即使sa被禁用,sa还是存在的。所以不会报错。
现在重新执行:
USE AdventureWorks GO EXECUTE sys.sp_cdc_enable_db; GO |
启用成功,然后通过以下语句检查是否成功:
SELECT is_cdc_enabled,CASEWHEN is_cdc_enabled=0THEN 'CDC功能禁用'ELSE 'CDC功能启用'END 描述 FROM sys.databases WHERE NAME = 'AdventureWorks' |
创建成功后,将自动添加CDC用户和CDC架构。
创建这两个用户、架构的原因是因为CDC要求独占方式使用这两个架构,所以要单独创建。如果存在了非CDC功能创建的CDC用户、架构的话,则需要先删除该cdc命名的架构,才能开启。
第二步、对目标表启用CDC:
使用db_owner角色的成员执行sys.sp_cdc_enable_table为每个需要跟踪的表创建捕获实例。然后通过sys.tables目录视图中的is_tracked_by_cdc列来判断是否创建成功。
默认情况下会对表的全部列做捕获。如果只需要对某些列做捕获,可以使用@captured_column_list参数指定这些列。
如果要把更改表放到文件组里的话,最好创建单独的文件组(最起码与源表独立)。
如果不想控制访问角色,则@role_name必须显式设置为null。
sys.sp_cdc_enable_table [ @source_schema = ] 'source_schema', [ @source_name = ] 'source_name' , [ @role_name = ] 'role_name' [,[ @capture_instance = ] 'capture_instance' ] [,[ @supports_net_changes = ] supports_net_changes ] [,[ @index_name = ] 'index_name' ] [,[ @captured_column_list = ] 'captured_column_list' ] [,[ @filegroup_name = ] 'filegroup_name' ] [,[ @partition_switch = ] 'partition_switch' ] |
例子:
把HumanResources.Department 这个表开启变更捕获。
USE AdventureWorks GO EXEC sys.sp_cdc_enable_table@source_schema= 'HumanResources', @source_name = 'Department',@role_name = NULL |
然后查询是否成功:
SELECT name , is_tracked_by_cdc , CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述 FROM sys.tables WHERE OBJECT_ID= OBJECT_ID('HumanResources.Department') |
对表开启以后,可以在下图中看到多了很多cdc架构开头的表:
下面列出相关的存储过程:
Sys.sp_cdc_add_job |
|
Sys.sp_cdc_generate_wrapper_function |
|
Sys.sp_cdc_change_job |
|
Sys.sp_cdc_get_captured_columns |
|
Sys.sp_cdc_cleanup_change_table |
|
Sys.sp_cdc_get_ddl_history |
|
Sys.sp_cdc_disable_db |
说明及例子 建议先禁用表,再禁用库 |
Sys.sp_cdc_help_change_data_capture |
|
Sys.sp_cdc_disable_table |
|
Sys.sp_cdc_help_jobs |
|
Sys.sp_cdc_drop_job |
|
Sys.sp_cdc_scan |
|
Sys.sp_cdc_enable_db |
|
Sys.sp_cdc_start_job |
|
Sys.sp_cdc_enable_table |
|
Sys.sp_cdc_stop_job |
函数:
Cdc.fn_cdc_get_all_changes_<capture_instance> |
|
Sys.fn_cdc_has_column_changed |
|
Cdc.fn_cdc_get_net_changes_<capture_instance> |
|
Sys.fn_cdc_increment_lsn |
|
Sys.fn_cdc_decrement_lsn |
|
Sys.fn_cdc_is_bit_set |
|
Sys.fn_cdc_get_column_ordinal |
|
Sys.fn_cdc_map_lsn_to_time |
|
Sys.fn_cdc_get_max_lsn |
|
Sys.fn_cdc_map_time_to_lsn |
|
Sys.fn_cdc_get_min_lsn |
案例:
下面开始从头到尾做一个实际案例:
先检查原库的内容:可以看到系统表里面只有一个dbo.sysdiagrams表
然后看看SQLServer代理:可以看到也没有相关的作业
步骤二:对数据库启用CDC数据库级别功能。必须显式启用:
Step1、执行以下语句:
USE AdventureWorks GO EXEC sys.sp_cdc_enable_db GO |
某些数据库可能存在一些存储过程包含有:execute as 等语句,此时会报错:
文字描述:
消息22830,级别16,状态1,过程sp_cdc_enable_db_internal,第186 行 无法更新元数据来指示已对数据库AdventureWorks 启用了变更数据捕获。执行命令'SetCDCTracked(Value = 1)' 时失败。返回的错误为15517: '无法作为数据库主体执行,因为主体"dbo" 不存在、无法模拟这种类型的主体,或您没有所需的权限。'。请使用此操作和错误来确定失败的原因并重新提交请求。 消息266,级别16,状态2,过程sp_cdc_enable_db_internal,第0 行 EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数= 0,当前计数= 1。 消息266,级别16,状态2,过程sp_cdc_enable_db,第0 行 EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数= 0,当前计数= 1。 消息3998,级别16,状态1,第1 行 在批处理结束时检测到不可提交的事务。该事务将回滚。 |
如果出现这个错误,目前的解决方法是执行下面语句,原因已在开头说明,对于没有使用EXECUTE AS的库,一般不会有这样的问题:
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa] |
然后再次执行,就成功开启了:
USE AdventureWorks GO EXEC sys.sp_cdc_enable_db GO |
现在检查是否成功:
SELECT is_cdc_enabled , CASE WHEN is_cdc_enabled = 0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述 FROM sys.databases WHERE NAME = 'AdventureWorks' |
现在检查表和作业:
作业没有改变
角色权限中多出了:步骤三:对某些表开启捕获:
这里选择HumanResources.Department、Person.ADDRESS、Person.Contact 开启:
先来看开启之前的样子:
然后对该表开启:
USE AdventureWorks; GO EXECUTE sys.sp_cdc_enable_table @source_schema = N'HumanResources' , @source_name = N'Department' , @role_name = N'cdc_Admin'--可以自动创建 , @capture_instance=DEFAULT GO |
结果如图:
也可以看到多了一个角色CDC_ADMIN,是在上面语句中动态创建的:
按照上面步骤把另外两个表也开启了:
USE AdventureWorks; GO EXECUTE sys.sp_cdc_enable_table @source_schema = N'Person' , @source_name = N'ADDRESS' , @role_name = N'cdc_Admin'--可以自动创建 , @capture_instance=DEFAULT GO EXECUTE sys.sp_cdc_enable_table @source_schema = N'Person' , @source_name = N'Contact' , @role_name = N'cdc_Admin'--可以自动创建 , @capture_instance=DEFAULT GO |
可以从系统表中看到:
了3个表,并且是刚才开启CDC功能的表。现在来检查是否开启成功:
SELECT name , is_tracked_by_cdc , CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述 FROM sys.tables WHERE OBJECT_ID IN( OBJECT_ID('HumanResources.Department'), OBJECT_ID('Person.ADDRESS'), OBJECT_ID('Person.Contact') ) |
结果如下:
步骤三:检验:
然后把表中的数据复制一份:
INSERT INTO HumanResources.Department ( Name , GroupName , ModifiedDate ) SELECT Name + '1' , GroupName + '1' , GETDATE() ModifiedDate FROM HumanResources.Department |
结果如下:
可以看到的确多了16条记录。这部分记录证明了,有16条数据从监控到目前为止做了改动,现在再来改动一下:
DELETE FROM HumanResources.Department WHERE DepartmentID>17 |
再查询cdc表,可以看到又多了16条记录:
现在来做下update的实验:
UPDATE HumanResources.Department SET ModifiedDate=GETDATE() |
再查看:
现在来分析一下这个表:
可以在联机丛书上查看:cdc.<capture_instance>_CT 可以看到,这样命名的表,是用于记录源表更改的表。对于insert/delete操作,会有对应的一行记录,而对于update,会有两行记录。
对于__$operation列:1 = 删除、2= 插入、3= 更新(旧值)、4= 更新(新值)
对于__$start_lsn列:由于更改是来源与数据库的事务日志,所以这里会保存其事务日志的开始序列号(LSN)
但是微软不检查直接查询这类表,建议使用cdc.fn_cdc_get_all_changes_<捕获实例> 和cdc.fn_cdc_get_net_changes_<capture_instance> 来查询
下文开始,来熟悉各种函数、存储过程的使用,并尝试一些不正常的操作。日常使用情景:
1、查询已经开启的捕获实例:
由于可能不记得或者不知道开启了什么表的捕获,所以可以使用以下语句来查找:
--返回所有表的变更捕获配置信息 EXECUTE sys.sp_cdc_help_change_data_capture; GO |
可以看到以下截图:
查看对某个实例(即表)的哪些列做了捕获监控:
EXEC sys.sp_cdc_get_captured_columns @capture_instance = 'HumanResources_Department' -- sysname |
得到下图:
也可以从下面中查找配置信息:
SELECT * FROM msdb.dbo.cdc_jobs |
如图:
2、查看当前配置使用sp_cdc_help_jobs:
从上文可以看到,启用cdc之后会自动创建了两个作业,可以先使用以下语句来查看:
sp_cdc_help_jobs |
对于一个大型的OLTP系统,由于数据更改会非常频繁,变更表中的数据会非常多,如果存放过久(最久可以存放100年),那对数据库空间是非常大的挑战。此时可以调整上图中cdc.AdventureWorks_cleanup 中retention(单位:分钟)。
3、修改配置:sp_cdc_change_job:
--显示原有配置: EXEC sp_cdc_help_jobs GO --更改数据保留时间为分钟 EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention=100 GO --停用作业 EXEC sys.sp_cdc_stop_jobN'cleanup' GO --启用作业 EXEC sys.sp_cdc_start_jobN'cleanup' GO --再次查看 EXEC sp_cdc_help_jobs GO |
得到以下结果:
4、停止/启用、删除/创建作业:
停止/开始作业,可以使用以下语句:
--停用作业 EXEC sys.sp_cdc_stop_jobN'cleanup' GO --启用作业 EXEC sys.sp_cdc_start_jobN'cleanup' GO |
删除作业:
EXEC sys.sp_cdc_drop_job@job_type = N'cleanup' -- nvarchar(20) GO --查看作业 EXEC sys.sp_cdc_help_jobs GO |
可以看到现在只剩下一个作业了:
创建作业:
EXEC sys.sp_cdc_add_job @job_type = N'cleanup', @start_job = 0, @retention = 5760 --查看作业 EXEC sys.sp_cdc_help_jobs GO |
下面看到已经创建成功:
5、DDL变更捕获:
CDC除了捕获数据变更之外,还能捕获DDL操作的变化。前提是先要确保SQLServer 代理的启用,其实CDC功能都需要确保sql 代理正常运行,因为所有操作都通过代理中的两个作业来实现的。
现在先来对HumanResources.Department 表修改一下,把name的长度加长:
ALTER TABLE HumanResources.Department ALTER COLUMN Name NVARCHAR(120) ; GO |
然后查询ddl记录表:
SELECT * FROM cdc.ddl_history |
可以看到:
6、使用CDC的函数来获取更改:
A、使用cdc.fn_cdc_get_all_changes_HumanResources_Department 函数报告捕获实例HumanResources_Department 的当前所有可用更改:
DECLARE @from_lsn binary(10), @to_lsn binary(10) SET @from_lsn = sys.fn_cdc_get_min_lsn('HumanResources_Department') SET @to_lsn = sys.fn_cdc_get_max_lsn() SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Department (@from_lsn, @to_lsn, N'all update old'); GO |
B、获取某个时间段的更改信息:
先根据日志序列号(logsequence number ,LSN)来获取跟踪变更数据:
Sys.fn_cdc_map_time_to_lsn获取变更范围内的最大、最小LSN值。可以使用:
Smallest greater than;smallest greater than orequal;largest less than;largest less than or equal.
如查询某个时间段插入的数据:
--插入数据 INSERT INTO HumanResources.Department(name,GroupName,ModifiedDate) VALUES('test','abc',GETDATE()) INSERT INTO HumanResources.Department(name,GroupName,ModifiedDate) VALUES('test1','abc1',GETDATE()) go --检查数据 DECLARE @bglsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal','2012-10-12 12:00:00.997') DECLARE @edlsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('largest less than or equal',GETDATE()) SELECT DepartmentID,GroupName,Name FROM cdc.HumanResources_Department_CT WHERE [__$operation]=2 AND [__$start_lsn] BETWEEN @bglsn AND @edlsn |
得到以下结果:
C、sys.fn_cdc_map_lsn_to_time 查询变更时间:
SELECT [__$operation] , CASE [__$operation] WHEN 1 THEN '删除' WHEN 2 THEN '插入' WHEN 3 THEN '更新(捕获的列值是执行更新操作前的值)' WHEN 4 THEN '更新(捕获的列值是执行更新操作后的值)' END [类型], sys.fn_cdc_map_lsn_to_time([__$start_lsn]) [更改时间] , name , DepartmentID , GroupName , ModifiedDate FROM cdc.HumanResources_Department_CT |
结果:
注意,由于该表刚好有一个modfieddate字段,所以和更改时间相同.
D、获取LSN边界:
SELECT sys.fn_cdc_get_max_lsn()[数据库级别的最大LSN], sys.fn_cdc_get_min_lsn('cdc.HumanResources_Department_CT')[捕获实例的lsn] |
相关推荐
在SQLServer2008之前,对数据变更的捕获通常使用触发器、时间戳等低效高成本的功能来实现,所以很多系统都没有做数据变更或者仅仅对核心表做监控。自从SQLServer2008引入了数据变更捕获功能(后面简称为CDC)后,...
主要介绍了使用SQLServer 2008的CDC功能实现数据变更捕获的脚本,大家参考使用
使用flink-connector-sqlserver-cdc 2.3.0把数据从SQL Server实时同步到MySQL中。
关于sql server 2012 CDC 使用数据库增量数据的提取
CDC操作Sqlserver2008
cdc技术分析文章,基于flink-cdc做监控数据源的实时变更数据捕获。该文从基础定义、使用场景、分析、实际使用流程、源码分析、常见问题及解决方式等几个方向讲解了这个技术栈。如果公司有要使用搜索方案,涉及到业务...
《SQL Server 2008数据库设计与实现》深入浅出地介绍了目前世界上最受欢迎的数据库管理系统之一——SQL Server。全书共分三个部分:第一部分阐释了数据库的基本概念,讲解了数据库建模语言;第二部分展示了从概念...
SQL Server 2008的更改数据捕获:崭新的异步CDC解决方案能够提高性能.pdf
适用 SQL Server Native Client 的这个可转散发安装程序会安装运行时间期间需要的客户端组件,以利用 SQL Server 2008 新功能,并选择性地安装开发使用 SQL Server Native Client API 之应用程序时所需要的头文件。
Microsoft SQL Server 2008 R2 功能包是是为 SQL Server 2008 R2 SP3 附加值的独立包的x64+x86集合,简体中文版本。 Microsoft® SQL Server® 2008 R2 SP3 功能包是为 Microsoft® SQL Server® 2008 R2 提供附加值...
第2部分为SQLServer 2008的准备篇,介绍了SQL Server 2008的功能、特性、各版本的比较、安装方法、SQL Server 2008的服务、客户端的工具等;第3部分为SQL Server 2008的基本操作篇,介绍如何管理与配置SQL Server ...
sqlserver 2008 数据操作 详解sqlserver 2008 数据操作 详解sqlserver 2008 数据操作 详解
第2章 使用SQL Server 2008管理工具 SQL 2008完全学习手册 视频教程 第3章 管理数据库 SQL 2008完全学习手册 视频教程 第4章 SQL Server 2008数据表管理 SQL 2008完全学习手册 视频教程 第5章 操作架构、索引和视图 ...
SQL Server Native Client 的这个可再分发安装程序用于安装运行时所需的客户端组件,以利用 SQL Server 2008 的新增功能,它也可以安装开发使用 SQL Server Native Client API 的应用程序所需的头文件。
实习0将基础部分已经对这些数据的准备工作以命令方式综合在一起,以便于查看SQL Server 2008图书管理数据库操作的主要功能,方便实习。实习1和实习2分别采用ASP.NET3.5(C#)/SQL Server 2008图书管理系统和VB 6.0/...
SQL Server 2008 微软 数据 平台 升级 方案
数据查询和管理,SQL Server 2008数据库的创建、配置和管理,数据类型、列属性、表数据完整性,Transact-SQL语言基础、常用函数和SQL Server 2008中Transact-SQL语言增强功能等,使用XML查询技术,存储过程与触发器...
1.使用Sql Server Management Studio 2008 连接数据库。 2.选中要导出数据的数据库节点,点鼠标右键,在菜单中选择“任务”->“生成脚本”,如图: 3。在弹出的界面中,点2次“下一步”进入如图界面中,把“编写...