记录 SQL Server 2008 中给定数据库的所有表中插入/更新/删除的行
在 SQL Server 2008 中跟踪/记录给定数据库的所有表中插入/更新/删除行的最佳方法是什么？
或者SQL Server 2008中有更好的“审核”功能吗？
Whats the best way to track/Log inserted/updated/deleted rows in all tables for a given database in SQL Server 2008?
Or is there a better "Audit" feature in SQL Server 2008?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
简短的回答是，没有一种解决方案适合所有情况。这取决于系统和要求，但这里有几种不同的方法。
DML 触发器
相对容易实现，因为您必须编写一个适用于一个表的触发器，然后将其应用到其他表。
缺点是，当您有很多表和更多触发器时，它可能会变得混乱。管理 200 个表的 600 个触发器（每个表插入、更新和删除触发器）并不是一件容易的事。
此外，它可能会导致性能影响。
在 SQL Server 中创建审核触发器
使用触发器记录对数据库表的更改
更改数据捕获< /strong>
非常容易实现，本机支持，但仅限企业版，这可能会花费很多美元;)。另一个缺点是 CDC 仍然没有达到应有的水平。例如，如果您更改架构，历史数据就会丢失。
事务日志分析
这样做的最大优点是，您所需要做的就是将数据库置于完全恢复模式，所有信息都将存储在事务日志中
但是，如果您想正确执行此操作，则需要第三方日志阅读器，因为本机不支持此功能。
读取 SQL Server 2008 中的日志文件 (*.LDF)< /a>
SQL Server事务日志资源管理器/分析器
如果你想实现这个我会建议您尝试一些现有的第三方工具。我使用了 ApexSQL 中的几个工具，但 Idera 和 Netwrix
ApexSQL 日志 – 通过读取事务日志进行审核
ApexSQL Comply – 在后台使用跟踪，然后解析这些跟踪并将结果存储在中央数据库中。
免责声明：我不隶属于上述任何公司。
Short answer is that there is no one single solution fits all. It depends on the system but and requirements but here are couple different approaches.
DML Triggers
Relatively easy to implement, because you have to write one that works well for one table and then apply it to other tables.
Downside is that it can get messy when you have a lot of tables and even more triggers. Managing 600 triggers for 200 tables (insert, update and delete trigger per table) is not an easy task.
Also, it might cause a performance impact.
Creating audit triggers in SQL Server
Log changes to database table with trigger
Change Data Capture
Very easy to implement, natively supported but only in enterprise edition which can cost a lot of $ ;). Another disadvantage is that CDC is still not as evolved as it should be. For example, if you change your schema, history data is lost.
Transaction log analysis
Biggest advantage of this is that all you need to do is to put the database in full recovery mode and all info will be stored in transaction log
However, if you want to do this correctly you’ll need a third party log reader because this is not natively supported.
Read the log file (*.LDF) in SQL Server 2008
SQL Server Transaction Log Explorer/Analyzer
If you want to implement this I’d recommend you try out some of the third party tools that exist out there. I worked with couple tools from ApexSQL but there are also good tools from Idera and Netwrix
ApexSQL Log – auditing by reading transaction log
ApexSQL Comply – uses traces in the background and then parses those traces and stores results in central database.
Disclaimer: I’m not affiliated with any of the companies mentioned above.
更改数据捕获旨在执行您想要的操作，但它需要每个桌子是单独设置的，因此根据您拥有的桌子数量，可能需要一些后勤工作。默认情况下，它只会将数据在捕获表中存储几天，因此您可能需要 SSIS 包来将其取出并存储更长时间。
Change Data Capture is designed to do what you want, but it requires each table be set up individually, so depending on the number of tables you have, there may be some logistics to it. It will also only store the data in capture tables for a couple of days by default, so you may need an SSIS package to pull it out and store for longer periods.
我不记得是否已经有一些工具可以实现此目的，但您始终可以使用触发器（然后您将可以访问具有更改行的临时表 - INSERTED 和 DELETED）。不幸的是，如果您想跟踪所有表，这可能是一项艰巨的工作。我相信应该有一些更简单的解决方案，但不要记住我所说的。
编辑。
也许这会有所帮助：
--变更跟踪
http://msdn.microsoft.com/en-us/library/cc280462.aspx
I don't remember whether there is already some tool for this, but you could always use triggers (then you will have access for temporal tables with changed rows- INSERTED and DELETED). Unfortunately, it could be quite a work to do if you would like to track all tables. I believe that there should be some simpler solution, but do not remember as I said.
EDIT.
Maybe this could be helpful:
--Change tracking
http://msdn.microsoft.com/en-us/library/cc280462.aspx
http://msdn.microsoft.com/en-us/library/cc280386.aspx
这允许您在数据库级别进行审计；它可能足以也可能不足以满足业务需求，因为如果没有将它们粘合在一起的逻辑，数据库记录通常没有多大意义。例如，知道用户 x 将一条记录插入到“time_booked”表中，并使用“projects”、“users”、“time_status”表的外键，如果没有 SQL 查询来粘合这 4 个表，则可能没有多大意义一起。
您可能还需要让每个数据库用户使用自己的用户 ID 连接 - 这对于集成安全性和客户端应用程序来说没问题，但可能不适用于使用连接池的网站。
http://msdn.microsoft.com/en-us/library/cc280386.aspx
This allows you to do audits at the database level; it may or may not be enough to meet the business requirements, as database records usually don't make all that much sense without the logic to glue them together. For instance, knowing that user x inserted a record into the "time_booked" table with a foreign key to the "projects", "users", "time_status" tables may not make all that much sense without the SQL query to glue those 4 tables together.
You may also need to have each database user connect with their own user ID - this is fine with integrated security and a client app, but probably won't work with a website using a connection pool.
sql server日志是不可能这样分析的。有一些第三方工具可用于读取日志，但据我所知，您无法查询它们的统计信息等。如果您需要此类信息，则必须创建某种审核以在单独的表中捕获所有这些事件。您可以使用“DDL 触发器”。
The sql server logs are not possible to analyze just like that. There are some 3rd party tools available to read the logs but as far as I know you can't query them for statistics and such. If you need this kind of info you'll have to create some sort of auditing to capture all these events in separate tables. You can use "DDL triggers".