Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part I Expand / Collapse
Author
Message
Posted Monday, May 11, 2009 2:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 11,265, Visits: 13,024
Nice and article and an interesting concept. I'd also be concerned about overhead and space issues. Although space issues could easily be dealt with by purging the table regularly. I can't imagine using this process to rollback something that happened a month ago.

I look forward to article number 2.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #714466
Posted Monday, May 11, 2009 2:01 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
Nice article. Good feature too. But would like to know how it would work with 1000's of tables on production where updates, inserts and deletes are in high volume. Wouldn't Trigger going to kill the server.

Would wait for II nd part.


SQL DBA.
Post #714468
Posted Monday, May 11, 2009 3:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
David McKinney (5/11/2009)
I agree - it is highly dubious! But hopefully that doesn't make it without merit.

Hey David,

Point taken - and I agree it is a good thing to experiment and do things just to see if they can be done.
In fact I agree with all of your comments.
If the article had said what you said in your comments, I probably wouldn't have bothered posting at all

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #714553
Posted Tuesday, May 12, 2009 12:21 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:02 AM
Points: 646, Visits: 1,863
Paul White (5/11/2009)
David McKinney (5/11/2009)
I agree - it is highly dubious! But hopefully that doesn't make it without merit.

Hey David,

Point taken - and I agree it is a good thing to experiment and do things just to see if they can be done.
In fact I agree with all of your comments.
If the article had said what you said in your comments, I probably wouldn't have bothered posting at all

Paul


You're right, Paul, there is a complete lack of real world context / usage in this [Part of the] article.
It was originally one long article (i.e. not in two parts). Unfortunately when I split it, my warnings over usage fell solely in the second part.

Anyway, I'm glad my comments have reassured you!
Post #714735
Posted Tuesday, May 12, 2009 12:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 21, 2012 11:13 PM
Points: 2, Visits: 31
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
Post #715375
Posted Tuesday, May 12, 2009 12:53 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:02 AM
Points: 646, Visits: 1,863
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.
Post #715391
Posted Tuesday, May 12, 2009 5:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 21, 2012 11:13 PM
Points: 2, Visits: 31
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
Post #715540
Posted Friday, May 15, 2009 2:02 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 28, 2014 5:37 PM
Points: 536, Visits: 757
I can totally see using this for some databases (or even specific tables), for SOX compliance purposes. Looking forward to the next installment.


Post #718270
Posted Monday, May 18, 2009 8:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 27, 2010 10:31 PM
Points: 15, Visits: 69
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.


Post #719522
Posted Saturday, May 23, 2009 10:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 3, 2014 3:47 PM
Points: 12, Visits: 48
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.

Post #722596
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse