问题描述
我们在 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所说).我只遇到过一次这个问题，所以它不是那么烦人.
无论如何，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
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.
Anyway, CDC is very useful mechanism which helps me track all changes on database.
这篇关于SQL Server 2008 更改数据捕获与审计跟踪中的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！