What advise means to audit data changes in MSSQL?

0 like 0 dislike
7 views
What advise means to audit data changes in MSSQL (need to keep changing all the fields), where the sheaf mssql + NHibernate.

From what I have found is:

1. To hang up on triggers in the database the history record in table with history.

2. In the NHibernate hang handlers in Interceptors or EventListeners and write to tables with history.

3. To use the feature MSSQL 2008 — Change Data Capture. The question here is, is it worth it for this use, I read somewhere "the Main scenario in which you want to use CDC is "big" ETL (extraction, transformation, loading) applications that asynchronously pieces distilled data from OLTP systems into the data warehouse."

Maybe there is a ready simple solution.
by | 7 views

3 Answers

0 like 0 dislike
The first option is the easiest. The implementation of temporality due to the apparent duplication of data. Problem — the data sample for the period (in fact, the changes are received). Often used by developers, as is usually done to fit your specific goals with your business logic when changes are selected.
The second option — the same eggs, only in profile. Would recommend after all of these two to choose the first one (on the DB level it is better to organize temporality).
The third is a good option. The main scenario is what this scheme can be applied more often and better. In your case, this thing will also be useful.
\r
We also recommend to pay attention to the article, which discusses the temporality of databases and proposal developers to ensure that the temporality in the DBMS.
\r
Here is an example of the discussion of temporality (history of changes) for the MSSQL. I think the theme might be useful.
by
0 like 0 dislike
On .NET/Java was never developed, but in one clever book recently I saw the recommendation to use DbDeploy, but don't know how this will help solve Your problem.
by
0 like 0 dislike
depends on the load, if the data and changes is very much critical performance, the only 3rd option CDC - can run without load on the main database (on a dedicated server), but requires MS-SQL Enterprise Edition
additionally, view the project AutoAudit on codeplex - maybe for you.
the difference between 1.3 and 2, that 1и3 you will see the story on the physical level, DB (database table), in the 2nd at the logical level applied C# Objects. However, the log will have either on the client or on the server applications can be critical to memory to the number of processed records (if the transaction more 10tysyach changes). Again, if you have in the database is a stored procedure, then in the 2nd option, you miss out the audit of their changes
by

Related questions

0 like 0 dislike
6 answers
0 like 0 dislike
7 answers
0 like 0 dislike
6 answers
110,608 questions
257,186 answers
0 comments
1,119 users