Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

(SQL Server 2008 R2 Standard, database under full recovery)

I have a table with fields id, firstname, lastname. A statement is executed:

insert into dbo.sometable (id, firstname, lastname) values (1, 'John', 'Smith')

After a few hours another statement is executed:

update dbo.sometable set firstname='Matt' where id=1

Is there a method or a tool that would allow us to see the history of a record (that a record was inserted on X date by Y person and these were the values inserted, and that same record was updated on A date by B person and these were the new values after the update)?

Users familiar with DB2 for i (ie, DB2 on the iSeries) might know of a TAA toolset command called CRTDBFJRN that can dump all transactions on a given table into another table giving a clear picture of the insert, the update and the field that was updated.

SQL Server Enterprise has CDC but we have a Standard edition; I could use triggers to track changed data by storing in an audit table. The use of triggers will require two more objects - a trigger and an audit table, and triggers would be fired on each change. It would be nice if a tool could replay transactions on demand from a transaction file.

What tools do you use or tasks do you perform when an auditor asks for a history of a financial record (inserted and updated on a single day, for example) such as: Who changed this record, what was changed, when was this record originally entered, what was the data when it was original entered etc?

Any help will be appreciated. Thank you.

share|improve this question
1  
Do you want a solution for both DB2 and SQL Server based on the tags? Or just SQL Server based on the text? –  gbn Jul 8 '11 at 16:06
    
Note the DB2 mentioned is DB2 for i, rather than DB2 LUW (Linux, Unix, Windows), so the DB2 solution would possibly be different on the iSeries. –  Chris Aldrich Mar 13 '13 at 12:55
add comment

6 Answers

I've just come across StandardCDC which might be of interest:

StandardCDC captures data manipulation language (DML) changes for a specified table and stores the results in a relational format. The capture table mirrors the column list of the tracked object, with options for storing only specific columns.

share|improve this answer
2  
Welcome to dba.se Chris - I hope you don't object to my edits in any way :-) –  Jack Douglas Feb 23 '12 at 15:08
add comment

Triggers and audit/history tables. And columns in the main table for Inserted/Updated

  • A history table is easily queried and can be demonstrated to an auditor
  • Universal across different RDBMS
  • No history write = no write because the trigger is part of the transaction

From the audit/history tables you can get the state of the database at any point in time.

Edit:

It's quite common to have multiple write paths onto a table and a trigger will ensure that changes are captured. This changes if you use table valued parameters to allow only one stored proc to actually write, which is called by other code.

You also have to consider when the actual end user is a proxy away (eg via a web server) where your client code must pass in credentials (or enable pass-through).

share|improve this answer
    
SInce it is financail records he is interested in and the data must stay for all time, I can't see where any solution except audit tables is appropriate. Of course nothing can recreate information that was not stored to begin with. –  HLGEM Feb 23 '12 at 14:57
    
@HLGEM: welcome to dba.se. Join us in chat.stackexchange.com/rooms/179/the-heap ? –  gbn Feb 23 '12 at 15:00
add comment

Change tracking can be used in Standard Edition. It will allow you to track changes to the data, but won't be able to tell you who made them.

Your options are basically the following:

  1. triggers - maybe even use instead of triggers - for an update you can move the current copy of the row to a history table, and replace it with the active copy.
  2. move to Enterprise and use SQL Server Audit and/or CDC.
  3. run 24/7 trace
  4. stop allowing users to run ad hoc queries like update table. If you control data access via stored procedures, you can do all the logging you want (and even bypass it depending on business rules) without having to add triggers to all your tables, move to a more expensive edition, etc.
share|improve this answer
    
Removing ad hoc query access has added benefits for security, plan cache, and performance. –  Robert Miller Jul 8 '11 at 19:27
add comment

Other options except the ones wrote by Aaron and gbn:

  • use a 3rd party audit tool (redgate, lumigent...)
  • implement custom package using fn_dblog system function (reads the transaction log)
  • use 24/7 server traces (already mentioned by Aaron, sorry)
share|improve this answer
add comment

I've tested two tools that can provide info about transactions including when, who, using which computer and application

ApexSQL Audit creates auditing triggers for you, so if you're not up to coding and creating triggers for each of your tables, this is an option

enter image description here

It has 2 built in reports, and the good thing about it is that it saves all captured transactions into 2 tables, so you can run queries against them and extract any info you need

enter image description here

Unfortunately, it can't replay the transactions

Another one is ApexSQL Log. It reads transaction logs and trn backups, so the database has to be in Full recovery model. Besides the data transactions, it also tracks schema changes (create table, alter function, etc) and it can replay transactions

enter image description here

share|improve this answer
add comment

SQL2008 has a new feature called CHANGETABLE that you can use. Check this link to read more about it. CHANGETABLE returns change tracking information for a table. You can use this statement to return all changes for a table or change tracking information for a specific row.

share|improve this answer
1  
Just to clarify, you can't just use CHANGETABLE() to check a table's changes after the fact. You need to enable CHANGE TRACKING on that table first. As I mentioned in my answer, it also doesn't tell you who made the change, which seemed to be one of @Ano's requirements. –  Aaron Bertrand Jul 8 '11 at 22:20
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.