知方可补不足~用CDC功能来对数据库变更进行捕捉回到目录
如果我们希望监视一个数据表的变化,在sql2008之前的版本里,在数据库端可能想到的只有触发器,或者在程序端通过监视自己的insert,update,delete来实现相应的功能,这种实现无疑是让我们感到恐惧的,不够灵活的,而当进行sql2008后,这种情况得到了本质的改变,sql2008为我们提供了CDC功能,它可以实时对指定的数据表进行监控,当前它同时对产生SQL的一些负载。
CDC工作流程CDC功能主要捕获SQLServer指定表的增删改操作,由于任何操作都会写日志(哪怕truncate),所以CDC的捕获来源于日志文件。日志文件 会把更改应用到数据文件中,同时也会标记符合要求的数据标记为需要添加跟踪的项。然后通过一些配套函数,最后写入到数据仓库中。
CDC实现步骤第一步、对目标库显式启用CDC:
在当前库使用sys.sp_cdc_enable_db。返回0(成功)或1(失败)。注意,无法对系统数据库和分发数据库启用该功能。且执行者需要用sysadmin角色权限。
该存储过程的作用域是整个目标库。包含元数据、DDL触发器、cdc架构和cdc用户。
使用以下代码启用:
USE tableNameGoEXECUTE sys.sp_cdc_enable_dbGO
当设置好数据库的CDC功能后,我们可以查看一下是否已经设置成功SELECT IS_CDC_ENABLED , CASE WHEN IS_CDC_ENABLED = 0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述FROM SYS.DATABASESWHERE NAME = 'tableName'
同时,数据库的用户将会多了一个CDC用户
QQE2KU8MagaLRAAgGBoOEhIIGQAAFCQuZjrfvHPt13s1577fWac8/9+4wMxtpzz8dvbeZ3/daac/7WjJJNM0sfdeal86c2nsm9XDn+3PbuNcX//W3BI0dyfv/1iv/YveT+4rn3Fc78dcGMe9ZMvTfrjd8+0OZXei1GRUVFRUUFfvT/q5kYdSeBGRT5NdtSfGvQkOYBAPCGW10zLWnyvGljpo59JWFQ7z49Y55u/9ijf/zPB9r86t9/8VP1n4ZZKn1qCs9AyZrXAs0UdYvqRL3WjesEALZAvwQAYQF5A4CweCHvExV5FZ8u2pn/VsmqscUrx36yZsKnhXMq9qzyoGkAiGRcl3fFp4tqDq2+dGbTzYYd6NZedGvvzYYd578rOPzlsu0bp7vdOgBEMu7Ke/u6SbS13OAv8+2+rhoAAJGM6/JuOpbYcmklurnFp2d0ay9u/Kjp/KKmY4kXtncGeQOAe4Qg75DmvX18smZC45fPNB1LbDq/qLk+t+nM3MYDva9sua9u4/86lxtV88E/6clbc5oq+MlYKmUTJo0CgBlCk3dI6bIsl6waayzvJZNe0LXMkmaYKA3kDfCJu/IuXgnyBgBmuCvvwsyRduStvu/VS1EvcVMkqterqle8KvKrD4K2DgBcEbK81TqxI+/kxG66lumv/Qx6EHQNqVqljhwAAFe4673z0oY7KG/ZcJG5ZobARHXNBm2BvAEBCCd5m9FVSHmstQXyBsIF1+V9zYa8DZ51FXfaiidk/7G6iGa1gfk129KsWdMeAOAHd+e9VyXFGXvvaYM7OXEWRhjflgOAwLjb3VclxV38tOv1b0ZoyvtI1o89kLcM49tApOJ6jy9M67Nn3ctHP3uz9tvMpjNzG7568dSHD3699JdFsx5If/0Jt1sHgEjGC4f2dWly8cqxq5Lilkx6ITmx29KJT+fOfnbnquEeNA0AkQzcrwKAsIC8AUBYeJR30fq0wD9rlQQdMA91pE0vv5l5dcW3enN7eoRkJwD44a7rFK1Pa/jhk9tXt96+urW5oeDm2aWhKtw/O634VzObrLMiXTNdT3LGl5KgWg1a3NRpA4AKL7pOSBPmCtddsHZR/uqF+asXrluZvG5l8trMuf4/40bNe289rTquq6D+X+8q46wZQOTgkbzNpxetT2ttLPP/3b5Riq8U4oY8ciGTnFlITs5AxyY27R/ZVB4/bfwwdXFNN2v+o56uDJRpxr2ra1BXrigF8gbs47W8AzurPRzzVy9sbSzz3Zy3NP5D2OdSyckZ6JtxLRXDbn7Rt3F37KH3f60pb38rankE9dLqDObVqy5uXJVeQXUGkDdgGe7kvW5lsl/ed2j72MSWimFN5fGNu2MvFjzxddpdicP6azandob+FHU22YT3NikwM9n0HLumqg0uJQBgBu/krZaQprzXZM5tbSzz+W18aZVa21c2dz6//uE9C+96+aXu6uKaktaTtyJdU/8GItf17Dou2uD6YiYbAIQKd957Tebc21e34iuFPm3j03P8D9s+bdcVPnku+8HSOT/v8UwHvRaDClXhpQ28d1Dd6jUamG58CdC8cAStFgCCwp28Vyx5+/bVrfjSKnIhU6HthtKYusInz69/+GRWm4IZ93T4yyPq4sZCVeQMLKKZrplfEz2hambTK6V57QB5A5bhbuQ8c+G05oYCv7ZbqkY3lcc3lr3o1/aZlfdXp/9yzdR7H/mv+zWr1bvp1cymlqWBV9dz4EGdrdokgyJ6lxsACBWP5C38xuAGKXpaNXkdAXkDluGu64TRxuAG2vN/pajZ4BKjB8gbsAx0HQAQFpA3AAgLyBsAhAXkDQDCwqO8HYn39gHjUkAkw13vtx/vHQjIG4hkuJv3th/vDRPIAOCDu1VrNuO9FYCkgUiGuzXnDsZ7g+sGIhzu5G0/3jsQUDUQyXgnb7UvdSneG9ZvA4AP7ry3I/HegW2BpIGIhTt524z3VjekGVICAJEAdyPn9uO9ZXMBoQAgPNzNe9uM91ZEYmr6cACIELjr9Dbjvf1oBleDyIGIAro7AAgLyBsAhAXkDQDCAvIGAGHhUd42472Djp/ByjYgQuCuKzsS7x04H6bWrfFCF5A3IAzczXvbjPdWiFlTyebFDwBhDXer1pyK91YvblEIWHNlmwzyBgSCuzXnTsV7Bx4YrGBTP2+DvAFh4E7ejsR7ay5N1bwnVxSBoTVAJESL95bv1LZJ760o7shZAwBzuPPeNuO99R6z1YnqUnrfAkCYwp28HYn3VstVbxQNJsYAgeFu5NypeG/FsYF/hqE1QFS4m/d2an9vzYPAZ3IDnP8JAIAF3HVlR+K91R5bVo20GZcFAAGArgwAwgLyBgBhAXkDgLCAvAFAWHiUt4P7ewdFbyANBtgAAeCuE9uP9w510ktv0lux+gUmz4Cwg7t5b0f29w76VVBJGwPyBsIC7lat2Y/31lu7IuvIW1FQnWhQDwDwDHdrzu3He5vx3prZFN+arAcAuIU7eduP9zbjvdXGGKgdvDcQpogZ7y3rKFlPw8b36uqrAMgbCAu489729/cOSd56iTLIGwh/uJO3/Xhv8zfnsmnvDTfnQDjC3ci5/Xhv80Ni5p+9zTcBAPzA3by3/XhvR0bOZcPBc5A3EBZw103tx3vr3WkH3lob6NbYaRuUBQDegJ4KAMIC8gYAYQF5A4CwgLwBQFh4lLcH8d7m57o0Z9ENCsLAG8AP3PVFm/He6tUsmsrUW7KiGF3XHEUHeQPhAnfz3vbjvfUwo1W9b83I239dCNUwAHAJ7latObW/txpNHRp4eFnfySvqCXoDDwBM4G7NuSPx3mZuyNX26FWid14Kdw3eG+AN7uTtVLy3+qMZt2zNe+u1AgBs8U7eaoW4Gu+t/migcz2h6qldXVYvDwAwhDvv7VS8t/qj+uY88EDWkbdmDXr1m7mlBwDP4E7ejsR7G9xLB81s7MnV0gXvDXALdyPn7sV7G9xaa0par4iZagGAB7ib93Zkf29N1HfdMsgbEBru+qIj+3srMHgeVotTLW+9G3jzeQCACdAFAUBYQN4AICwgbwAQFpA3AAgLj/L2cn9vABAY7uRtf39vAAB8cDfv7cj+3k5NTTlSgwVjYF4NcATuVq3ZjPc2WJTCCsUSmlBLAYBluFtzbjPem0NVgLwBVnAnb0fivdXaUCxEDVxqJst3LGtTH6jrUdwjGEtRUUSvfuPFcGbOAgAUeCfvqDuRXYv39qF5l66pE708ssqFatajmTNoKZM1h3oWABAId97bfry3ui3FZUXWUqNaTkE/atZsbIZx65o2G7QF2gaM4U7eNuO9jd2dZrbARAvyVtejlydUeZsvBQCacDdybjPeW8+dBibq+VuDIgaJxt5b0+Xqta6ZxzgdFA4YwN28t3vx3gAQaXB37Xcj3hsAIhPu5A0AgFOAvAFAWEDeACAsIG8AEBYe5e1UvLfxpFHQOW0ACHe469MOxnuDvIEIh7t5b5vx3lHB8GeT5TsWhAUuF3HlVwAAz+Fu1ZpT+3uH5LplleABQAC4W3Nuf39vWcstay7tVKzuhGWegGBwJ2/78d6yvnNWfAxMBFUD4uGdvNUe0r39vYMOm+k9e4PIAZHgznvbjPcO1K3JoTV1OgCIAXfytr+/t6xzK65OAe8NiA13I+f29/eWQ5G3WuchnBgA8A13896OxHuD9wYAmcNVa47EeyuGxE0OpKvTASCsgd4MAMIC8gYAYQF5A4CwgLwBQFh4lLc38d5msgUdgQcAnuGuvzq7v3dQQaoXnxsUd0TeEsZSY6NUWyvV1NCqKlpeTnftoiUlJC8P5+eTvDxaUkJ37aLl5bSqSqqpkWprpcZGCWP7TQORBnfz3t7Ee/szax6rE42vAlJtLT1wgJaV0dJSUlBAsrNJRgZJScEzZ+JJk/Do0WjIENSnD+reHUVHow4d0LPPohdfRPHxOCEBjx+Pp0/H8+aR9HS8YQNJT8fz5uHp0/H48TghAcXHoxdfRM8+izp0QNHRqHt31KcPGjIEjx6NJ03CM2eSlBSSkUGys0lBAS0tpWVldP9+6eJFc/8zgOBwt2rN1f29Q/02cFmbn7t/9CN68CApLCQLFuCEBBQTg3r1IrNm4alT8axZJDWVZGWRnBxSVES3bqWff04rKqTqaunsWamhQWppMf6tDJBaWqSGBunsWXr8OK2ooJ9/TrduJYWFJCeHZGWR1FT8DxvQCy+gZ57BCQlkwQJSWEgphpSuX7fcLhC+cCdvR+K9fRjccgf9yv+vhLF04sSge+4hS5eW/uEPKDYWPfssHjGCpKSQoiJaVSXdvGlsBhOkGzfooUOkqIikpOARI1DXrig2Fk+YQDIy6Nat0okTcLcfCXAnb5vx3uZvzvW+lc6do3v2zPjNb/C0ad88+ihq3/7An/6E332X5Ob+9ec/l+rqHPpVvEa6dImWl5PcXDxzJho0CLVvjwYMwNOnk9Wr6WefSefPszYQcB4B4719GDxmK7jnn/+ZfPwxTkrCQ4ei6Gj00kt4ypRZbdrQXbv+8OMfm6wkHJFOn6a7dpHly/Hkyehvf0PR0XjoUJyURDZvlq5dY20d4ADceW9H9vc2fq72IV24QPLycELCxb/8BWdk0JISevSo4tk4cGhNb3xOGKSWFnr0KC0pwRkZqFs3PGIE2bBBqq1lbRdgHe7k7VS8d6AOAwUp1dSQDz448Kc/1Tz+eOr993f86U8NpGv+FkA86MGDZMkS9OKLaPBgsnq1dPIka4uAkOFO3o7Ee/vxK5YeOUIyMlCfPigujqxYIZ04EZjHoLjBxwhBqq4my5ejgQNR374kI4N+8w1riwCzcDfv7dT+3j5h0337SErKuT//+cuHHya5udK5c5o5DSoxmTMSkL7/nuTk4KFDUc+eZOFCun8/a4uAIHDXX23Ge/tU3evuu/GsWSgmBo8dSwoLpcuXZf0HaePRdb38nvwYnCLV1ZEPP8SJiSgmBs+eTT//nLVFgDZCdVOybRt+80301FN48mRaWio1NbG2SHCkmzdpaSmeNAm1a4ffeots387aIuAOBJE3WbcOxcTguXNpWZlEKWtzIg6JELp7N54zB3XtSvLyWJsD/A9hL2+yZg3q3Jm8957l1WP/+3eJjv85eorhhHT9OklPR9HRJCeHtS1A2MpbIoSsWoXatyeZmXYWCSSgbxeQmptJRgbq0IF88AFrWyKa8JO3dPs2ycpC7dqRlSsVC6etBYrb0TDI2wAJIbJiBW7XjixfLiHE2pxIJJzkLTU1kaVLUadOJDtb/a3lQHEzmgxV4dZOUFTI6tWoQweSkSHdusXalsiCa3n7p8cXTJ9e3r9/U8eO24cMcTxQXFOrescmrwKO/QQCQXJyUJcuJC1NamxkbUukwLu8pfp6kpKCunUj+fmB6erMlgPFLcg7qCd381cJb0heHnr2WZKaKl25wtoW8eFX3lJt7f6+fdHzz5PCQsVXzgaKh3pzLoO8bUMKClD37jg5Wbp0ibUtIsOjvKVbt8iCBahXr5LhwzUzOBsoDt6bFaS4GMXG4pQUm3MfgB7cyZts2oS6dCFbt8peBYrDszdbyJYtKDqalpSwNkRAOJK3VFuLR4wg8+f7U7wJFIebcx7A8+bhUaPC92U4fMKLvMmaNahXL3rwYGCiN4HiTt2cm/TwgB60shLFxsJyNwdhL2/pxAk0YADJzFR/5U2geEjylk1oG+RtB5KRgeLjpe++Y22ICDCWN87KQoMGSadOaX7rTaA4yJs3pJoaFB+P33+ftSFhDzN5S83NKD4eb9zoVIWWA8UNdGsmD8jbJfCGDWjQIOn2bdaGhDFs5E3370fR0VJNDZPWFZhUbEh/rM9JEKQTJ1CnTrSykrUh4QoDeZPsbJzIkQBA3pyDR42C8TZreC1vPGkSycryuFFjrlyqOrJ32c7CKZvXjf9o9ZjiVYmlueM+/zj5fM0XrE0D/geSkYGnTGFtRfjhnbzp2bPor3+le/d61qJJyjfPqj+9iraWB/5dPrM2970E1qYB/x+6Zw96/nkKu6mEgkfyplVVePRoPqMipitIREf7oYYP0LXC21c23L64rPnbCXWfdlw+4xnWpgF3INXXo1Gj6NGjrA0JG7yQN92/H7/2mgcNWWN37pCWg881nU9trs+5cfz1q9t+eyk/6rsP/sm/DLZy59esbQwn3H6NLB46FAbbTOK6vOkXX3A1kKZmZ/bg1qrYpvOLm+tzblSP88n7sTYPBC50//7ACYMaNN+gHHTPE80UR2q21rqilHqpv9636q80T0Tv7PTOXa+ULMt41Cj61VfGeQDZbXnT3bvxG2+42oR9tq16ubWq1y2fvL994+qOBy/lR0VFRd0bFRXf9jfJgzsOvO++uHY99IpHab0XXf2tpt7Md27zNVtr3bgtzQoDK7Emb4P0oOBx4+iePSYzW2tFgAuHiydAt2zBb73lXv1OseX9gfhwr1s/LGmuz7lRPd4n7z/f9aOFgzuXLxt+fOMba8bE5M8YqFfcpAhlre7iiLwN6jHfuoPy1msrJNmbAU+ZQs29WR3k7TB03z787rsuVe4sJZn90ZEXmn5YHCjvHUkTDmT1q949p3rbjOOlk/++bGTQejTdV0gCU9zx2qxZr5KgNQQqVk+3Iclb83oRWLleW3q/jz9x3e9+1+VnP9OsNtBU9Ue9+jXN08yjVw9XuGIW3bcPTZ3qRs1uUPxeH3SkZ/MPi5vrc25Wj7+248FL+VHR/+dnG6c+V/3x/OrSecdzR+xbGeQRw4zwjAXmbM2htm4yp1rein8NKjS+TBhn0zNDluWi3/+eVlQYGK9Zj5mag+bhWdg+nDdOqqlBcXGOV+seH6a9JFfHosvpzfU5Td9O2H5anrb2eFTUL2YO6ndwxYiytKGz+vw2fXTs5iPNejUE7dma2TRTnKo51NbdlrfahZqpx6QIUf/+0unTiub0jNE8wUAjzbfOubZlN+SNOnRQvH6cc4qyRh4u6X3pWFJzfc6bed89P785aas8NoUc/uxI1doxU15oO3Rc3bQs+f6E24t3XlQXd0Q2mlir2VrrITlbRR6T3ttCPSblLbW0oM6dTZ6d+qNBzcZ5Ik7e+JVX6LFjztb