CRUD Application Logging

Question

I am attempting to overhaul my CRUD application's logging architecture. It is a .NET Winforms application with a SQL Server 2008 R2 backend.

In the current setup, a call is made to the database log any time the user presses a 'Save Button'. A changeset is determined using .NET reflection on classes that represent tables that we have stored in SQL.

The log is stored in two tables called ActionLogHeader and ActionLogDetail.

Header Schema:

Detail Schema:

ActionLogDetail_id | ActionLogHeader_id | ColumnChanged | PreValue | PostValue

There can be multiple Details for a single Header. Each detail row represents a single column that changed in a table. An ActionType is insert/update/delete/view.

This generally works for showing users a list of changes that happened to any one thing in our database. But it becomes difficult to pull data out of this to create a complete picture of what something looked like at any momemnt in time. Aggregates over this data for specific data points are difficult.

We've explored moving this data out of SQL Server and into Hadoop/HBase but I feel all we've done is move the data without changing the structure. We've flattened the table and added more columns so fetching data from SQL wouldn't be an issue. But doing any sort of analysis over the data still needs a MapReduce job which is a pain to set up.

So I'm interested in what other people are doing for logging. Is there a better strategy? I've seen that a lot of people like triggers at the database level for some change-logging but I don't know how that affects performance. I'm not sure where to go from here.

Sometimes we simply serialize the "post" object(s) as JSON/XML, supply some metadata for it, and dump that single entry in a table. If you want to know what changed, you pull the current and previous rows and do the diff on the serialized data on the fly and show that to the user. — Cᴏʀʏ, Aug 07 '14 at 20:11
I am not a fan of the EAV style logging you describe for very reason you are running in to. It is nearly impossible to create a point in time view of the table. Triggers are an option but that can get really cumbersome as you have to add triggers to every table. This also adds to system testing for new tables and such. And of course if the triggers are not written well they can have a major impact on performance. Another option is CDC (Change Data Capture). It is not the cat's meow as there are some limitations here too but it might be worth exploring. — Sean Lange, Aug 07 '14 at 20:11
@Cory You're suggesting dumping the entire row somewhere? Sort of like a snapshot? — Cuthbert, Aug 07 '14 at 20:56

score 3 · Accepted Answer · edited May 23 '17 at 10:30

Take a look at Change Data Capture Microsoft CDC docs" to see if you could use this instead of rolling your own. There is also a article that covers the basics of CDC that is useful as a overview. Of course, there is overhead regardless of you do it.

If you are trying to log only things coming from your application, your approach would be preferable.

Some people also use 3-rd party tools that use the Transaction log (.ldf) for database change audting .

ADDED

Remembered this article, re CDC performance. If MS can be trusted, if should be better performance than most roll your own solutions. Of course to prove this, you would have to roll you own and compare it. A key paragraph in the article:

Change data capture enables the capture of changes in the source system by asynchronously reading the transaction log of the source database. For this, change data capture uses the same log reader that is used in transactional replication. Because change data capture works on existing table schemas, the source database or application doesn’t need to be changed to enable change data capture. Because the log reader job works asynchronously, DML transactions are far less impacted than with synchronous solutions like triggers. All changes to the source tables are recorded in special change tables, so no comparison between source and target system for changes is needed.

ADDED

Other alternatives.

Well, you could write triggers, etc. as you mentioned to capture all data changes, with slowing down your server, debugging issues, etc. You could use a 3rd party tranlog analyzer (Apex) that analyzes the logs, you could continue down your current path with the gaping holes, or you could upgrade to the Enterprise version. Nothing is going to magically make everything wonderful without cost and/or effort.

There are other valid approaches too.

See prior answers to similar questions

creating-audit-triggers-in-sql-server

best-way-to-implement-an-audit-trail-in-sql-server

I need to log things at the database level, too. That is a gaping hole in our logging in that mass updates not done in the application never get logged. — Cuthbert, Aug 07 '14 at 20:28
CDC will log all data changes, if you are changing schemas, you have a bit more work to make CDC work. — Gary Walker, Aug 07 '14 at 20:42
CDC is only available in the enterprise version of SQL Server. We're on Standard. Is there an alternative? — Cuthbert, Aug 07 '14 at 20:49

CRUD Application Logging

1 Answers1