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 first option - the easiest. Realization of temporality due to explicit data duplication. The problems here are the sampling of data for the period (in fact, the receipt of changes). Most often it is used by developers, as it is usually done for their specific goals with their business logic when selecting changes.
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.
neil clench
I never developed on .NET / Java, but I recently saw a recommendation in my smart book to use DbDeploy, but I don’t know how much it will help solve your problem.
depends on the load, if there is a lot of data and changes and critical speed, then only the 3rd CDC version can work without the load on the main database (on a dedicated server), but requires MS-SQL Enterprise Edition
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
Custom drop-down lists (select box) in Chrome and Opera :: Did you get spammed & quot; Is me Judy from (habrahabr.ru) & quot ;? :: Where to find & quot; wall of shame & quot ;? :: Flowplayer and files with Russian names? :: Are there normal translations of the book Thinking in Java?
Leave Repply for What advise the means of auditing data changes in MSSQL?
Useful Links