用于审计触发器或 CDC [英] For Auditing Triggers or CDC

问题描述

我想看看其他人是否在使用 SQL Server 2008 Change Data Capture，如果是，您喜欢它吗?我们目前将 APEXSQL 审计触发器用于我们的审计目的，这似乎工作得很好，但这意味着我们必须向所有审计"表添加触发器.

我读过的一些文章指出了一些事情，比如在更改架构时必须创建一个新的捕获表然后删除旧的，但就一般维护而言，它似乎相当简单.

非常感谢任何评论/输入.

--S

解决方案

系统有多忙，审计的最终目标是什么；是在短时间内跟踪更改，还是长时间审核更改?我对 CDC 的最大问题之一是它利用日志读取器和 SQL 代理作业来捕获更改，因此繁忙的系统可能会落后到永远无法赶上的地步，除非您关闭 CDC，否则会导致最坏的情况完整的事务日志，或者充其量延迟截断导致日志大小增长.如果您的意图是进行真正的审计，CDC 不是为此而构建的，它更多地用于同步更改而不是长期审计，除非您设置作业以将数据拉入审计表，就像使用触发式解决方案一样.

您没有在此处提及新的服务器审核规范，这将是另一种查看方式，但请记住，服务器审核规范用于通过包含进行审核.这是我仍然在我的 SQL Server 2008 Ent 数据库中使用旧的久经考验的触发器和审计表方法的原因之一，它仍然是最简单的解决方案，直到较新的功能成为产品中的 v1.0 功能.

I wanted to see if others are using SQL Server 2008 Change Data Capture and if so how do you like it? We currently use APEXSQL Audit Triggers for our auditing purposes which seems to work pretty well, but means we have to add triggers to all of our "audited" tables.

Some of the articles I have read have pointed out things like having to create a new capture table when you change a schema then drop the old one, but as far as the general maintenance is concerned it seems to be fairly straight forward.

Any comments /input is greatly appreciated.

--S

解决方案

How busy is the system and what is the end goal for the Auditing; tracking changes in a short period of time, or auditing changes for a long time? One of the biggest problems I have with CDC is that it utilizes the log reader and SQL Agent jobs to capture changes, so a busy system can get behind to the point that it will never catch up unless you turn off CDC, leading to at worst a full transaction log, or at best delayed truncation causing the log to grow in size. If your intent is to do real auditing CDC is not built for that, its more for synchronizing changes than it is for auditing for a long term, unless you setup jobs to pull the data over into audit tables like you would with a triggered solution.

You don't mention the new Server Audit Specifications here, which would be another option to look at, but keep in mind that Server Audit Specifications are used for auditing by inclusion. This is one of the reasons that I still use the old tried and true triggers and audit tables method in my SQL Server 2008 Ent databases, its still the easiest solution until the newer features get past being v1.0 features in the product.

这篇关于用于审计触发器或 CDC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！