Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part I

Question

Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part I

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

David McKinney SSChampion Points: 10472 More actions · Answer 1

Simon Taylor (5/12/2009)
Nice example, David. I developed a complimentary set of audit functionality that audits data changes themselves a couple years ago for SQL Server 2000. I ported it to SQL Server 2005, and would like to generalize the approach and clean up the implementation, but I understand that data-audit functionality ("Change Data Capture") is a built-in feature in 2008. So, I suspect that the life of my little custom tool for auditing data changes is near it's end.
However, I am most interested in your follow-up example. The database I wrote data change auditing for has several hundred tables. So, automating the trigger generating code was a key challenge I faced, and the code I came up with to generate the data change audit triggers was somewhat complex and cumbersome. Since you are going to conquer a similar problem in generating these triggers, and you have hinted that you are going to use XML, it sounds like you are going to solve the same problem using a different approach. I am quite curious to say the least!
Simon Taylor

Hi Simon,

You might be interested in an article I wrote some time ago, which sounds like it might be more like what you built. http://www.sqlservercentral.com/articles/Security/3179/

Let me know what you make of it.

Good luck,

David.

Simon Taylor-375472 Grasshopper Points: 18 More actions · Answer 2

Thanks, David. I took a look at that post you mentioned. It was similar in the end result to what I had done, but your use of XML generated from the table meta-data, and XSL to generate the actual SQL script for the triggers was a completely different approach.

In my original approach, I had generated embedded TSQL and executed via sp_executesql. So, probably more of a traditional approach that way.

Although I have not developed much of a liking for XSL, I also consider the drawbacks of writing embedded SQL code in TSQL batch and/or stored procedures as pretty significant. So, I do like the concept of separating the SQL generating portion into a transform of XML data. Thanks for pointing out your prior article, and again, looking fwd to part 2 of the auditing with rollback post!

Simon

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 3

I can totally see using this for some databases (or even specific tables), for SOX compliance purposes. Looking forward to the next installment.

marshall.jones SSC Enthusiast Points: 177 More actions · Answer 4

I think this is an interesting and useful example so long as you know enough to not use it as is in real life 🙂

The xml scripting with CTE is very interesting and I think the concept should prove useful in isolated situations, just don't go and blindly apply this to a database without testing it out or you will drown in a slow database with a ton of data.

Maurice Pelchat Mr or Mrs. 500 Points: 546 More actions · Answer 5

This approach needs some more improvements. Updating all columns is problematic for trigger that test "if update()" because if there is no necessarily a real change. You put the column is in the update set clause but it is not necessarily changed. At rollback or rollforward time the underlying trigger is going to fire for nothing. You need to add these if update clause to avoid adding unnecessary columns.

You also have to exclude some column from insert, columns that are not to be supposed to be there like identity, add set identity_insert on for those tables to keep original identity values.

Another problem, if you process a "rollback" you will still experiment an hole in identity sequence values. The next value is going to be beyond. But this is already a problem with a real rollback, identities doesn't come down.

You audit table should be placed in a different database, because it doubles logging.

Idea is interesting but there is probably other issues. And don't expect you rollforward to work at the same speed of the original operation. If the original operation is an Insert ... Select or an Update from table join another table or Delete from table join another table (I mean set oriented insert, update or delete) these run many times faster (x20) than individual insert, updates or delete.

Vladimir Sotirov SSC Eights! Points: 921 More actions · Answer 6

Good Article David,

do you think you can change the method to use CDC in SQL2008 instead of triggers? We all know why we do not want to use triggers.

The CDC do not give you the SQL that needs to be run to roll forward and roll backward. IF you make the article for SQL2008 this will still has its value.

nellisjp SSChasing Mays Points: 624 More actions · Answer 7

Seems to me that this could be leveraged as a home-grown replication solution between non-homogeneous databases. Just need an application at either end to ship-and-apply the changes.