SQLServer 2008 CDC功能实现数据变更捕获脚本_MsSql

CDC:Change Data Capture

复制代码 代码如下:

--步骤：本文中以GPOSDB为例

--第一步、对目标库显式启用CDC：
--在当前库使用sys.sp_cdc_enable_db。返回0（成功）或1（失败）。
--注意，无法对系统数据库和分发数据库启用该功能。且执行者需要用sysadmin角色权限。
--该存储过程的作用域是整个目标库。包含元数据、DDL触发器、cdc架构和cdc用户。
--使用以下代码启用：
USE GPOSDB  --要启用CDC的数据库
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::[GPOSDB] TO [sa]

--经过检查，uspUpdateEmployeeHireInfo这个存储过程的确有：WITH EXECUTE AS CALLER
--使用sa的原因是即使sa被禁用，sa还是存在的。所以不会报错。
--现在重新执行：
USE GPOSDB
GO
EXECUTE 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 = 'GPOSDB'

--创建成功后，将自动添加CDC用户和CDC架构。
--在用户和架构下面可以看到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' ]

--例子：
--把SYSTEMPARA 这个表开启变更捕获。
USE GPOSDB
GO
EXEC sys.sp_cdc_enable_table @source_schema = 'DBO',
    @source_name = 'SYSTEMPARA',@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('dbo.systempara')
--对表开启以后，可以在下图中看到多了很多cdc架构开头的表：
--刷新一下GPOSDB数据库，在系统表下面可以看到多了下面几张表
[cdc].[DBO_SYSTEMPARA_CT]
[cdc].[change_tables]
[cdc].[captured_columns]
[cdc].[ddl_history]
[cdc].[index_columns]
[cdc].[lsn_time_mapping]
[dbo].[systranschemas]
[dbo].[dtproperties]

--启动之后，可以看到SQLServer代理里面的作业，也出现了这两个作业：
[cdc.GPOSDB_capture]
[cdc.GPOSDB_cleanup]

--在可编程性-》函数-》表值函数里，也多了两个函数
[cdc].[fn_cdc_get_all_changes_DBO_SYSTEMPARA]
[cdc].[fn_cdc_get_net_changes_DBO_SYSTEMPARA]

--下面列出相关的存储过程：
--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
--说明及例子

--------------------下面开始从头到尾做一个实际案例-------------------------
--下面开始从头到尾做一个实际案例

--步骤一:对目标库显式启用CDC
USE GPOSDB  --要启用CDC的数据库
GO
EXECUTE 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::[GPOSDB] TO [sa]

--现在重新执行：
USE GPOSDB
GO
EXECUTE 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 = 'GPOSDB'

--步骤二：对表启用CDC
USE GPOSDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = 'DBO',
@source_name = 'SYSTEMPARA',
@role_name = NULL,
@capture_instance=DEFAULT
GO

--然后查询是否成功：
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('dbo.systempara')

--可以看到GPOSDB数据库里的系统表里新增了[cdc].[DBO_SYSTEMPARA_CT]表

 

 

--步骤三：检验，下面来改动数据
--先查询一下DBO_SYSTEMPARA_CT表
SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]

--可以看到一条记录都没有，因为刚刚创建，并没有对原表systempara做任何增删改操作

--向[SystemPara]表插入一条记录
INSERT INTO [dbo].[SystemPara]
        ( [ParaValue] ,
          [Name] ,
          [Description]
        )
VALUES  ( '中国' , -- ParaValue - varchar(50)
          '中国' , -- Name - varchar(50)
          '中国'  -- Description - varchar(50)
        )

--查询一下DBO_SYSTEMPARA_CT表，可以看到多了一条记录
SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]

--更新[SystemPara]表的一条记录
UPDATE [dbo].[SystemPara] SET [ParaValue]='德国' WHERE [Description]='中国'

--查询一下DBO_SYSTEMPARA_CT表，可以看到多了两条记录
SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]

--删除[SystemPara]表的一条记录
DELETE FROM  [dbo].[SystemPara]  WHERE [Description]='中国'

--查询一下DBO_SYSTEMPARA_CT表，可以看到多了一条记录
SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]

--现在来分析一下DBO_SYSTEMPARA_CT表
--可以在联机丛书上查看：
--cdc.<capture_instance>_CT 
--可以看到，这样命名的表，是用于记录源表做过更改操作的表。
--对于insert/delete操作，会有对应的一行记录，而对于update，会有两行记录。
--对于__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）
--update语句的__$operation列的值是3和4，所以一条update语句对应两条记录

--对于__$start_lsn列：由于更改是来源与数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）
--但是微软不检查直接查询这类表，建议使用
--cdc.fn_cdc_get_all_changes_<捕获实例>
--cdc.fn_cdc_get_net_changes_<capture_instance>
-- 来查询

----------------------------------------------------------

--下文开始，来熟悉各种函数、存储过程的使用，并尝试一些不正常的操作。

 

