浅析SQL 2008的Change Data Capture功能
2008-08-21 09:56:20 来源:WEB开发网 闂傚倸鍊搁崐鎼佸磹閹间礁纾归柟闂寸绾惧綊鏌熼梻瀵割槮缁炬儳缍婇弻鐔兼⒒鐎靛壊妲紒鐐劤缂嶅﹪寮婚悢鍏尖拻閻庨潧澹婂Σ顔剧磼閻愵剙鍔ょ紓宥咃躬瀵鎮㈤崗灏栨嫽闁诲酣娼ф竟濠偽i鍓х<闁绘劦鍓欓崝銈囩磽瀹ュ拑韬€殿喖顭烽幃銏ゅ礂鐏忔牗瀚介梺璇查叄濞佳勭珶婵犲伣锝夘敊閸撗咃紲闂佺粯鍔﹂崜娆撳礉閵堝洨纾界€广儱鎷戦煬顒傗偓娈垮枛椤兘骞冮姀銈呯閻忓繑鐗楃€氫粙姊虹拠鏌ュ弰婵炰匠鍕彾濠电姴浼i敐澶樻晩闁告挆鍜冪床闂備胶绮崝锕傚礈濞嗘挸绀夐柕鍫濇川绾剧晫鈧箍鍎遍幏鎴︾叕椤掑倵鍋撳▓鍨灈妞ゎ厾鍏橀獮鍐閵堝懐顦ч柣蹇撶箲閻楁鈧矮绮欏铏规嫚閺屻儱寮板┑鐐板尃閸曨厾褰炬繝鐢靛Т娴硷綁鏁愭径妯绘櫓闂佸憡鎸嗛崪鍐簥闂傚倷鑳剁划顖炲礉閿曞倸绀堟繛鍡樻尭缁€澶愭煏閸繃宸濈痪鍓ф櫕閳ь剙绠嶉崕閬嶅箯閹达妇鍙曟い鎺戝€甸崑鎾斥枔閸喗鐏堝銈庡幘閸忔﹢鐛崘顔碱潊闁靛牆鎳愰ˇ褔鏌h箛鎾剁闁绘顨堥埀顒佺煯缁瑥顫忛搹瑙勫珰闁哄被鍎卞鏉库攽閻愭澘灏冮柛鏇ㄥ幘瑜扮偓绻濋悽闈浶㈠ù纭风秮閺佹劖寰勫Ο缁樻珦闂備礁鎲¢幐鍡涘椽閸愵亜绨ラ梻鍌氬€峰ù鍥敋閺嶎厼鍨傞幖娣妼缁€鍐煥濠靛棙顥滈柣锕備憾濮婂宕掑▎鎺戝帯濡炪們鍨归敃銈夊煝瀹ュ鍗抽柕蹇曞Х椤斿姊洪幖鐐插姶闁告挻鐟╅幃姗€骞庨懞銉у幐闂佸憡鍔戦崝搴㈡櫠閺囩姷纾奸柍褜鍓熷畷姗€鍩炴径鍝ョ泿闂傚⿴鍋勫ú銈吤归悜鍓垮洭鏁冮埀顒勬箒濠电姴锕ら悧蹇涙偩濞差亝鐓涢悘鐐额嚙婵″ジ鏌嶇憴鍕伌鐎规洖宕埢搴ょ疀閹惧妲楃紓鍌氬€搁崐鐑芥⒔瀹ュ绀夐幖杈剧到閸ㄦ繃銇勯弽顐粶濡楀懘姊洪崨濠冨闁搞劍澹嗙划濠氬箮閼恒儱鈧敻鏌ㄥ┑鍡欏嚬缂併劏妫勯湁闁绘ǹ宕甸悾鐑樻叏婵犲啯銇濇俊顐㈠暙閳藉鈻庨幇顓炩偓鐑芥⒑鐠囨彃顒㈤柣顓у櫍瀹曪繝骞庨懞銉ヤ粧濡炪倖娲嶉崑鎾垛偓瑙勬礀閻栧ジ銆佸Δ浣哥窞閻庯綆鍋呴悵顐⑩攽閻樻剚鍟忛柛锝庡灣瀵板﹪宕滆閸嬫挾绮☉妯绘悙缂佺姵鐓¢弻娑㈠Ψ椤旂厧顫╅梺钘夊暟閸犳牠寮婚敐澶婃闁圭ǹ瀛╅崰鎰版⒑閼姐倕鏋庣紓宥咃躬瀵鈽夐埗鈹惧亾閿曞倸绠f繝闈涙川娴滎亝淇婇悙顏勨偓銈夊礈濞嗘挻鍋嬮柛鈩冪▓閳ь剚妫冨畷姗€顢欓崲澹洤绠圭紒顔煎帨閸嬫捇鎳犻鈧崵顒傜磽閸屾艾鈧娆㈤敓鐘茬獥婵°倕鎳庣粻浼存煙闂傚鍔嶉柛瀣ф櫊閺岋綁骞嬮敐鍡╂缂佺虎鍘搁崑鎾绘⒒娴h櫣甯涢柛鏃€娲滅划鏃堟濞磋櫕鐩畷姗€顢欓崗鍏夹氶梻渚€鈧偛鑻晶顖炴煏閸パ冾伃妤犵偞甯¢獮瀣攽閹邦亞纾婚梺璇叉唉椤骞愭搴g焼濞撴埃鍋撻柛鈺冨仱楠炲鏁傞挊澶夋睏闂備礁婀辩划顖滄暜閳哄倸顕遍柍褜鍓涚槐鎾存媴閻熸澘濮㈤悷婊勫閸嬬喖宕氶幒鎴旀瀻闁规儳鐤囬幗鏇炩攽閻愭潙鐏﹂柣顓у枛閳讳粙顢旈崼鐔哄幍闁荤喐鐟ョ€氼剚鎱ㄩ崶銊d簻闁靛濡囩粻鐐存叏婵犲啯銇濋柡灞芥嚇閹瑩鎳犵捄渚純濠电姭鎷冮崒姘ギ闂佸搫鐬奸崰鏍箹瑜版帩鏁冮柨婵嗘噽閿涙捇姊绘担鐟邦嚋缂佽瀚板畷鎴濃槈閵忕姷鍘撮梺鐟邦嚟婵參宕戦幘缁樻櫜閹煎瓨锚娴滅偓銇勯幘瀵糕姇婵炲懎锕弻锛勪沪閻e睗锝嗙箾绾板彉閭鐐茬箳娴狅箓鎸婃径濠呭帿闂傚倸鍊烽悞锕傛儑瑜版帒纾归柡鍥ュ灩缁犵娀鏌熼柇锕€鏋熸い顐f礋閺岀喖骞嗚閹界姴鈹戦娑欏唉闁哄本鐩獮姗€寮堕幋鐘点偡闂備礁鎲¢幐绋跨暦椤掑嫧鈧棃宕橀鍢壯囨煕閳╁喚娈樺ù鐘虫倐濮婃椽鎳¢妶鍛瘣闂佸搫鎳忛惄顖炲箖妤e啯鍊婚柦妯猴級閵娧勫枑濠㈣埖鍔曠壕濠氭煙閸撗呭笡闁哄懏鐓¢獮鏍垝閻熸澘鈷夐梺璇茬箰缁夌懓顫忛搹鍦<婵☆垵顕ч棄宥呪攽閻愭彃绾ч柨鏇樺灪娣囧﹪鎮界粙璺槹濡炪倖鐗楀銊╂偪閳ь剟姊婚崒姘偓鎼佹偋婵犲嫮鐭欓柟閭﹀枦婵娊鏌ゅù瀣珖缁炬崘妫勯湁闁挎繂鐗婇ˉ澶愭煟閹炬潙濮堥柟渚垮妼铻g紒瀣仢椤鈹戦垾鍐茬骇闁告梹鐟╅悰顔嘉熼崗鐓庣彴闂佽偐鈷堥崜锕€危娴煎瓨鈷掑ù锝嚽归弳閬嶆煙绾板崬浜扮€规洘鍔栫换婵喰掔粙鎸庡枠鐎殿喛鍩栭幆鏃堝箻鐎涙ɑ婢戝┑锛勫亼閸婃牕顫忔繝姘ラ悗锝庝憾閸熷懘鏌曟径娑滅濞存粍绮嶉妵鍕箻鐠鸿桨绮跺┑鈩冨絻椤兘寮婚敐澶嬫櫜闁搞儜鍐ㄧ婵°倗濮烽崑鐐垫暜閿熺姷宓侀悗锝庡枛缁秹鏌嶈閸撶喖骞冨Δ浣虹瘈婵﹩鍘搁幏娲煟閻斿摜鎳冮悗姘煎弮瀹曟洖螖閸涱喚鍘卞┑鈽嗗灥閵嗏偓闁稿鎹囬幃銏ゅ箵閹烘垹闃€婵犵數濮烽弫鍛婃叏閻戣棄鏋侀柛娑橈攻閸欏繘鏌i幋锝嗩棄闁哄绶氶弻娑樷槈濮楀牊鏁鹃梺鍛婄懃缁绘垿濡甸崟顖氱闁告鍋熸禒鑲╃磼閻愵剙鍔ゆい顓犲厴瀵鎮㈤悡搴n槶閻熸粌绻掗弫顔尖槈閵忥紕鍘撻梻浣哥仢椤戝懘鎮橀敃鍌涚厪闁搞儜鍐句純濡ょ姷鍋炵敮鈥崇暦閸楃儐娓婚柟顖嗗本顥$紓鍌氬€搁崐鎼佸磹妞嬪海鐭嗗〒姘e亾閽樻繈姊洪鈧粔鎾几娴g硶鏀介柣妯挎珪閻ㄦ垹鈧鎸风欢姘跺蓟濞戙垹鐒洪柛鎰典簴婵洭姊虹粙鍖″姛闁稿繑锕㈠璇测槈濡攱鏂€闂佺硶鍓濋〃蹇斿閳ь剚淇婇悙顏勨偓鏍ь潖瑜版帒绀夐柡鍥ュ灩閻撴﹢鏌熸潏楣冩闁稿﹤顭烽弻娑㈠Ψ閵忊剝鐝栭柡宥忕節濮婄粯鎷呴崨濠傛殘闂佸湱枪椤兘骞冮悜鑺ユ櫆闁伙絽澶囬弨铏節閻㈤潧孝婵炶绠撳畷鐢稿礃椤旂晫鍘撻梺鍛婄箓鐎氼剟寮抽悢鍏肩叆婵炴垶鐟ч惌鎺撴叏婵犲洨绱伴柕鍥ㄥ姍楠炴帡骞嬪⿰鍐╃€抽梻鍌欑閹诧繝鎮烽妷锔绘闁归棿绀侀悡婵嬫煙閻愵剚鐏遍柛顐邯閺屾盯顢曢妶鍛亖闂佸憡蓱閹倿寮婚敐鍫㈢杸闁哄洨鍋橀幋椋庣磼缂併垹骞栭柣鏍帶閻g兘骞嬮敃鈧粻濠氭偣閸ヮ亜鐨洪柣銈傚亾婵犵數鍋犻幓顏嗗緤娴犲绠熼柨鐔哄Т绾捐銇勯弽顐沪闁抽攱鍨归惀顏堫敇閻愭潙娅f繛瀛樼矊缂嶅﹪骞冪捄琛℃闁哄诞鍐ㄐ曢梻浣虹《閺備線宕戦幘鎰佹富闁靛牆妫楃粭鎺楁煕閻曚礁浜伴柟顖氬暙鐓ゆい蹇撴噽閸樺憡绻涙潏鍓у埌婵犫偓鏉堛劍娅犳い蹇撶墛閻撳啴鎮峰▎蹇擃仼闁诲繑鎸抽弻鐔碱敊閻e本鍣伴悗娈垮枛閻栧ジ鐛€n喗鍋愰弶鍫厛閺佸洭姊婚崒姘偓椋庣矆娴i潻鑰块弶鍫涘妿娴犳岸姊绘担渚敯濠殿喓鍊楅崚鎺撴償閵娿儳顦梺鍦劋椤ㄥ懐鐚惧澶嬬厱妞ゆ劑鍊曢弸搴∶归悩鐑橆仩缂佽鲸鎸婚幏鍛村礈閹绘帒澹嶆俊鐐€栧ú妯荤箾婵犲洤鏋侀柛鎰靛枛绾惧吋绻涢幋鐐跺妤犵偛鐗撳缁樻媴閸涘﹥鍎撳┑鐐茬湴閸ㄨ棄鐣峰┑鍡欐殕闁告洦鍓欓埀顒€鐖奸弻锝呂熼懖鈺佺闂佺粯鎸诲ú鐔煎蓟閻斿吋鍤嬫い鎺嗗亾濠碉紕鍘ч湁婵犲﹤瀚崝銈夋煃鐟欏嫬鐏撮柡浣哥Ч瀹曠喖顢曢埄鍐╃窔闂傚倷鑳舵灙闁挎洏鍎甸幃褔鎮╅懠顒佹濠电娀娼ч鍡涘疾濠靛鐓冪憸婊堝礈閻旂厧鐏抽柨鏇炲€搁柨銈嗕繆閵堝倸浜鹃梺缁樺笒閻忔岸濡甸崟顖氱鐎广儱鐗嗛崢锛勭磽娴e搫孝濠⒀傜矙閸┾偓妞ゆ巻鍋撻柛妯荤矒瀹曟垿骞橀弬銉︽杸闂佺粯枪娴滎剛绮i弮鍫熺厱閻庯綆鍋掑▓鏃堟煃鐟欏嫬鐏存い銏$懅濞戠敻鎮滈悾灞藉冀濠电姷鏁搁崑娑㈠箯閹寸姴绶ら柛顭戝暎閿濆绠涢柡澶庢硶椤斿﹪姊洪悷鏉挎毐缁剧虎鍙冨畷浼村箻鐠囪尙顔嗛梺缁樶缚缁垶宕甸幋锔界厾缂佸娉曟禒娑欐叏閿濆棗濮嶆慨濠傤煼瀹曟帒顫濋钘変壕闁绘垼濮ら崵鍕煠閸濄儲鏆╁ù鐘崇缁绘繈鎮介棃娑楃捕濡炪倖娲﹂崣鍐ㄧ暦濡も偓铻e〒姘煎灠濞堛劌顪冮妶鍡楀闁稿﹥鐗滈埀顒佺濮樸劑鍩€椤掑倹鍤€濠㈢懓锕畷浼村冀瑜夐弸鏃堟煏婵犲繐顩紒鈾€鍋撻梻浣圭湽閸ㄨ棄岣胯閻楀海绱撴担鍝勪壕婵犮垺岣跨划鏃堟偡闁箑娈ㄩ梺鍝勮閸庤京绮婚悽鍛婄厵闁绘垶岣跨粻姗€鏌涢悙鍨毈闁哄矉缍侀幃鈺呮倻濮楀棔鍝楅梺璇茬箰缁诲牓宕濆畝鍕垫晩闊洦绋戝敮閻熸粌顦靛畷鎴﹀箻閼搁潧鏋傞梺鍛婃处閸撴瑧鍠婂鍛斀闁宠棄妫楁禍婵堢磼鐠囨彃鈧潡鏁愰悙鍓佺杸婵炴垶鐟﹂崕顏堟⒑闂堚晛鐦滈柛姗€绠栭弫宥呪堪閸愶絾鏂€闂佸疇妫勫Λ妤呮倶閻樼粯鐓欑痪鏉垮船娴滀即鏌ㄥ┑鍫濅粶妞ゆ挸銈稿畷鍫曞煛閸愯法闂繝鐢靛仩閹活亞绱炴笟鈧棢闁规崘顕х粈澶屸偓骞垮劚椤︿即鎮″▎鎴犵<閻庯綆浜炴禒銏ゆ煛閸℃稐鎲鹃柡宀嬬秮閺佹劙宕惰楠炲螖閻橀潧浠滄い鎴濐樀瀵偊宕橀鑲╁姦濡炪倖甯掗崐缁樼▔瀹ュ應鏀介柣妯虹-椤f煡鏌涚€e墎绉柡灞剧洴婵$兘骞嬪┑鍡樻婵°倗濮村ú顓㈠箖濡ゅ啯鍠嗛柛鏇ㄥ墮绾板秶绱撴担鍓叉Ч闁瑰憡濞婇崹楣冨籍閸繄顦ㄥ銈嗘煥濡插牐顦归柡灞剧洴閸╁嫰宕楅悪鈧禍顏勎涢崟顐悑闁搞儮鏅濋敍婵囩箾鏉堝墽鍒板鐟帮躬瀹曟洟骞囬悧鍫㈠幈闂侀潧枪閸庨亶銆傚畷鍥╃<妞ゆ梻鈷堥崕蹇斻亜閹惧啿鎮戠€垫澘瀚埀顒婄秵娴滄牠宕戦幘缁樼叆閻庯絻鍔嬬花濠氭⒑閻熺増鎯堢紒澶婄埣钘濋柨鏃堟暜閸嬫挸鈻撻崹顔界亪闂佽绻戠换鍫ュ春閻愬搫绠i柨鏇楀亾闁绘搫绻濋弻娑㈠焺閸愮偓鐣兼繛瀵稿閸ㄨ泛顫忓ú顏勫窛濠电姴娴烽崝鍫曟⒑閸涘﹥澶勯柛娆忛鐓ら柛娑橈梗缁诲棝鏌曢崼婵堢闁告帊鍗抽弻娑㈡偆娴e摜浠搁悗瑙勬礃閸旀瑥鐣疯ぐ鎺濇晝闁挎繂鎳庢导搴㈢節绾版ɑ顫婇柛銊﹀▕瀹曘垼顦崇紒鍌氱У閵堬綁宕橀埡浣插亾閸偅鍙忔俊顖滃帶娴滈箖鎮楀鐐

