5

As an aspiring DBA, I have recently been asssigned the task of implementing the tracking of all data changes in the database for a peice of software we are developing. After playing with microsoft's change data capture methods, Im looking into some other solutions. We are planing to distribute our product as a hosted solution and unlimited installations would be desired for maximum scalability. Ive looked at IBM's Guardium as well as DB Audit by SoftTree. Im curious if anyone has any solutions they may have used in the past or possibly any suggestions or methods to achieve complete, and of course cost effective, auditing of data changes.

Improve this question
2
  • Do you want to track that the data has changed & by who, or do you also need to track the values of the change?
    – Nick Kavadias
    Dec 11, 2009 at 4:43
  • Yes, Yes and Yes. All data modifications in the database need to be logged with before and after values. However I am trying to avoid using a trigger based systems as I am under the belief this will inpact performance more than a transaction log based system. Basicly CDC would work perfectly but its only available in enterprise and that will not fit our pricing model for this product.
    – Josh Anderson
    Dec 11, 2009 at 16:13

4 Answers 4

Reset to default
1

I use Idera SQL compliance manager. Works well, uses server-side tracing and sends trace files to a central db server and has anti-tampering hashing too. It is also very configurable so you can choose what to audit, and has nice reports with SSRS.

Improve this answer
1

We use Guardium. This is essentially a packet sniffer plus a piece of software installed directly on the database server in the event that anyone would run anything while RDPd into the database server.

Improve this answer
0

Another product that might be of interest is OmniAudit: http://www.krell-software.com/omniaudit/

It uses triggers but short of modifying the original app to include logging of all changes there aren't many options left open to you.

Improve this answer
0

There is one more auditing feature available in SSMS - it's SQL Server Auditing. It provides the server-level and database-level auditing, pre-defined groups and activities, and audits both DDL and DML changes. It can also audit changes of the auditing. Setting auditing for the INSERT, UPDATE, DELETE, SELECT, REFERENCES, and EXECUTE statements is very granular, it can be specified for each statement individually, for specific users, and objects

"All editions of SQL Server support server level audits. Database level auditing is limited to Enterprise, Developer, and Evaluation editions" 1

SQL Server Audit (Database Engine)

However, there is a drawback - capturing of the UPDATE statement is incomplete. The information captured does not indicate what records and columns were changed, nor shows the old and the new values. SQL Server Change Data Capture provides more accurate information about UPDATEs, as it clearly indicates what and how was changed

Improve this answer

You must log in to answer this question.