2

I need to audit modification actions (create/delete/update) in an enterprise web application. Then the user may need to see history of changes of a specific entity (a Person for example) to decide to do an action or not.

As far as I know it can be done into application by using libraries like log4net, nlog,... in .NET or maybe there are other libraries special for versioning which I don't know.

Or it can be done in database for example using Change Data Capture of SQL Server.

Using built in database solutions seems more efficient but the question is:

Is it correct in terms of software engineering to control business of application into Database?

Saber
  • 2,440
  • 1
  • 25
  • 40

4 Answers4

2

In my company's applications we log in both places, but for different uses:

  • Application: The application keeps a detailed log actions for debugging / tuning.
  • Database: 'Official' audit data is held in the database, and is added as part of our stored procs. Being in the database makes it trivial to access, controls access, and makes real-time monitoring easy.
Adam Caudill
  • 194
  • 1
  • 11
2

For basic auditing that allows us to track changes to each table, including the data in the table, recording what was changed, when, and by whom, we've had good luck with AutoAudit. It is an open-source package that adds automatic auditing to any or all of the tables in your database.

I have only personally tried it on relatively small projects (fewer than 20 tables, and only a handful of simultaneous users), but it has worked quite well. It takes almost no effort to set up, and the out-of-the-box features met our needs.

A major upgrade to AutoAudit version 3.20 was released in November 2013 with these added features:

  • handles tables with up to 5 PK columns

  • performance improvements up to 90% faster than version 2.00

  • improved historical data retrieval UDF

  • Handles column/table names that need quotename [ ]

  • Archival process to keep the live Audit tables smaller/faster but retain the older data in archive AutoAudit tables

You can find it here.

Community
  • 1
  • 1
Mark Meuer
  • 7,200
  • 6
  • 43
  • 64
1

What version and edition of SQL Server are you using? SQL Server 2008 + support auditing natively, this is an Enterprise Edition feature in 2008 and 2008 R2 I believe, but server level auditing is available in the standard edition of SQL Server 2012 with only database level audits being an enterprise feature.

See the following link for details:

SQL Server Audit (Database Engine)

Change data capture isn't really designed for auditing, it's meant to be used for providing a summary of data that has changed on a source system so that it can be easily consumed by another system.

EDIT:

So, if you need to keep the actual data that has changed, then CDC can do this, but you won't be able to see who changed it, so it is not an audit. A combination of CDC and auditing could work, but would be complicated to set up.

The easiest way would probably be to maintain a history table using triggers for update, insert and delete, adding the audit information such as when the modification took place and who performed the action. This would also mean that you would not have to use an enterprise edition of SQL server as triggers can be defined on all editions.

steoleary
  • 8,968
  • 2
  • 33
  • 47
  • I need to track changes of a table, get a list of old values and even deleted records of that table. Do you still believe for this scenario SQL Server Audit is preferable over CDS? Why? – Saber Mar 13 '13 at 09:56
  • 1
    OK, so if you need the data, then CDC can do that, but I don't think it keeps track of who modified the record, so it's not an audit. I guess you could use a combination of CDC and auditing, but that is a bit over the top probably. What might be easier is to use triggers that fire on insert, update, delete and store the record in a history table along with the user that modified it (if you can get this information). You could do this with standard edition SQL server then too, rather than having to use enterprise. – steoleary Mar 13 '13 at 10:20
  • so if you like please edit your answer and I'll mark it as answer – Saber Mar 13 '13 at 10:27
0

Yes, I would recommend to do it in the database. This solution will be faster. In terms of software engineering, it is correct because you will use the Database for the solution, but you will create another layer (the UI) to retrieve the information from the database.

If your SQL Server is not an enterprise edition, you may need to create triggers manually, or maybe buy helping tools like apexsql trigger to help you to audit that information.

Daniel Calbimonte
  • 35
  • 4