审核 SQL Server 数据更改 [英] Auditing SQL Server data changes

问题描述

我正在考虑更改 SQL Server 2005 数据库的审核流程，并且我在 SQL Server 2008 中遇到了变更数据捕获.

I'm looking at changing our Auditing process for our SQL Server 2005 databases and I came across Change Data Capture in SQL Server 2008.

这看起来是个好主意，我很想尝试一下，但在此之前有人在商业环境中使用过它，您有什么想法?

This looks like a good idea, and I'm tempted to try it, but before I do has anyone used it in a commercial environment and what are your thoughts?

我在 MS 帮助中阅读有关 CDC 的内容时注意到，它说审计数据通常会保留几天.这在这里是不可能的，我想无限期地保留数据，有人知道这种方法有问题吗?

I noticed when I was reading about CDC in the MS help, it said that audit data would usually be kept for a couple of days. That's not possible here, I'd like to keep the data indefinitely, does anyone know of problems with this approach?

如果由于我不知道的原因这不是一个好的解决方案，您是否有任何其他解决方案来审核数据更改.我很想使用一个可以在我想要的任何桌子上全面实施的系统.

If this isn't a good solution for reasons I'm unaware of, have you any other solutions for auditing of data changes. I'm keen to use a system that can be implemented across the board on any tables I desire.

我关注基本信息:任何更改的之前、之后、谁、何时"信息.

I'm after the basic: "Before, After, Who By, When" information for any changes.

推荐答案

在我看来，CDC 应该只是达到目的的一种手段.我过去曾实施过审计跟踪解决方案，其中涉及到触发器的使用.对于高度事务性的数据库，这会非常混乱且性能密集.

The CDC should is just a means to an end in my opinion. I have implemented audit trail solutions in the past and they have involved the use of Triggers. This got to be very messy and performance intensive for highly transactional databases.

CDC 为您提供的是在不使用触发器的情况下记录审计数据的能力，但您仍然需要一种方法将这些数据放入永久表中.这可以通过为每个要审计的表创建一个镜像表或一个跟踪所有表的所有更改的单个表来完成(我已经完成了后者).

What the CDC gives you is the ability to log the audit data without the use of triggers, but you still need a means to take that data into a permanent table. This can be done with a mirror table for each table to be audited or a single table that tracks all the changes to all the tables (I have done the latter).

以下是一些链接，其中包含有关如何使用触发器完成操作的其他信息:
SQL 审计跟踪
sql-server-history-table-populate-through-sp-或触发

这是一个使用 LINQ 的开源审计跟踪解决方案:DoddleAudit

Here's an open source audit tracking solution that uses LINQ: DoddleAudit

这篇关于审核 SQL Server 数据更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！