--第一步、对目标库显式启用CDC： --在当前库使用sys.sp_cdc_enable_db。返回0（成功）或1（失败）。 --注意，无法对系统数据库和分发数据库启用该功能。且执行者需要用sysadmin角色权限。 --该存储过程的作用域是整个目标库。包含元数据、DDL触发器、cdc架构和cdc用户。 --使用以下代码启用： USE GPOSDB --要启用CDC的数据库 EXECUTE sys.sp_cdc_enable_db; --在一开始直接执行时，出现了报错信息： --消息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 EXECUTE sys.sp_cdc_enable_db; --启用成功，然后通过以下语句检查是否成功： 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 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_ --说明及例子 --Sys.fn_cdc_has_column_changed --说明及例子 --Cdc.fn_cdc_get_net_changes_ --说明及例子 --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的数据库 EXECUTE sys.sp_cdc_enable_db; --某些数据库可能存在一些存储过程包含有：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 EXECUTE sys.sp_cdc_enable_db; --通过以下语句检查是否成功： SELECT is_cdc_enabled, CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述 FROM sys.databases WHERE NAME = 'GPOSDB' --步骤二：对表启用CDC USE GPOSDB EXEC sys.sp_cdc_enable_table @source_schema = 'DBO', @source_name = 'SYSTEMPARA', @role_name = NULL, @capture_instance=DEFAULT --然后查询是否成功： 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]表 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._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_ -- 来查询 ---------------------------------------------------------- --下文开始，来熟悉各种函数、存储过程的使用，并尝试一些不正常的操作。 --日常使用情景： --1、查询已经开启的捕获实例： --返回所有表的变更捕获配置信息 USE [GPOSDB] EXECUTE sys.sp_cdc_help_change_data_capture; --查看对某个实例(即表)的哪些列做了捕获监控 USE [GPOSDB] EXEC sys.sp_cdc_get_captured_columns @capture_instance = 'systempara' -- sysname --也可以从下面中查找配置信息 SELECT * FROM msdb.dbo.cdc_jobs --对于一个大型的OLTP系统，由于数据更改会非常频繁，变更表中的数据会非常多， --如果存放过久（最久可以存放100年），那对数据库空间是非常大的挑战。 --此时可以调整上图中cdc.AdventureWorks_cleanup 中retention（单位：分钟）。 --3、修改配置：sp_cdc_change_job --显示原有配置 EXEC sp_cdc_help_jobs --更改数据保留时间为100分钟 EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention=100 --重启一下作业，以使设置生效 --停用作业 EXEC sys.sp_cdc_stop_job N'cleanup' --启用作业 EXEC sys.sp_cdc_start_job N'cleanup' --再次查看 EXEC sp_cdc_help_jobs --可以看到retention（单位：分钟）的值变为100了 --4、停止/启用、删除/创建作业 --停用作业 EXEC sys.sp_cdc_stop_job N'cleanup' --启用作业 EXEC sys.sp_cdc_start_job N'cleanup' --删除作业 EXEC sys.sp_cdc_drop_job @job_type = N'cleanup' -- nvarchar(20) --查看作业 EXEC sys.sp_cdc_help_jobs --可以看到现在只剩下一个作业了:cdc.GPOSDB_capture --创建作业 EXEC sys.sp_cdc_add_job @job_type = N'cleanup', @start_job = 0, @retention = 5760 --查看作业 EXEC sys.sp_cdc_help_jobs --5、DDL变更捕获： --CDC除了捕获数据变更之外，还能捕获DDL操作的变化。 --前提是先要确保SQLServer 代理的启用，其实CDC功能都需要确保sql 代理正常运行 --因为所有操作都通过代理中的两个作业来实现的。 --现在先来对SYSTEMPARA 表修改一下，把PARAVALUE的长度加长 USE [GPOSDB] ALTER TABLE [dbo].[SystemPara] ALTER COLUMN PARAVALUE VARCHAR(120) ; --然后查询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'); --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) --检查数据 --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服务，因为它有两个操作都是通过作业来启动的。

删除CDC

