SQL Server 2008更改数据捕获与审核跟踪中的触发器 [英] SQL Server 2008 change data capture vs triggers in audit trail
问题描述
我们在Web应用程序上使用触发器实现了审计跟踪。这些触发器记录字段级别的更新。因此，在一个表中，如果您更改了5个列值，那么审计跟踪中将有5条记录，每条记录分别具有旧值和新值。
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.
最近我们已经升级到SQL Server2008。我一直在考虑使用新的变更数据捕获功能，因为它以较少的工作量就可以提供非常整齐的行级更新，并且实现起来非常容易。只是想从一直使用变更跟踪进行任何警告或任何其他实际有用信息的人那里获得一些意见。
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.
编辑：- http://technet.microsoft.com/zh-cn/magazine/2008.11.sql.aspx?pr=blog
推荐答案
我在WPF应用程序中使用CDC。效果很好，但是我发现了三个问题：
I use CDC in my WPF app. Works very well but I've discovered three problems:
- 您需要经常备份变更表（我使用
MERGE语句将记录添加到历史表中）。因为我发现记录仅在更改表中保留大约2-3天。不要忘记备份 cdc.lsn_time_mapping 表。 - 不能在启用CDC的情况下截断表。
- 禁用cdc并重新启用存在问题（如MS所说，应在新的Service Pack中解决）。我只有一次这个问题，所以不是很烦。
- You need to back-up change tables quite often (I use
MERGEstatement 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.
无论如何，CDC是非常有用的机制，可帮助我跟踪数据库上的所有更改。
Anyway, CDC is very useful mechanism which helps me track all changes on database.
这篇关于SQL Server 2008更改数据捕获与审核跟踪中的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
