SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Jack Corbett
  Jack Corbett
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24454 Visits: 14905
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4755 Visits: 1619
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.
Paul White
Paul White
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20552 Visits: 11359
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
David McKinney
David McKinney
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1707 Visits: 2090
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!
Simon Taylor-375472
Simon Taylor-375472
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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
David McKinney
David McKinney
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1707 Visits: 2090
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
Simon Taylor-375472
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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
Misha_SQL
Misha_SQL
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 1009
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
marshall.jones
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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 Smile

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
Maurice Pelchat
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 51
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search