审计SQL Server数据更改

Question

我正在考虑为我们的SQL Server 2005数据库更改审核流程，我发现了SQL Server 2008中的Change Data Capture。

这看起来是一个好主意，我很想尝试一下，但在我开始之前，有没有人在商业环境中使用过它，并且你们的想法是什么？

当我阅读MS帮助中关于CDC的内容时，我注意到它说审核数据通常会保留几天。这里不可能这样做，我想永久保存数据，有没有人知道这种方法存在问题？

如果由于我不知道的原因这不是一个好的解决方案，你们有没有其他用于数据更改审核的解决方案。我渴望使用可以在我想要的任何表上实施的系统。

我想要基本的：“之前，之后，谁，何时”等任何更改信息。

- MrEdmundo

Answer 1

CDC在我看来只是达到目的的一种手段。过去，我曾实现过审核跟踪解决方案，并涉及触发器的使用。这变得非常混乱且对高事务性数据库的性能要求很高。

CDC提供了记录审核数据而无需使用触发器的能力，但仍需要一种方式将该数据传递到永久表中。这可以通过为每个要审核的表创建一个镜像表或跟踪所有表更改的单个表来完成（我已经采用了后者）。

以下是一些链接，提供有关如何使用触发器进行审核的其他信息：
SQL Audit Trail
sql-server-history-table-populate-through-sp-or-trigger 这里有一个使用LINQ的开源审核跟踪解决方案：DoddleAudit

- Jose Basilio

---

我看到了，我刚刚又读了一些CDC的内容，并理解了你的意思。它似乎从异步传输中受益匪浅。我很喜欢你用单个表来跟踪所有更改的想法，但是在“高度事务性数据库”中，这个表可能会很快变得非常庞大。 - MrEdmundo

嘿，我终于抽出时间看了一下你说的内容。我想知道你是如何跟踪在你的系统中做出更改的人员的？原则上 CDC 似乎相当容易设置，并提供了一些不错的功能。 - MrEdmundo

在我的设置中，每个表都有4个额外的列：CreatedDate、CreatedBy、UpdatedDate和UpdatedBy。我在SQL更新语句中填充最后2列的用户名/日期。我使用它来确定谁更改了什么。 - Jose Basilio

谢谢你的帮助。我现在使用触发器。我阅读的所有CDC内容都说它可以用于审计，尽管这不是其设计目的。我担心使用它不合适。最终，我实现了一个创建类似CDC的审计数据的系统，尽管是通过使用触发器。我遵循了4种操作类型的类似系统，它看起来还不错。 - MrEdmundo

请您批准我的答案吗？ - Jose Basilio

非常小心使用默认的CDC，它会在短短几天内过期数据，这对于审计目的是不可接受的。 - HLGEM

Answer 2

虽然有点晚，但希望对其他读者有帮助...

以下是几种不同的审计技术及其优缺点。没有适合所有情况的“正确”解决方案。它取决于要求和被审计的系统。

触发器

• 优点：相对容易实现，具有很大的灵活性，可以完全控制审计内容和审计数据的存储方式
• 缺点：当你有很多表格甚至更多的触发器时会变得混乱。维护可能会很复杂，除非有一些第三方工具来帮助。此外，根据数据库的不同，它可能会对性能产生影响。

CDC（更改数据捕获）

• 优点：非常容易实现，本地支持
• 缺点：仅在企业版中提供，不太健壮 - 如果更改架构，您的数据将丢失。我不建议用它来保留长期的审计跟踪。

跟踪

• 优点：可以灵活地审计内容。甚至可以审计select语句。
• 缺点：您需要创建一个单独的应用程序，以便解析跟踪文件并从中收集有用的信息。

读取事务日志

• 优点：只需将数据库设置为完整恢复模式即可存储所有信息
• 缺点：您需要第三方日志读取器才能有效地阅读此信息

我使用过ApexSQL的几个审计工具，但也有来自Idera（合规管理器）和Krell软件（omni audit）的好工具。

ApexSQL Audit - 基于触发器的审计工具。生成和管理审计触发器

ApexSQL Log - 允许通过读取事务日志进行审计

- Anthony Horovitz

Answer 3

在SQL Server 2008中，您可以使用“审计”功能并将数据存储在文件、应用程序日志或系统日志中。了解更多信息，请访问：http://msdn.microsoft.com/en-us/library/cc280386.aspx。

- Christopher

Answer 4

我正在使用this脚本，您可以定义要进行审计的表或在数据库中定义所有表。此外，还有用于查看历史记录的单个记录的tvf。请查看链接以获取详细信息。

- Ice_Drop

Answer 5

您可以编辑删除捕获的更改的 Sql Server 作业，该作业在两天后自动清除。您也可以根据需要延长此清理时间，或使其永久化。

- Akhilesh