在常见的企业数据平台管理中有一项任务是一直困扰SQL Server DBA们的,这就是对数据更新的监控。很多数据应用都需要捕获对业务数据表的更新。笔者见过几种解决方案:
1、在数据表中加入特殊的标志列;
2、 通过在数据表上创建触发器;
3、通过第三方产品,例如Lumigent的Log Explorer。
其实第1种和第2中方案都不好,因为第1种方法需要在应用程序编码的时候尤为小心,如果有一段数据访问逻辑忘了更新标志位就会导致遗漏某些数据更新,而第2种方法对性能影响过于明显,因为触发器的性能开销是众所周知的。第3种方法其实属于一种叫做Log Audit的方案体系。因为SQL Server同其他关系型数据库一样,所有数据操作都会在日志中记录,因此通过分析日志就可以获得完整的数据操作历史。SQL Server其实早就有内部的API可供ISV开发者中Log Audit的方案,不过微软对这套API控制比较严格,只有签署了一堆协议的核心级合作伙伴才能了解这套API。
因此,现对业务数据更新的跟踪在SQL Server平台上一直是一件非常头疼的事情,用户需要在投入大量开发精力和投入额外采购成本之间做出选择。幸运的事,微软终于在SQL Server 2008中提供了一套半公开的Log Audit机制,就是我们所说的Change Data Capture,我们后面简称CDC。
CDC的工作原理
我们前面说过CDC是通过分析日志获得数据操作历史信息的,那么CDC的工作原理到底是怎么样的呢?下图可以非常贴切地说明这个功能的原理:
图1
◆当DML提交到应用数据库时,SQL Server必须写入日志,并在缓存中更新数据,然后在检查点将内存中的数据刷回数据文件。
◆CDC的内部进程根据CDC的设置,在日志文件中提取更新历史信息,并将这些个更新信息写入对应的更新跟踪表。
◆DBA或开发人员通过调用CDC的函数来访问更新跟踪表,提取感兴趣的更新历史信息,并通过ETL应用程序更新数据仓库。
◆理论上面更新跟踪表事会无限制增长的,因此CDC内部有一个清理进程,在默认情况下更新跟踪信息在写入跟踪表三天后会被自动清理。
CDC的配置
由于CDC是一项比较高端的功能,因此只有在SQL Server 2008的企业版、开发版和评估版中才能找到CDC功能。
启用数据库级别的CDC
要启用CDC功能,首先需要一个sysadmin服务器角色的成员用户激活数据库级别的CDC,这个过程可以通过sys.sp_cdc_enable_db_change_data_capture存储过程来完成。如果想知道一个数据库是否启用了CDC功能,可以通过查询sys.databases系统目录的is_cdc_enabled字段。
当一个数据库启用CDC功能后,SQL Server会自动在这个数据库中创建cdc架构和cdc用户,所有CDC相关的数据表和用户函数都会存放在cdc架构下。
CDC功能启用后,SQL Server会首先在cdc架构下创建五张表用于记录一些CDC的原数据,分别是ddl_history,change_tables,captured_columns,index_columns和lsn_time_mapping。
在数据库启用了CDC后,接下来我们就需要在数据表上启用CDC了。属于db_owner角色的用户可以通过存储过程sys.sp_cdc_enable_table_change_data_capture来启用对某张数据表的更新跟踪,一张数据表最多可以设置两个跟踪实例。每个跟踪实例中可以设置对原始数据表的所有列或部分列进行更新跟踪。如果想知道数据表是否进行了更新跟踪,DBA可以查询sys.tables系统目录的is_tracked_by_cdc字段。
对一张数据表启用CDC跟踪实例后,SQL Server会在cdc架构下创建一张数据表用于记录从日志中解析出来的更新历史信息。
一段CDC的评估脚本
为了评估CDC功能,我特地写了一段脚本如下:
1、首先创建一个测试数据库;
2、然后激活TestCDC数据库上的更新捕获功能;
USE TestCDC
GO
EXEC sp_cdc_enable_db_change_date_capture;
GO
执行了存储过程sp_cdc_enable_db_change_data_capture后,就会在数据库TestCDC中看到有一些新的表被创建了,分别是ddl_history,change_tables,captured_columns,index_columns和lsn_time_mapping,并且这5张表都是在cdc架构下。
3、然后在TestCDC数据库中创建测试表
USE TestCDC
GO
CREATE TABLE dbo.Product (ProductID int PRIMARY KEY NOT NULL,
ProductName nvarchar(100),
Category nvarchar(50))
GO
4、在dbo.Product表上激活更新跟踪
EX
EC sp_cdc_enable_table_change_data_capture 'dbo',
'Product', @role_name= NULL, @supports_net_changes =1;
成功提交上述命令后,就可以在数据表change_tables,captured_columns和index_columns表中看到相应的记录,其中change_table中一条,capture_column中三条,index_columns中一条。同时cdc架构下有增加了一张新表叫做dbo_Product_CT,这张表的结构和Product表的结构有点相似,Product表中的三列在dbo_Product_CT中都有,同时dbo_Product_CT表中还增加了_$start_lsn,_$end_lsn,_$seqval,_$operation和_$update_mask五个新的字段。ITPUB个人空间-hU:i B%P%B&X
其实在存储过程sp_cdc_enable_table_change_data_capture中有一系列的参数,在这里我们为了简化忽略了一个参数就是@captured_column_list,这个参数可以对表中特定的某些字段启用更新跟踪。
5、在Product表上提交INSERT语句
INSERT INTO dbo.Product VALUES (1, N'ABC', N'A');
提交完了这条命令后,就会在lsn_time_mapping和dbo_Product_CT中分别看到一条新记录。
其中dbo_Product_CT表中的_$operation字段的值是2,_$update_mask字段的值是0x07。 _$operation字段是代表DML操作类型,1是delete,2是insert,3是update的旧值,4是update的新值。
$update_mask字段是表示一个字段列表的掩码,那些在DML操作中被更新了的字段位为1,而没有更新的字段位为0。在本例中Product表一共有三列被跟踪,所以应该是一个三位的二进制数,右边低位第一位是第一列ProductID,低位第二位是第二列ProductName,第三位就是Category了。因为这是一次INSERT,所以更新涉及到了所有的三列,所以_$update_mask字段就应该是0x7了。
6、继续在Product表上提交UPDATE语句
UPDATE dbo.Product SET Category = N'B' WHERE ProductID = 1;
提交完这条命令后,当然也会在lsn_time_mapping和dbo_Product_CT中看到新记录了。不过这次lsn_time_mapping中是一条,而dbo_Product_CT中则是两条。(为什么会这样呢?建议大家自己试一下咯,一试就明白了。)
其中dbo_Product_CT表中的_$operation字段的值是第一条是3,第二条是4,_$update_mask字段的值两条都是0x04。
在这次操作中我们更新的是第三列,所以_$update_mask字段就应该是0x4了。(如果我们更新的是ProductID会发现_$update_mask并非是0x1,而同样是0x7,这估计是因为ProductID是主键,更新主键应该视同一条新的记录。)
7、再来一次UPDATE
UPDATE dbo.Product SET Category = N'A' WHERE ProductID = 1;
提交完这条命令后,在dbo_Product_CT中又看到两条新记录了。其中dbo_Product_CT表中的_$operation字段的值是第一条是3,第二条是4,_$update_mask字段的值两条都是0x04。(看来CDC确实会记录下数据的每次修改。)
8、继续在Product表上提交DML语句
DELETE dbo.Product WHERE ProductID = 1;
提交完了这条命令后,就会在lsn_time_mapping和dbo_Product_CT中分别看到一条新记录。
其中dbo_Product_CT表中的_$operation字段的值是1,_$update_mask字段的值是0x07。
9、提交一个DDL试试看
ALTER TABLE dbo.Product ADD Description nvarchar(100);
提交完这句命令后,只会在ddl_history表中看到一条新的记录。
10、然后再试试DML
UPDATE dbo.Product SET Description = N'NA';
提交完这句语句后,所有cdc架构下的表中都没有看到新记录。说明新增的列Description不跟踪更新了......估计有人会说(细心的人哦!):“这次当然看不到新记录了,因为在前面第7步我们已经删除了所有的记录,因此这次的UPDATE语句没有影响到任何记录,当然CDC的表中不会有任何记录了。”那么到底对Description更新会不会记录呢,经过测试确实是不记录的。
那么如果我们想对Description也进行更新跟踪应该怎么办呢?很简单的,由另外一个存储过程叫做sp_cdc_disable_table_change_data_capture可以禁用对某张表的更新跟踪,可以使用这个存储过程先对Product表禁用更新跟踪,然后再重新启用对Product表的更新跟踪就可以了。
11、最后试一下DROP命令
DROP TABLE dbo.Product;
dbo.Product表消失了,同时cdc.dbo_Product_CT表也消失了。
12. 评估结束。一定有人问,捕获到的更新怎么用呢,还有一堆系统函数和存储过程可以帮助用户,但是那段测试的过程就不详细写了。
其中最重要的应该就是cdc.fn_cdc_get_all_changes_和cdc.fn_cdc_get_net_changes_两个函数了,这两个函数可以帮助我们获取dbo_Product_CT表中数据,其中cdc.fn_cdc_get_all_changes_是用于获取所有更新,而cdc.fn_cdc_get_net_changes_则是用于获取精简后的更新,在精简的更新中有一些重复的更新就会被合并成一条记录,比如说我们把产品类型由A改为B,然后又改回A,在cdc.fn_cdc_get_all_changes_中应该有3条记录,而在cdc.fn_cdc_get_net_changes_中则只有1条记录。两个函数的范例如下(你会发现精简结果集的函数运算相当慢,至少在CTP4中是这样的,不知道以后的版本会不回有改进):
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Product(0x00000048000001760004,0x00000048000001F70004, 'all'); SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Product(0x00000048000001760004, 0x00000048000001F70004, 'all'); |
CDC功能的IO开销
很明显,CDC功能是会产生一定的IO和存储开销的,为了评估CDC功能产生的这些开销。我又进行了一段评测。
整个评估的思路是这样的:
1、创建两个数据库
2、在两个数据库中分别创建一张结构完全相同的表,一个数据库启用CDC功能,而另外一个禁用CDC功能
3、向两张表中写入相同行数的数据
4、视图sys.dm_io_virtual_file_stats来获得两个数据库文件上的
5、利用sysindexes来获得两个数据库中数据表的存储消耗情况
因为是在虚拟机中进行的测试,所以选取了比较小的数据表(AdventureWorks数据库中的SalesOrderDetails),大约有12万行数据。
评估的结果如下:
图2
从上面两张表中可以看到,CDC激活后日志文件的读会显著增加。原因是CDC在写更新跟踪表时,需要读取日志。
图3
图4
从上面两张表中可以看到激活CDC后数据文件的写入和日志文件写入都会显著增加,不过需要考虑到CDC激活后会需要多写一张表,在本例中就是dbo_SalesOrderDetails_CT,所以这种增加是可以理解的。当然在生产环境中并不会对数据表的所有列进行CDC监控,所以激活CDC对IO写入的影响还需要针对不同情况进行分析。
图5
从上面这张图可以看出,CDC激活后会生产数据表不会消耗更多的存储空间,但是更新跟踪表会需要俄外的存储空间。另外可以发现的一点是,在本例中dbo_SalesOrderDetail_CT表消耗的空间比SalesOrderDetail表多,这是因为在dbo_SalesOrderDetail_CT表中加入了一些额外的字段,例如_$start_lsn和_$end_lsn,同时注意观察dbo_SalesOrderDetail_CT表会发现,SQL Server在这张表上使用_$start_lsn、_$end_lsn和_$seqval三个字段作为聚簇索引,而SalesOrderDetail表上原来的聚簇索引(SalesOrderID,SalesOrderDetailID)再加上_$start_lsn、_$end_lsn和_$seqval三个字段则被创建为一个非聚簇索引,所以这就导致了dbo_SalesOrderDetail_CT表需要消耗比原始表更多的空间,不过原始数据表上的非聚簇索引不会在CDC跟踪表上被创建,这也就说明了原始数据表聚簇索引的大小也会对CDC引发的IO产生影响。
CDC对存储的消耗
为了进一步理解CDC功能对存储的消耗,特别整理了一下CDC的数据开销。首先CDC功能对数据库存储空间产生显著影响的两张表是cdc._CT表和cdc.lsn_time_mapping表,这里简称为表1和表2。
下面是对表1和表2作的一些较为深入地剖析:
1、表1和表2的数据
表1主要由3个binary(10)字段、1个int字段、1个varbinary(128)字段以及所有被选定更新跟踪的原始表字段构成。因此表1每行数据的尺寸大概是在30 + 4 + 5 (因为通常一张表需要监控的字段会在16个以内,所以暂定为2bytes的binary然后加上varbinary数据2个bytes的固定开销),也就是 39 + x(假定原始表需要监控的字段键总尺寸为x个字节)个字节。
表2则有1个binary(10)字段、2个datetime字段和1个varbinary(10)字段构成。因此表2每行数据应该是20 + 16 + 12 = 48个字节。
2、表1和表2的索引(这个不太好估算,因为不同的表聚簇索引的键值密度是不一样的,一般按照1/4的数据尺寸估算,只有多没有少啦!)
表1的3个binary(10)字段构成了聚簇索引,同时3个binary(10)字段加上原始数据表的聚簇索引构成一个非聚簇索引,同上面一样,我们假定原始表聚簇索引键是x个字节,那么表1的非聚簇索引每行是(30 + y(假定原始表聚簇索引键尺寸为y个字节) + 4(指向聚簇索引的内部指针))个字节。
而表2中的binary(10)字段构成了聚簇索引,其中1个datetime字段构成了非聚簇索引。因此表2的非聚簇索引每行是8 + 4 = 12个字节。
3、对原始数据表的一行数据进行UPDATE操作,会在表1中添加2行数据,而DELETE操作和INSERT操作则会增加1行数据;而对于表2则是每笔事务增加1行数据。
因此我们作如下假定,典型的OLTP环境:
1、原始数据表的聚簇索引为1个整型字段,同时需要监控的字段总尺寸为50字节(约为5个decimal(19)或5个char(10))。
2、对原始表提交100,000个事务。
3、产生1,000,000行次数据操作,其中UPDATE占60%,INSERT和DELETE占40%。
4、那么最终CDC产生的额外数据存储空间应该为(39 + 4 + 50) * (1000000 * 1.2 + 1000000 * 0.4) + 48 * 100000 = 153,600,000个字节,约为164MB(假定数据页填充率为90%)。
5、因此约合200MB左右。
经过这样的对比我们可以知道,CDC在生产环境特别是OLTP环境对存储空间的影响不算太明显的,当然这个还要取决于DBA在原始数据表上选取多少字段进行监控,以及这些字段的数据尺寸,同时还有原始数据表的聚簇索引键值密度。另外需要说明的是表1和表2都是由一个异步的进程通过读取日志来完成的,因此表1和表2的数据刷新和原始数据表的刷新会有一定的延时。
对部署CDC的建议ITPUB个人空间,经过以上测试,我们可以发现以下情况:
◆CDC激活会显著增加日志文件的读操作。
◆CDC激活后更新跟踪表会产生额外的写入,并消耗存储空间。
◆CDC激活后,原数据表的聚簇索引尺寸会影响到CDC产生的IO数据量,而原始数据表上的非聚簇索引则不会。
◆CDC激活后,被选定进行更新跟踪的列键值属性同样会影响到CDC产生的IO数据量和存储空间。ITPUB个人空间
因此如同微软建议的一样,在CDC激活的环境下,应该将更新跟踪表写入与原始表不同的文件组并存放在不同的存储设备上,注意控制需要监控的数据列尺寸,同时应该注意为日志文件选取可提高读取性能的存储硬件上,比如RAID10。
- ››SQL Server 2008 R2 下如何清理数据库日志文件
- ››sqlite 存取中文的解决方法
- ››SQL2005、2008、2000 清空删除日志
- ››SQL Server 2005和SQL Server 2000数据的相互导入...
- ››sql server 2008 在安装了活动目录以后无法启动服...
- ››sqlserver 每30分自动生成一次
- ››sqlite 数据库 对 BOOL型 数据的插入处理正确用法...
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
更多精彩
赞助商链接