更改数据捕获或更改跟踪-与传统审核跟踪表相同吗？ [英] Change Data Capture or Change Tracking - Same as Traditional Audit Trail Table?

问题描述

在我深入研究Microsoft文档的深渊之前，我想知道是否有使用Change Data Capture和Change Tracking的人知道这两种方法中的一种还是两种可以用来代替传统的...

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 ...

真实
表的审计跟踪表副本（原始表的所有字段，
加日期/时间，用户ID和DML
操作字段）插入到
触发器中。

"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"

。

MSDN概述文档在较高级别上说明了什么。变更数据捕获和变更跟踪是可以的，但是对我来说还不够清楚，也不能直截了当地指出，这些工具可以用来代替我们经常制作的传统审计跟踪表。

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.

具有使用变更数据捕获和变更跟踪的任何经验的人可以救我吗很多时间，还是确认我花时间在寻找正确的工具？审核跟踪的关键部分是捕获表字段的所有更改（在INSERT，UPDATE，DELETE上），发生的时间以及执行的人。这些更改通常通过审核跟踪报告按时间顺序提供给最终用户。这是另一个问题...更改数据捕获或更改跟踪是解决方案，我认为可以像查询普通表中的数据一样查询此数据？

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?

编辑：无论何时何地，我都需要一个永久的审核记录。我发现Change Data Capture与事务日志有关，所以这对我来说是有限的。

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.

推荐答案

我认为您仍然在您的情况下需要审核表。在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:

清理作业每天凌晨2点运行。
它将更改表项保留
4320分钟或3天，使用单个
delete语句删除
最多5000条条目。

The cleanup job runs daily at 2 A.M. It retains change table entries for 4320 minutes or 3 days, removing a maximum of 5000 entries with a single delete statement.

这听起来像是行不通。我认为这不会满足审核表woudl的任何人的需求。似乎也很难将数据表中未包含的任何字段添加到审核日志中，除非它具有自己的五个默认字段（我找不到它们是什么。），而且似乎数据也不会查询或用于回滚特定的重大更改非常有用。或者也许我只是不理解该流程，因为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.

这篇关于更改数据捕获或更改跟踪-与传统审核跟踪表相同吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！