SQL Server 2008 更改数据捕获与审计跟踪中的触发器 [英] SQL Server 2008 change data capture vs triggers in audit trail

问题描述

我们在 Web 应用程序上使用触发器实现了审计跟踪.这些触发日志字段级别更新.因此，在一个表中，如果您更改了 5 个列值，我们在审计跟踪中有 5 条记录，每列有一个具有旧值和新值.

最近我们升级到 SQL Server 2008.我一直在考虑使用新的变更数据捕获功能，因为它提供了非常简洁的行级更新，而且工作量非常少，而且非常容易实现.只是想从一直使用变更跟踪的人那里获得一些意见，以解决任何警告或任何其他实际有用的信息.

任何建议都会有所帮助.

- http://technet.microsoft.com/en-us/magazine/2008.11.sql.aspx?pr=blog

解决方案

我在我的 WPF 应用程序中使用 CDC.效果很好，但我发现了三个问题:

• 您需要经常备份更改表(我使用 MERGE 语句将记录添加到历史表中).因为我发现记录只保留在更改表中大约 2-3 天.不要忘记备份 cdc.lsn_time_mapping 表.
• 您无法在启用 CDC 的情况下截断表.
• 禁用cdc并重新启用存在问题(应该在新的服务包中解决，正如MS所说).我只遇到过一次这个问题，所以它不是那么烦人.<块引用>

  http://blogs.technet.com/b/claudia_silva/archive/2010/06/04/cdc-cdc-hangs-when-i-try-to-disable-it.aspx

无论如何，CDC 是一个非常有用的机制，它可以帮助我跟踪数据库上的所有更改.

We have audit trail implemented using triggers on our web application. Those triggers log field level updates. So, in a table, if you have 5 column values changed, we have 5 records in the audit trail one for each column with old value and the new value.

Recently we have upgraded to SQL Server 2008. I have been thinking of using the new change data capture feature as it gives a very neat row level update with very less efforts and it's super-easy to implement. Just wanted to get some opinions from people who have been using change tracking for any caveat or any other real-world useful info on this.

Any advice would be helpful.

Edit :- http://technet.microsoft.com/en-us/magazine/2008.11.sql.aspx?pr=blog

解决方案

I use CDC in my WPF app. Works very well but I've discovered three problems:

• You need to back-up change tables quite often (I use MERGE statement to add records to historical tables). Because record stays in change table only for about 2-3 days as I found out. Don't forget to backup cdc.lsn_time_mapping table.
• You can't truncate tables with CDC enabled.
• There is a problem with disabling cdc and reenabling (should be solved in new service pack as MS said). I've got this problem only once so it's not so annoying.

  http://blogs.technet.com/b/claudia_silva/archive/2010/06/04/cdc-cdc-hangs-when-i-try-to-disable-it.aspx

Anyway, CDC is very useful mechanism which helps me track all changes on database.

这篇关于SQL Server 2008 更改数据捕获与审计跟踪中的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！