1 --步骤：本文中以GPOSDB为例
  3 --第一步、对目标库显式启用CDC：
  4 --在当前库使用sys.sp_cdc_enable_db。返回0（成功）或1（失败）。
  5 --注意，无法对系统数据库和分发数据库启用该功能。且执行者需要用sysadmin角色权限。
  6 --该存储过程的作用域是整个目标库。包含元数据、DDL触发器、cdc架构和cdc用户。
  7 --使用以下代码启用：
  8 USE GPOSDB  --要启用CDC的数据库
  9 GO
 10 EXECUTE sys.sp_cdc_enable_db;
 11 GO
 12 --在一开始直接执行时，出现了报错信息：
 13 --消息22830，级别16，状态1，过程sp_cdc_enable_db_internal，第193 行
 14 --无法更新元数据来指示已对数据库AdventureWorks 启用了变更数据捕获。
 15 --执行命令'SetCDCTracked(Value = 1)' 时失败。
 16 --返回的错误为15517: '无法作为数据库主体执行，因为主体"dbo"
 17 --不存在、无法模拟这种类型的主体，或您没有所需的权限。'。请使用此操作和错误来确定失败的原因并重新提交请求。
 18 --这里引出了另外一个知识点：错误号 15517 的错误
 19 --这种错误会在很多地方出现，如还原数据库的时候也会有可能出现。
 20 --共同点是：某个/些存储过程使用了具有WITHEXECUTE AS 的选项。
 21 --使其在当前库具有了某个架构，但是当在别的地方执行时，由于没有这个架构，所以就报错，解决方法：
 22 ALTER AUTHORIZATION ON DATABASE::[GPOSDB] TO [sa]
 24 --经过检查，uspUpdateEmployeeHireInfo这个存储过程的确有：WITH EXECUTE AS CALLER
 25 --使用sa的原因是即使sa被禁用，sa还是存在的。所以不会报错。
 26 --现在重新执行：
 27 USE GPOSDB
 28 GO
 29 EXECUTE sys.sp_cdc_enable_db;
 30 GO
 31 --启用成功，然后通过以下语句检查是否成功：
 32 SELECT 
 33 is_cdc_enabled,
 34 CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述
 35 FROM    sys.databases
 36 WHERE   NAME = 'GPOSDB'
 38 --创建成功后，将自动添加CDC用户和CDC架构。
 39 --在用户和架构下面可以看到cdc用户和cdc架构
 41 --创建这两个用户、架构的原因是因为CDC要求独占方式使用这两个架构，所以要单独创建。
 42 --如果存在了非CDC功能创建的CDC用户、架构的话，则需要先删除该cdc命名的架构，才能开启。
 44 --第二步、对目标表启用CDC：
 45 --使用db_owner角色的成员执行sys.sp_cdc_enable_table为每个需要跟踪的表创建捕获实例。
 46 --然后通过sys.tables目录视图中的is_tracked_by_cdc列来判断是否创建成功。
 47 --默认情况下会对表的全部列做捕获。如果只需要对某些列做捕获，
 48 --可以使用@captured_column_list参数指定这些列。
 49 --如果要把更改表放到文件组里的话，最好创建单独的文件组（最起码与源表独立）。
 51 --如果不想控制访问角色，则@role_name必须显式设置为null。
 52 sys.sp_cdc_enable_table
 53     [ @source_schema = ] 'source_schema',
 54     [ @source_name = ] 'source_name' ,
 55     [ @role_name = ] 'role_name'
 56     [,[ @capture_instance = ] 'capture_instance' ]
 57     [,[ @supports_net_changes = ] supports_net_changes ]
 58     [,[ @index_name = ] 'index_name' ]
 59     [,[ @captured_column_list = ] 'captured_column_list' ]
 60     [,[ @filegroup_name = ] 'filegroup_name' ]
 61   [,[ @partition_switch = ] 'partition_switch' ]
 63 --例子：
 64 --把SYSTEMPARA 这个表开启变更捕获。
 65 USE GPOSDB
 66 GO
 67 EXEC sys.sp_cdc_enable_table @source_schema = 'DBO',
 68     @source_name = 'SYSTEMPARA',@role_name = NULL
 70 --然后查询是否成功：
 71 SELECT  name ,
 72         is_tracked_by_cdc ,
 73         CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用'
 74              ELSE 'CDC功能启用'
 75         END 描述
 76 FROM    sys.tables
 77 WHERE   OBJECT_ID = OBJECT_ID('dbo.systempara')
 78 --对表开启以后，可以在下图中看到多了很多cdc架构开头的表：
 79 --刷新一下GPOSDB数据库，在系统表下面可以看到多了下面几张表
 80 [cdc].[DBO_SYSTEMPARA_CT]
 81 [cdc].[change_tables]
 82 [cdc].[captured_columns]
 83 [cdc].[ddl_history]
 84 [cdc].[index_columns]
 85 [cdc].[lsn_time_mapping]
 86 [dbo].[systranschemas]
 87 [dbo].[dtproperties]
 89 --启动之后，可以看到SQLServer代理里面的作业，也出现了这两个作业：
 90 [cdc.GPOSDB_capture]
 91 [cdc.GPOSDB_cleanup]
 93 --在可编程性-》函数-》表值函数里，也多了两个函数
 94 [cdc].[fn_cdc_get_all_changes_DBO_SYSTEMPARA]
 95 [cdc].[fn_cdc_get_net_changes_DBO_SYSTEMPARA]
 97 --下面列出相关的存储过程：
 98 --Sys.sp_cdc_add_job
 99 --说明及例子
