变更数据捕获或变更跟踪 - 与传统审计跟踪表相同吗？
在我深入研究 Microsoft 文档的深渊之前，我想知道有变更数据捕获和变更跟踪经验的人是否知道其中之一或两者是否可以用来取代传统的...
“审计跟踪表副本的真实 table'（原始表的所有字段， 加上日期/时间、用户 ID 和 DML 动作字段）插入到 触发器”
...数据库表审计跟踪的设置，其中触发器填充审计跟踪表（这都是手动工作）。MSDN
概述文档在较高级别解释了更改数据捕获和更改跟踪是什么，但它不是对我来说还不够清楚，也没有直接说明，这些工具可以用来取代我们经常制作的传统审计跟踪表，
有使用变更数据捕获和变更跟踪经验的人可以帮我节省很多吗？时间，或确认我花时间寻找正确的工具？我们的审计跟踪的关键部分是捕获表字段的所有更改（插入、更新、删除）、发生时间以及执行者。更改通常通过审计跟踪报告按时间顺序提供给最终用户，这是另一个问题...更改数据捕获或更改跟踪是解决方案，我假设可以像普通表中的数据一样查询这些数据？
编辑：我需要永久的审计跟踪，无论时间如何。我发现更改数据捕获与事务日志有关，所以这对我来说听起来很有限。
Before I delve into the abyss of Microsoft documentation any deeper, I'd like to know if someone experienced with Change Data Capture and Change Tracking know if one or both of these can be used to replace the traditional ...
"Audit trail table copy of the 'real
table' (all of the fields of the original table,
plus date/time, user ID, and DML
action field) inserted into by
Triggers"
... setup for a database table audit trail, where the trigger populates the audit trail table (which is all manual work).
The MSDN overview documentation explains at a high level what Change Data Capture and Change Tracking are, but it isn't clear enough to me, and doesn't state outright, that these tools can be used to replace the traditional audit trail tables we've made so often.
Can someone with any experience using Change Data Capture and Change Tracking save me a lot of time, or confirm that I am spending time looking at the right tool? The critical part of our audit trail is capturing all changes to a table's fields (on INSERT, UPDATE, DELETE), when it happened, and who did it. These changes are commonly provided to an end user chronologically via an audit trail report. Which is another question ... Change Data Capture or Change Tracking is the solution, I'd assume that this data can be queried just like data from a normal table?
EDIT: I need a permanent audit trail, irregardless of time. I see that Change Data Capture has to do with the transaction logs, so this sounds finite to me.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为根据您的情况，您仍然需要审核表。查看 BOL，似乎会自动创建并安排每天凌晨 2 点运行的清理作业。来自博尔：
听起来它绝对没有达到您想要的效果。我认为这不会达到任何审计表格的人所希望的效果。而且，除了它自己的五个默认字段（我找不到它们是什么）之外，将数据表中没有的任何字段添加到审核日志中，即使不是不可能，也会很困难。对于查询或用于回滚特定的不良更改非常有用。或者也许我只是不理解这个过程，因为 BOL 在这个主题上写得很差，它肯定没有回答我用这个显然考虑不周的过程取代我的审计时所担心的任何问题。
I think you still need audit tables in your circumstances. Looking in BOL it appears that a cleanup job is automatically created and ascheduled that runs every day at 2 am. From BOL:
That sounds like it definetely doesn't do what you want. I can't think that would do what anyone who audits tables woudl want. It also appears that it would be difficult if not impossible to add any fields not in the data table to the audit log other than it's own five default fields (I couldn't find what they were.) It also appears that the data would not be very useful to query or to use to rollback a specific bad change. OR maybe I just don;t understand the process because BOL is pretty poorly written on this subject, it certainly didn't answer any of the concerns I would have in replacing my auditing with this apparently poorly thought out process.