Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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 '09 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 '09 at 16:13
add comment

4 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.