--日常使用情景：
--1、查询已经开启的捕获实例：
--返回所有表的变更捕获配置信息
USE [GPOSDB]
GO
EXECUTE sys.sp_cdc_help_change_data_capture;
GO

 

--查看对某个实例(即表)的哪些列做了捕获监控
USE [GPOSDB]
GO
EXEC sys.sp_cdc_get_captured_columns
@capture_instance = 'systempara' -- 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
--更改数据保留时间为100分钟
EXECUTE sys.sp_cdc_change_job
    @job_type = N'cleanup',
    @retention=100
GO

--重启一下作业，以使设置生效
--停用作业
EXEC sys.sp_cdc_stop_job N'cleanup'
GO
--启用作业
EXEC sys.sp_cdc_start_job N'cleanup'
GO
--再次查看
EXEC sp_cdc_help_jobs
GO
--可以看到retention（单位：分钟）的值变为100了

--4、停止/启用、删除/创建作业

--停用作业
EXEC sys.sp_cdc_stop_job N'cleanup'
GO
--启用作业
EXEC sys.sp_cdc_start_job N'cleanup'
GO

--删除作业
EXEC sys.sp_cdc_drop_job @job_type = N'cleanup' -- nvarchar(20)
GO
--查看作业
EXEC sys.sp_cdc_help_jobs
GO
--可以看到现在只剩下一个作业了:cdc.GPOSDB_capture

--创建作业
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 代理正常运行
--因为所有操作都通过代理中的两个作业来实现的。
--现在先来对SYSTEMPARA 表修改一下，把PARAVALUE的长度加长
USE [GPOSDB]
GO
ALTER TABLE  [dbo].[SystemPara] ALTER COLUMN PARAVALUE VARCHAR(120) ;
GO

--然后查询ddl记录表
SELECT  * FROM    cdc.ddl_history

--6、使用CDC的函数来获取更改
--A、使用 [cdc].[fn_cdc_get_all_changes_DBO_SYSTEMPARA]
--函数报告捕获实例的当前所有可用更改
DECLARE @from_lsn BINARY(10) ,
    @to_lsn BINARY(10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('SYSTEMPARA')
SET @to_lsn = sys.fn_cdc_get_max_lsn()
SELECT  *
FROM    cdc.fn_cdc_get_all_changes_DBO_SYSTEMPARA(@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 [dbo].[SystemPara]
        ( [ParaValue] ,
          [Name] ,
          [Description]
        )
VALUES  ( '中国' , -- ParaValue - varchar(50)
          '中国' , -- Name - varchar(50)
          '中国'  -- Description - varchar(50)
        )

GO

--检查数据
--1删除
--2插入
--3、4更改
--曾经插入过的记录就算delete了也可以查询出来
DECLARE @bglsn VARBINARY(10)= sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',
                                                         '2013-10-21 12:00:00.997')
DECLARE @edlsn VARBINARY(10)= sys.fn_cdc_map_time_to_lsn('largest less than or equal',
                                                         GETDATE())
SELECT  *
FROM    [cdc].[DBO_SYSTEMPARA_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]) [更改时间] ,
        *
FROM    [cdc].[DBO_SYSTEMPARA_CT]

 

--D、获取LSN边界
SELECT  sys.fn_cdc_get_max_lsn() [数据库级别的最大LSN] ,
        sys.fn_cdc_get_min_lsn('cdc.DBO_SYSTEMPARA_CT') [捕获实例的lsn]

这两个值可以用于上面提到的函数里面用于筛选数据之用。

----------------------------------------------------------
--1. CDC的目的是什么？

--CDC就是极大地方便了我们获取某个表数据更新情况的一个机制。它通过一个独立的进程，
--异步读取日志文件，而不是触发器的方式工作。而且它的数据是会持久化保存到一个系统表的。
--2. CDC是不是SQL Server 2008特有的功能，对别的数据库或者早期版本是否起作用？

--CDC是SQL Server 2008特有的功能，而且是企业版特有的功能。开发版也有该功能，但仅用于测试场合。

--3. CDC读取日志，那么如果日志被截断了会怎么样？

--如果某部分日志，CDC的进程还没有读取，那么在截断日志时就会忽略这个部分,不能截断!!

--捕获进程是一个独立的，它随着代理服务启动而启动。两次扫描之间间隔5分钟。

--4. 系统表中的数据是否会永久存在？--不会，它会被保留3天。会有一个清理的作业，每天晚上2点进行扫描。
--最后，补充一点的是，CDC功能依赖Agent服务，因为它有两个操作都是通过作业来启动的。

时间： 2024-11-08 19:25:13

SQLServer 2008 CDC功能实现数据变更捕获脚本_MsSql的相关文章

SQLServer 2008 CDC功能实现数据变更捕获脚本

CDC:Change Data Capture复制代码 代码如下:--步骤:本文中以GPOSDB为例 --第一步.对目标库显式启用CDC:--在当前库使用sys.sp_cdc_enable_db.返回0(成功)或1(失败).--注意,无法对系统数据库和分发数据库启用该功能.且执行者需要用sysadmin角色权限.--该存储过程的作用域是整个目标库.包含元数据.DDL触发器.cdc架构和cdc用户.--使用以下代码启用:USE GPOSDB  --要启用CDC的数据库GOEXECUTE sys.s