--删除CDC步骤
--要删除哪个库的cdc 先use哪个库
use [store_xinye]
--备份CDC表 将CDC系统表相关数据导出来
select * into cdc_captured_columns from cdc.captured_columns
select * into cdc_change_tables from cdc.change_tables
select * into cdc_dbo_Logistics_CT from cdc.dbo_Logistics_CT
select * into cdc_ddl_history from cdc.ddl_history
select * into cdc_index_columns from cdc.index_columns
select * into cdc_lsn_time_mapping from cdc.lsn_time_mapping
--禁用 这个操作会直接删除CDC系统表
EXECUTE sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'Logistics',
@capture_instance = N'dbo_Logistics';
--所有CDC相关对象都会删除 包括 cdc的capture和cleanup 作业
EXECUTE sys.sp_cdc_disable_db;

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

最近由于工作需要，研究了一下2008 CDC 功能 ，觉得还不错，下面整理了一下研究过程，虽然比较粗略，但是基本上能用了，如果有补充请大家回复，我视情况修改原帖，谢谢。在 SQLServer 2008之前，对 数据 变更 的 捕获 通常 使用 触发器、时间戳等低效高成本的 功能 来 实现 ，所以很多系统都没有做 数据 变更 或者仅仅对核心表做监控。自从 SQLServer 2008引入了 数据 变更 捕获 功能 （后面简称为 CDC ）后，解决了很多烦恼问题，如客户2个月前更改了一个 数据 ，但是由于服务器空间不足，经常只能保留一个月的备份。此时客户为了逃避因为更改 数据 带来的较大负面影响，往往会把责任推给软件开发商或者运维商，如果此时有 变更 捕获 的话，就

开启 SQLServer 数据 库的 CDC 时，运行报错：       无法更新元 数据 来指示已对 数据 库 XXX 启用了 变更 数据 捕获 。执行命令 ‘Set CDC Tracked(Value = 1)’ 时失败。返回的错误为 15517: ‘无法作为 数据 库主体执行，因为主体 “dbo” 不存在、无法模拟这种类型的主体，或您没有所需的权限。’ 解决办法： 如果是上面的报错，可以切换成 sa用户试一试： ALTER AUTHORIZATION ON DATABASE

在 SQLServer 2008之前，对 数据 变更 的 捕获 通常 使用 触发器、时间戳等低效高成本的 功能 来 实现 ，所以很多系统都没有做 数据 变更 或者仅仅对核心表做监控。 自从 SQLServer 2008引入了 数据 变更 捕获 功能 （后面简称...

介绍:SQL Server 2008 变更 数据 捕获 SQL Server 2008的 CDC 函数读取激活了 CDC 的每个表所关联的事务日志来记录系统表中的 变更 。它将这些文件写到同一个 数据 库的系统表中，这些系统表是可以通过直接查询或系统函数来访问的。 让我们看些示例代码并看看每一步有什么改变。 使用 示例代码来跟踪可变部分 首先，我们从一个全新的 数据 库开始。要跟踪这些 变更 ，打开对象浏览器并展开这些文...

变更 数据 捕获 （Change Data Capture ，简称 CDC ）记录 SQL Server 表的插入、更新和删除活动。 使用 变更 数据 捕获 可以更有效跟踪表对象DML历史操作，对 ETL 等 数据 转移也非常有用。 变更 数据 捕获 适用版本： SQL Server 2008 以上的 Enterprise Edition、Developer Edition 和 Evaluation Edition 变更 数据 捕获 原理： 变更 数据 捕获 的更改 数据 源为 SQL Server 事务日志。当对表启用 变更 数据 捕获 时，系统将

CDC （change data capture） 功能 主要 捕获 SQLServer 指定表的增删改操作，由于任何操作都会写日志（哪怕truncate），所以 CDC 的 捕获 来源于日志文件。日志文件会把更改应用到 数据 文件中，同时也会标记符合要求的 数据 标记为需要添加跟踪的项。然后通过一些配套函数，最后写入到 数据 仓库中。 本文就是根据 CDC 功能 来 实现 2张 数据 表的实时同步问题，希望对你有帮助。

注：关于SQL SERVER 2012在SP3补丁级别下对表开启 CDC ， 若该表有频繁的MERGE操作，则向 CDC 表中插入 数据 时会报违反唯一约束错误，尽管此时表中 并无 数据 。及时在关闭 CDC 后重新开启，仍不能解决。此问题系SQL SERVER的一个BUG,已在 SP4补丁中修复。所以若遇到此问题，升级补丁即可。 use test /*在可以对表启用 变更 数据 捕获 之前，必须先对 数据 ...