100 --Sys.sp_cdc_generate_wrapper_function
101 --说明及例子
102 --Sys.sp_cdc_change_job
103 --说明及例子
104 --Sys.sp_cdc_get_captured_columns
105 --说明及例子
106 --Sys.sp_cdc_cleanup_change_table
107 --说明及例子
108 --Sys.sp_cdc_get_ddl_history
109 --说明及例子
110 --Sys.sp_cdc_disable_db
111 --说明及例子  建议先禁用表，再禁用库
112 --Sys.sp_cdc_help_change_data_capture
113 --说明及例子
114 --Sys.sp_cdc_disable_table
115 --说明及例子
116 --Sys.sp_cdc_help_jobs
117 --说明及例子
118 --Sys.sp_cdc_drop_job
119 --说明及例子
120 --Sys.sp_cdc_scan
121 --说明及例子
122 --Sys.sp_cdc_enable_db
123 --说明及例子
124 --Sys.sp_cdc_start_job
125 --说明及例子
126 --Sys.sp_cdc_enable_table
127 --说明及例子
128 --Sys.sp_cdc_stop_job
129 --说明及例子
131 --函数：
132 --Cdc.fn_cdc_get_all_changes_<capture_instance>
133 --说明及例子
134 --Sys.fn_cdc_has_column_changed
135 --说明及例子
136 --Cdc.fn_cdc_get_net_changes_<capture_instance>
137 --说明及例子
138 --Sys.fn_cdc_increment_lsn
139 --说明及例子
140 --Sys.fn_cdc_decrement_lsn
141 --说明及例子
142 --Sys.fn_cdc_is_bit_set
143 --说明及例子
144 --Sys.fn_cdc_get_column_ordinal
145 --说明及例子
146 --Sys.fn_cdc_map_lsn_to_time
147 --说明及例子
148 --Sys.fn_cdc_get_max_lsn
149 --说明及例子
150 --Sys.fn_cdc_map_time_to_lsn
151 --说明及例子
152 --Sys.fn_cdc_get_min_lsn
153 --说明及例子
155 --------------------下面开始从头到尾做一个实际案例-------------------------
156 --下面开始从头到尾做一个实际案例
158 --步骤一:对目标库显式启用CDC
159 USE GPOSDB  --要启用CDC的数据库
160 GO
161 EXECUTE sys.sp_cdc_enable_db;
162 GO
165 --某些数据库可能存在一些存储过程包含有：execute as 等语句，此时会报错：
168 --文字描述：
169 --消息22830，级别16，状态1，过程sp_cdc_enable_db_internal，第186 行
170 --无法更新元数据来指示已对数据库AdventureWorks 启用了变更数据捕获。执行命令'SetCDCTracked(Value = 1)' 时失败。返回的错误为15517: '无法作为数据库主体执行，因为主体"dbo" 不存在、无法模拟这种类型的主体，或您没有所需的权限。'。请使用此操作和错误来确定失败的原因并重新提交请求。
171 --消息266，级别16，状态2，过程sp_cdc_enable_db_internal，第0 行
172 --EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数= 0，当前计数= 1。
173 --消息266，级别16，状态2，过程sp_cdc_enable_db，第0 行
174 --EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数= 0，当前计数= 1。
175 --消息3998，级别16，状态1，第1 行
176 --在批处理结束时检测到不可提交的事务。该事务将回滚。
177 --如果出现这个错误，目前的解决方法是执行下面语句，原因已在开头说明，对于没有使用EXECUTE AS的库，一般不会有这样的问题：
180 ALTER AUTHORIZATION ON DATABASE::[GPOSDB] TO [sa]
182 --现在重新执行：
183 USE GPOSDB
184 GO
185 EXECUTE sys.sp_cdc_enable_db;
186 GO
187 --通过以下语句检查是否成功：
188 SELECT 
189 is_cdc_enabled,
190 CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述
191 FROM    sys.databases
192 WHERE   NAME = 'GPOSDB'
194 --步骤二：对表启用CDC
195 USE GPOSDB
196 GO
197 EXEC sys.sp_cdc_enable_table
198 @source_schema = 'DBO',
199 @source_name = 'SYSTEMPARA',
200 @role_name = NULL,
201 @capture_instance=DEFAULT
202 GO
204 --然后查询是否成功：
205 SELECT  name ,
206         is_tracked_by_cdc ,
207         CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用'
208              ELSE 'CDC功能启用'
209         END 描述
210 FROM    sys.tables
211 WHERE   OBJECT_ID = OBJECT_ID('dbo.systempara')
213 --可以看到GPOSDB数据库里的系统表里新增了[cdc].[DBO_SYSTEMPARA_CT]表
219 --步骤三：检验，下面来改动数据
220 --先查询一下DBO_SYSTEMPARA_CT表
221 SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
223 --可以看到一条记录都没有，因为刚刚创建，并没有对原表systempara做任何增删改操作
225 --向[SystemPara]表插入一条记录
226 INSERT INTO [dbo].[SystemPara]
227         ( [ParaValue] ,
228           [Name] ,
229           [Description]
230         )
231 VALUES  ( '中国' , -- ParaValue - varchar(50)
232           '中国' , -- Name - varchar(50)
233           '中国'  -- Description - varchar(50)
234         )
236 --查询一下DBO_SYSTEMPARA_CT表，可以看到多了一条记录
237 SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
240 --更新[SystemPara]表的一条记录
241 UPDATE [dbo].[SystemPara] SET [ParaValue]='德国' WHERE [Description]='中国'
243 --查询一下DBO_SYSTEMPARA_CT表，可以看到多了两条记录
244 SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
246 --删除[SystemPara]表的一条记录
247 DELETE FROM  [dbo].[SystemPara]  WHERE [Description]='中国'
249 --查询一下DBO_SYSTEMPARA_CT表，可以看到多了一条记录
250 SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
252 --现在来分析一下DBO_SYSTEMPARA_CT表
253 --可以在联机丛书上查看：
254 --cdc.<capture_instance>_CT  
255 --可以看到，这样命名的表，是用于记录源表做过更改操作的表。
256 --对于insert/delete操作，会有对应的一行记录，而对于update，会有两行记录。
257 --对于__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）
258 --update语句的__$operation列的值是3和4，所以一条update语句对应两条记录
260 --对于__$start_lsn列：由于更改是来源与数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）
261 --但是微软不检查直接查询这类表，建议使用
262 --cdc.fn_cdc_get_all_changes_<捕获实例>
263 --cdc.fn_cdc_get_net_changes_<capture_instance>
264 -- 来查询
266 ----------------------------------------------------------
268 --下文开始，来熟悉各种函数、存储过程的使用，并尝试一些不正常的操作。
272 --日常使用情景：
273 --1、查询已经开启的捕获实例：
274 --返回所有表的变更捕获配置信息
275 USE [GPOSDB]
276 GO
277 EXECUTE sys.sp_cdc_help_change_data_capture;
278 GO
282 --查看对某个实例(即表)的哪些列做了捕获监控
283 USE [GPOSDB]
284 GO
285 EXEC sys.sp_cdc_get_captured_columns
286 @capture_instance = 'systempara' -- sysname
290 --也可以从下面中查找配置信息
291 SELECT * FROM msdb.dbo.cdc_jobs
298 --2、查看当前配置使用sp_cdc_help_jobs：
299 --从上文可以看到，启用cdc之后会自动创建了两个作业，可以先使用以下语句来查看：
300 sp_cdc_help_jobs
304 --对于一个大型的OLTP系统，由于数据更改会非常频繁，变更表中的数据会非常多，
305 --如果存放过久（最久可以存放100年），那对数据库空间是非常大的挑战。
306 --此时可以调整上图中cdc.AdventureWorks_cleanup 中retention（单位：分钟）。
308 --3、修改配置：sp_cdc_change_job
309 --显示原有配置
310 EXEC sp_cdc_help_jobs
311 GO
312 --更改数据保留时间为100分钟
313 EXECUTE sys.sp_cdc_change_job
314     @job_type = N'cleanup',
315     @retention=100
316 GO
318 --重启一下作业，以使设置生效
319 --停用作业
320 EXEC sys.sp_cdc_stop_job N'cleanup'
321 GO
322 --启用作业
323 EXEC sys.sp_cdc_start_job N'cleanup'
324 GO
325 --再次查看
326 EXEC sp_cdc_help_jobs
327 GO
328 --可以看到retention（单位：分钟）的值变为100了
330 --4、停止/启用、删除/创建作业
332 --停用作业
333 EXEC sys.sp_cdc_stop_job N'cleanup'
334 GO
335 --启用作业
336 EXEC sys.sp_cdc_start_job N'cleanup'
337 GO
339 --删除作业
340 EXEC sys.sp_cdc_drop_job @job_type = N'cleanup' -- nvarchar(20)
341 GO
342 --查看作业
343 EXEC sys.sp_cdc_help_jobs
344 GO
345 --可以看到现在只剩下一个作业了:cdc.GPOSDB_capture
348 --创建作业
349 EXEC sys.sp_cdc_add_job
350     @job_type = N'cleanup',
351     @start_job = 0,
352     @retention = 5760
354 --查看作业
355 EXEC sys.sp_cdc_help_jobs
356 GO
360 --5、DDL变更捕获：
361 --CDC除了捕获数据变更之外，还能捕获DDL操作的变化。
362 --前提是先要确保SQLServer 代理的启用，其实CDC功能都需要确保sql 代理正常运行
363 --因为所有操作都通过代理中的两个作业来实现的。
364 --现在先来对SYSTEMPARA 表修改一下，把PARAVALUE的长度加长
365 USE [GPOSDB]
366 GO
367 ALTER TABLE  [dbo].[SystemPara] ALTER COLUMN PARAVALUE VARCHAR(120) ;
368 GO
370 --然后查询ddl记录表
371 SELECT  * FROM    cdc.ddl_history
374 --6、使用CDC的函数来获取更改
375 --A、使用 [cdc].[fn_cdc_get_all_changes_DBO_SYSTEMPARA]
376 --函数报告捕获实例的当前所有可用更改
377 DECLARE @from_lsn BINARY(10) ,
378     @to_lsn BINARY(10)
379 SET @from_lsn = sys.fn_cdc_get_min_lsn('SYSTEMPARA')
380 SET @to_lsn = sys.fn_cdc_get_max_lsn()
381 SELECT  *
382 FROM    cdc.fn_cdc_get_all_changes_DBO_SYSTEMPARA(@from_lsn, @to_lsn,N'all update old');
383 GO
388 --B、获取某个时间段的更改信息：
389 --先根据日志序列号（logsequence number ,LSN）来获取跟踪变更数据
390 --Sys.fn_cdc_map_time_to_lsn获取变更范围内的最大、最小LSN值。可以使用
391 Smallest greater than;
392 smallest greater than orequal;
393 largest less than;
394 largest less than or equal;
396 --如查询某个时间段插入的数据
397 INSERT INTO [dbo].[SystemPara]
398         ( [ParaValue] ,
399           [Name] ,
400           [Description]
401         )
402 VALUES  ( '中国' , -- ParaValue - varchar(50)
403           '中国' , -- Name - varchar(50)
404           '中国'  -- Description - varchar(50)
405         )
407 GO
409 --检查数据
410 --1删除
411 --2插入
412 --3、4更改
413 --曾经插入过的记录就算delete了也可以查询出来
414 DECLARE @bglsn VARBINARY(10)= sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',
415                                                          '2013-10-21 12:00:00.997')
416 DECLARE @edlsn VARBINARY(10)= sys.fn_cdc_map_time_to_lsn('largest less than or equal',
417                                                          GETDATE())
418 SELECT  *
419 FROM    [cdc].[DBO_SYSTEMPARA_CT]
420 WHERE   [__$operation] = 2
421         AND [__$start_lsn] BETWEEN @bglsn AND @edlsn
425 --C、sys.fn_cdc_map_lsn_to_time 查询变更时间：
426 SELECT  [__$operation] ,
427         CASE [__$operation]
428           WHEN 1 THEN '删除'
429           WHEN 2 THEN '插入'
430           WHEN 3 THEN '更新（捕获的列值是执行更新操作前的值）'
431           WHEN 4 THEN '更新（捕获的列值是执行更新操作后的值）'
432         END [类型] ,
433         sys.fn_cdc_map_lsn_to_time([__$start_lsn]) [更改时间] ,
434         *
435 FROM    [cdc].[DBO_SYSTEMPARA_CT]
439 --D、获取LSN边界
440 SELECT  sys.fn_cdc_get_max_lsn() [数据库级别的最大LSN] ,
441         sys.fn_cdc_get_min_lsn('cdc.DBO_SYSTEMPARA_CT') [捕获实例的lsn]
444 这两个值可以用于上面提到的函数里面用于筛选数据之用。
447 ----------------------------------------------------------
448 --1. CDC的目的是什么？
450 --CDC就是极大地方便了我们获取某个表数据更新情况的一个机制。它通过一个独立的进程，
451 --异步读取日志文件，而不是触发器的方式工作。而且它的数据是会持久化保存到一个系统表的。
452 --2. CDC是不是SQL Server 2008特有的功能，对别的数据库或者早期版本是否起作用？
454 --CDC是SQL Server 2008特有的功能，而且是企业版特有的功能。开发版也有该功能，但仅用于测试场合。
458 --3. CDC读取日志，那么如果日志被截断了会怎么样？
460 --如果某部分日志，CDC的进程还没有读取，那么在截断日志时就会忽略这个部分,不能截断!!
462 --捕获进程是一个独立的，它随着代理服务启动而启动。两次扫描之间间隔5分钟。
466 --4. 系统表中的数据是否会永久存在？
468 --不会，它会被保留3天。会有一个清理的作业，每天晚上2点进行扫描。
472 --最后，补充一点的是，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;

change tracking和CDC，当分区切换的时候，这期间的数据都不能同步/记录，所以要分区切换最好还是用自增ID

作业 'cdc.MGLog_capture' 已成功启动。
作业 'cdc.MGLog_cleanup' 已成功启动。
警告: @allow_partition_switch 参数设置为 1。变更数据捕获功能将不跟踪在表中因分区切换而引入的变更，这样，在使用变更时，将导致数据不一致。有关在使用变更数据捕获时分区切换行为的详细信息，请参阅联机丛书。