On DB2, who updated a record and what did the record look like before the update?

Question

(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.

Answer 1

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

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

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

Answer 2

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.

Answer 3

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).

Answer 4

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.

Answer 5

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)

Answer 6

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.

Answer 7

I'm an open sorcerer and cannot afford MS license fees for SQL Enterprise Edition, which Change Data Capture requires.

I developed yact on github that uses simple SQL triggers to store the 5Ws (who, when, where, why and what). It stores before and after images of data as XML.