What means of audit of changes of the data in MSSQL will advise?

Question

Database

What tools would you recommend for auditing data changes in MSSQL (you need to save changes to all fields), where is the mssql + NHibernate bundle.
From what I found, this is:
1. Hang up a history record in tables with history on triggers in the database.
2. In NHibernate itself, hang handlers in Interceptors or EventListeners and write to history tables.
3. Use the 2008 MSSQL feature - Change Data Capture. Here the question is whether it is worth using it for this, I read somewhere “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.

Answer 1

The first option is the easiest. Implementation of temporality due to explicit duplication of data. Problems at the same time - data sampling for the period (actually, receiving changes). Most often, it is used by developers, as it is usually done for their specific purposes with their own business logic when selecting changes.
The second option is the same eggs, only in profile. I would still recommend choosing the first of these two (it is still better to organize temporality at the database level).
The third one is a good one. The main scenario is where such a scheme can be applied most often and best. In your case, this thing will also be useful.
I also advise you to pay attention to the article, which considers the temporality of databases and the proposals of developers to ensure this same temporality in the DBMS.
Here is an example of a discussion of temporality (chronology of changes) regarding MSSQL specifically. I think the topic can be useful.

Answer 2

I have never developed on .NET/Java, but in one smart book I recently saw a recommendation to use DbDeploy , but I don’t know how this will help solve your problem.

Answer 3

depends on the load, if there is a lot of data and changes and speed is critical, then only the 3rd CDC option - can work without load on the main database (on a dedicated server), but requires MS-SQL Enterprise Edition
additionally see the AutoAudit project on codeplex - maybe will suit you.
The difference between 1.3 and 2 options is that 1 and 3 you will see the history at the physical level of the database (database tables), in the 2nd at the logical level of application C # Objects. However, you will have to log either on the client or on the application server - it can become critical to memory in terms of the number of processed records (if there are more than 10 thousand changes in the transaction). Again, if you have stored procedures in the database, then in the 2nd option you will miss their changes from the audit