使用SQLServer 2008的CDC功能实现数据变更捕获

原文:使用SQLServer 2008的CDC功能实现数据变更捕获 最近由于工作需要,研究了一下2008 CDC功能,觉得还不错,下面整理了一下研究过程,虽然比较粗略,但是基本上能用了,如果有补充请大家回复,我视情况修改原帖,谢谢. 背景: 在SQLServer2008之前,对数据变更的捕获通常使用触发器.时间戳等低效高成本的功能来实现,所以很多系统都没有做数据变更或者仅仅对核心表做监控. 自从SQLServer2008引入了数据变更捕获功能(后面简称为CDC)后,解决了很多烦恼问题,如客户2个

如何在SSIS 2012中使用数据变更捕获

最新项目稍有空隙,开始研究SQL Server 2012和2014的一些BI特性,参照(Matt)的一个示例,我们开始体验SSIS中的CDC(Change Data Capture,变更数据捕获). 注:如果需要了解关于SQL Server 2008中的CDC,请看这里http://www.cnblogs.com/downmoon/archive/2012/04/10/2439462.html),本文假定读者对CDC的工作方式已有所了解.^_^. 我们分三步完成实例: 1.准备基础数据: 2.设

SQLServer 2000 升级到 SQLServer 2008 性能之需要注意的地方之一_MsSql

测试sql: 复制代码 代码如下: SET STATISTICS IO ON SET STATISTICS TIME ON SELECT COUNT(1) FROM dbo.tbtext a INNER LOOP JOIN dbo.tbtext b ON a.id = b.id option (maxdop 1) SET STATISTICS IO Off SET STATISTICS TIME Off 表结构: 复制代码 代码如下: CREATE TABLE [dbo].[tbtext]( [

MS sqlserver 2008数据库转换成2000版本的方法_MsSql

话说本来我的电脑有个2000的数据库,去年我在那个电脑上新装了一个2005的数据库.前不久我买了台新电脑,装了数据库2008 将在旧电脑上的一个数据库附加到了2008上面.做完项目,将数据库传到空间时,空间数据库是2000的,报错说我的数据库是2005的 现在我想把我的数据库转成2000,但是我的2008数据库的脚本向导里面没有转成2000这个选项,2005的版本又附加不上去. 关于这个问题,上网请教了网友,真实人多力量大,给出了一系列答案,下面就为大家整理的内容,希望可以帮助大家解决SQLSe

debezium - 数据实时捕获和传输管道(CDC)

标签 PostgreSQL , debezium , kafka , CDC 背景 在业务系统中,会涉及到多个数据源的数据流转,例如在线系统的数据流转到分析系统.流计算系统.搜索引擎.缓存系统.事件处理系统等. 为了实现这一目的,CDC系统应运而生,CDC系统用于捕获上游数据的实时变化(已提交的数据),写入消息队列(例如KAFKA)持久化.下游系统,消费消息队列的数据.从而实现实时的数据流转. debezium是一个开源的分布式CDC系统,支持对接各种数据源,将上游已持久化的数据变更捕获后写入消

网络数据包捕获函数库Libpcap安装与使用（非常强大）

1.Libpcap简介 Libpcap是Packet Capture Libray的英文缩写,即数据包捕获函数库.该库提供的C函数接口用于捕捉经过指定网络接口的数据包,该接口应该是被设为混杂模式.这个在原始套接子中有提到. 著名的软件TCPDUMP就是在Libpcap的基础上开发而成的.Libpcap提供的接口函数实现和封装了与数据包截获有关的过程. Libpcap提供了用户级别的网络数据包捕获接口,并充分考虑到应用程序的可移植性.Libpcap可以在绝大多数Linux平台上运行.在Window

SQL Server 2008的CDC功能

CDC(Change Data Capture)通过对事务日志的异步读取,记录DML操作的发生时间.类型和实际影响的 数据变化,然后将这些数据记录到启用CDC时自动创建的表中.通过cdc相关的存储过程,可以获取详细的 数据变化情况.由于数据变化是异步读取的,因此对整体性能的影响不大,远小于通过Trigger实现的数 据变化记录. 下面我用一个实例讲解这个功能.该功能主要在ETL解决方案中比较有用. USE AdventureWorksDW; GO EXECUTE sys.sp_cdc_enabl

SQLServer 2008 R2中使用Cross apply统计最新数据和最近数据_mssql2008

使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数.表值函数作为右输入,外部表表达式作为左输入.通过对右输入求值来获得左输入每一行的计算结果,生成的行被组合起来作为最终输出.APPLY 运算符生成的列的列表是左输入中的列集,后跟右输入返回的列的列表. 注意:若要使用 APPLY,数据库兼容级别必须至少为 90. APPLY 有两种形式:CROSS APPLY 和 OUTER APPLY.CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行.OUTER A