使用SQLServer 2008的CDC功能實作資料變更捕獲
CDC:Change Data Capture
轉載自：
http://blog.csdn.net/dba_huangzj/article/details/8130448
http://www.cnblogs.com/chenxizhang/archive/2009/05/16/1458388.html
1 --步驟：本文中以GPOSDB為例
2
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]
23
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'
37
38 --建立成功後，将自動添加CDC使用者和CDC架構。
39 --在使用者和架構下面可以看到cdc使用者和cdc架構
40
41 --建立這兩個使用者、架構的原因是因為CDC要求獨占方式使用這兩個架構，是以要單獨建立。
42 --如果存在了非CDC功能建立的CDC使用者、架構的話，則需要先删除該cdc命名的架構，才能開啟。
43
44 --第二步、對目标表啟用CDC：
45 --使用db_owner角色的成員執行sys.sp_cdc_enable_table為每個需要跟蹤的表建立捕獲執行個體。
46 --然後通過sys.tables目錄視圖中的is_tracked_by_cdc列來判斷是否建立成功。
47 --預設情況下會對表的全部列做捕獲。如果隻需要對某些列做捕獲，
48 --可以使用@captured_column_list參數指定這些列。
49 --如果要把更改表放到檔案組裡的話，最好建立單獨的檔案組（最起碼與源表獨立）。
50
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' ]
62
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
69
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]
88
89 --啟動之後，可以看到SQLServer代理裡面的作業，也出現了這兩個作業：
90 [cdc.GPOSDB_capture]
91 [cdc.GPOSDB_cleanup]
92
93 --在可程式設計性-》函數-》表值函數裡，也多了兩個函數
94 [cdc].[fn_cdc_get_all_changes_DBO_SYSTEMPARA]
95 [cdc].[fn_cdc_get_net_changes_DBO_SYSTEMPARA]
96
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 --說明及例子
130
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 --說明及例子
154
155 --------------------下面開始從頭到尾做一個實際案例-------------------------
156 --下面開始從頭到尾做一個實際案例
157
158 --步驟一:對目标庫顯式啟用CDC
159 USE GPOSDB --要啟用CDC的資料庫
160 GO
161 EXECUTE sys.sp_cdc_enable_db;
162 GO
163
164
165 --某些資料庫可能存在一些存儲過程包含有：execute as 等語句，此時會報錯：
166
167
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的庫，一般不會有這樣的問題：
178
179
180 ALTER AUTHORIZATION ON DATABASE::[GPOSDB] TO [sa]
181
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'
193
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
203
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')
212
213 --可以看到GPOSDB資料庫裡的系統表裡新增了[cdc].[DBO_SYSTEMPARA_CT]表
214
215
216
217
218
219 --步驟三：檢驗，下面來改動資料
220 --先查詢一下DBO_SYSTEMPARA_CT表
221 SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
222
223 --可以看到一條記錄都沒有，因為剛剛建立，并沒有對原表systempara做任何增删改操作
224
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 )
235
236 --查詢一下DBO_SYSTEMPARA_CT表，可以看到多了一條記錄
237 SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
238
239
240 --更新[SystemPara]表的一條記錄
241 UPDATE [dbo].[SystemPara] SET [ParaValue]='德國' WHERE [Description]='中國'
242
243 --查詢一下DBO_SYSTEMPARA_CT表，可以看到多了兩條記錄
244 SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
245
246 --删除[SystemPara]表的一條記錄
247 DELETE FROM [dbo].[SystemPara] WHERE [Description]='中國'
248
249 --查詢一下DBO_SYSTEMPARA_CT表，可以看到多了一條記錄
250 SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
251
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語句對應兩條記錄
259
260 --對于__$start_lsn列：由于更改是來源與資料庫的事務日志，是以這裡會儲存其事務日志的開始序列号（LSN）
261 --但是微軟不檢查直接查詢這類表，建議使用
262 --cdc.fn_cdc_get_all_changes_<捕獲執行個體>
263 --cdc.fn_cdc_get_net_changes_<capture_instance>
264 -- 來查詢
265
266 ----------------------------------------------------------
267
268 --下文開始，來熟悉各種函數、存儲過程的使用，并嘗試一些不正常的操作。
269
270
271
272 --日常使用情景：
273 --1、查詢已經開啟的捕獲執行個體：
274 --傳回所有表的變更捕獲配置資訊
275 USE [GPOSDB]
276 GO
277 EXECUTE sys.sp_cdc_help_change_data_capture;
278 GO
279
280
281
282 --檢視對某個執行個體(即表)的哪些列做了捕獲監控
283 USE [GPOSDB]
284 GO
285 EXEC sys.sp_cdc_get_captured_columns
286 @capture_instance = 'systempara' -- sysname
287
288
289
290 --也可以從下面中查找配置資訊
291 SELECT * FROM msdb.dbo.cdc_jobs
292
293
294
295
296
297
298 --2、檢視目前配置使用sp_cdc_help_jobs：
299 --從上文可以看到，啟用cdc之後會自動建立了兩個作業，可以先使用以下語句來檢視：
300 sp_cdc_help_jobs
301
302
303
304 --對于一個大型的OLTP系統，由于資料更改會非常頻繁，變更表中的資料會非常多，
305 --如果存放過久（最久可以存放100年），那對資料庫空間是非常大的挑戰。
306 --此時可以調整上圖中cdc.AdventureWorks_cleanup 中retention（機關：分鐘）。
307
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
317
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了
329
330 --4、停止/啟用、删除/建立作業
331
332 --停用作業
333 EXEC sys.sp_cdc_stop_job N'cleanup'
334 GO
335 --啟用作業
336 EXEC sys.sp_cdc_start_job N'cleanup'
337 GO
338
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
346
347
348 --建立作業
349 EXEC sys.sp_cdc_add_job
350 @job_type = N'cleanup',
351 @start_job = 0,
352 @retention = 5760
353
354 --檢視作業
355 EXEC sys.sp_cdc_help_jobs
356 GO
357
358
359
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
369
370 --然後查詢ddl記錄表
371 SELECT * FROM cdc.ddl_history
372
373
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
384
385
386
387
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;
395
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 )
406
407 GO
408
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
422
423
424
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]
436
437
438
439 --D、擷取LSN邊界
440 SELECT sys.fn_cdc_get_max_lsn() [資料庫級别的最大LSN] ,
441 sys.fn_cdc_get_min_lsn('cdc.DBO_SYSTEMPARA_CT') [捕獲執行個體的lsn]
442
443
444 這兩個值可以用于上面提到的函數裡面用于篩選資料之用。
445
446
447 ----------------------------------------------------------
448 --1. CDC的目的是什麼？
449
450 --CDC就是極大地友善了我們擷取某個表資料更新情況的一個機制。它通過一個獨立的程序，
451 --異步讀取日志檔案，而不是觸發器的方式工作。而且它的資料是會持久化儲存到一個系統表的。
452 --2. CDC是不是SQL Server 2008特有的功能，對别的資料庫或者早期版本是否起作用？
453
454 --CDC是SQL Server 2008特有的功能，而且是企業版特有的功能。開發版也有該功能，但僅用于測試場合。
455
456
457
458 --3. CDC讀取日志，那麼如果日志被截斷了會怎麼樣？
459
460 --如果某部分日志，CDC的程序還沒有讀取，那麼在截斷日志時就會忽略這個部分,不能截斷!!
461
462 --捕獲程序是一個獨立的，它随着代理服務啟動而啟動。兩次掃描之間間隔5分鐘。
463
464
465
466 --4. 系統表中的資料是否會永久存在？
467
468 --不會，它會被保留3天。會有一個清理的作業，每天晚上2點進行掃描。
469
470
471
472 --最後，補充一點的是，CDC功能依賴Agent服務，因為它有兩個操作都是通過作業來啟動的。 删除cdc
--删除CDC步驟
--要删除哪個庫的cdc 先use哪個庫
use [store_xinye]
go
--查詢每個CDC表的配置 填寫@capture_instance需要用到
EXEC sys.sp_cdc_help_change_data_capture
--備份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。變更資料捕獲功能将不跟蹤在表中因分區切換而引入的變更，這樣，在使用變更時，将導緻資料不一緻。有關在使用變更資料捕獲時分區切換行為的詳細資訊，請參閱聯機叢書。
f
![10萬并發量，如何保證MySQL與redis的資料同步？，解決思路如下1、在cud不頻繁的場景下，可以用讀寫鎖進行，這個[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)