I have a .net application that uses EF along with mainly SQL Server 2008 but a couple of customers use 2005. In the database there are a few hundred tables. I need to audit a selection of these tables to record all data inserts/updates/deletes. I was thinking of having copies of these tables to record all the changes along with the datetime and username, then have a trigger on the actual table to insert the data changes to the relevant audit table. Is this the best way of going about this? Is a trigger the best way or should I use the EF instead? I was thinking of having just the one audit table but I reckon it could get out of hand very quickly.
我有一个使用EF的.net应用程序,主要是SQL Server 2008,但有几个客户使用2005.在数据库中有几百个表。我需要审核这些表的选择以记录所有数据插入/更新/删除。我想有这些表格,以记录所有与日期时间和用户名的更改的副本,然后对实际表的触发器插入数据更改的相关审计表。这是解决这个问题的最好方法吗?触发器是最佳方式还是我应该使用EF?我想的只有一张审核表,但我认为它可能会很快失控。
3 个解决方案
#1
2
Auditing a SQL Server is not an easy task, especially when you need to support older versions of SQL Server.
审核SQL Server并非易事,尤其是当您需要支持旧版本的SQL Server时。
-
Track changes with your DbContext. This will add overhead to your application and (as you already noted) will become a heck of a nightmare easily (key-value doesn't scale, structure changes for history tables need to be adjusted (how do you reflect a dropped column for example)
使用DbContext跟踪更改。这会增加你的应用程序的开销,并且(正如你已经注意到的)将很容易成为一个噩梦(键值不能扩展,历史表的结构更改需要调整(例如,如何反映已删除的列) )
-
CDC - if your SQL server supports CDC this is a nice option and put a lower impact on your application
CDC - 如果您的SQL服务器支持CDC,这是一个不错的选择,对您的应用程序产生的影响较小
-
Third party options like Audit SQL Server You would need to investigate if they do what you need
审计SQL Server等第三方选项您需要调查他们是否满足您的需求
Edit: CDC is not available in SQL 2005
编辑:CDC在SQL 2005中不可用
#2
1
Also check this: http://doddleaudit.codeplex.com/
另请查看:http://doddleaudit.codeplex.com/
We're using it to audit db changes in our product. You can also modify it easily to suit your needs.
我们正在使用它来审核我们产品中的数据库更改。您也可以轻松修改它以满足您的需求。
#3
0
Starting from SQL Server 2008, you got some built-in functionalities to do, I think, what you want.
从SQL Server 2008开始,您可以使用一些内置功能来实现您想要的功能。
#1
2
Auditing a SQL Server is not an easy task, especially when you need to support older versions of SQL Server.
审核SQL Server并非易事,尤其是当您需要支持旧版本的SQL Server时。
-
Track changes with your DbContext. This will add overhead to your application and (as you already noted) will become a heck of a nightmare easily (key-value doesn't scale, structure changes for history tables need to be adjusted (how do you reflect a dropped column for example)
使用DbContext跟踪更改。这会增加你的应用程序的开销,并且(正如你已经注意到的)将很容易成为一个噩梦(键值不能扩展,历史表的结构更改需要调整(例如,如何反映已删除的列) )
-
CDC - if your SQL server supports CDC this is a nice option and put a lower impact on your application
CDC - 如果您的SQL服务器支持CDC,这是一个不错的选择,对您的应用程序产生的影响较小
-
Third party options like Audit SQL Server You would need to investigate if they do what you need
审计SQL Server等第三方选项您需要调查他们是否满足您的需求
Edit: CDC is not available in SQL 2005
编辑:CDC在SQL 2005中不可用
#2
1
Also check this: http://doddleaudit.codeplex.com/
另请查看:http://doddleaudit.codeplex.com/
We're using it to audit db changes in our product. You can also modify it easily to suit your needs.
我们正在使用它来审核我们产品中的数据库更改。您也可以轻松修改它以满足您的需求。
#3
0
Starting from SQL Server 2008, you got some built-in functionalities to do, I think, what you want.
从SQL Server 2008开始,您可以使用一些内置功能来实现您想要的功能。