What advise the means of auditing data changes in MSSQL?Asked by paul vaden
What advise means for auditing data changes in MSSQL (you need to save the change of all fields), where a bunch of mssql + NHibernate.
From what I found is:
1. Hang up triggers in the database, write history to tables with history.
2. In NHibernate itself, hang up handlers in Interceptors or EventListeners and write tables with history.
3. Use feature 2008 MSSQL - Change Data Capture. Here the question is whether it should be used for this, somewhere I read “The main scenario in which CDC is supposed to be used is“ large ”ETL (extraction, transformation, loading) applications that asynchronously transfer data from the OLTP system to the data warehouse. "
Maybe there are ready-made simple solutions.
The second option - the same eggs, only in profile. I would recommend to choose the first of these two (at the database level, it is better to organize temporality).
The third is a good option. The main scenario is that on which such a scheme can be applied more often and best of all. In your case, this thing will also be useful.
I also advise you to pay attention to link, which examines the temporality of databases and the proposals of development firms to ensure this temporality in the DBMS.
Here is link discussion of temporality (chronology of changes) regarding MSSQL. I think the topic can be useful.
Additionally, see the AutoAudit project on the codeplex - maybe it will suit you.
the difference between options 1.3 and 2 is that you will see history 1 and 3 at the physical DB level (DB tables), and at the logical level of applied C # Objects. However, you will have to log either on the client or on the application server - it may become critical to the memory by the number of records processed (if there are more than 10 thousand changes in the transaction). Again, if you have stored procedures in the database, then in the 2nd version you will miss out on auditing their changes