September 25, 2009 at 10:26 am
Guys,
The topic of how to audit tables has recently sprung up in our discussions... so I like your opinion on whats the best way to approach this. We have a mix of both the approaches (which is not good) in our database, as each previous DBA did what he/she believed was the right way. So we need to change them to follow any one model.
CREATE TABLE dbo.Sample(
Name VARCHAR(20),
...
...
Created_By VARCHAR(20),
Created_On DATETIME,
Modified_By VARCHAR(20),
Modified_On DATETIME
)
CREATE TABLE dbo.Audit_Sample(
Name VARCHAR(20),
...
...
Created_By VARCHAR(20),
Created_On DATETIME,
Modified_By VARCHAR(20),
Modified_On DATETIME
Audit_Type VARCHAR(1) NOT NULL
Audited_Created_On DATETIME
Audit_Created_By VARCHAR(50)
)
Approach 1: Store, in audit tables, only those records that are replaced/deleted from the main table ( using system table DELETED). So for each UPDATE and DELETE in the main table, the record that is being replaced is INSERTED into the audit table with 'Audit_Type' column as wither 'U' ( for UPDATE ) or 'D' ( for DELETE)
INSERTs are not Audited. For current version of any record you always query the main table. And for history you query audit table.
Pros: Seems intutive, to store the previous versions of records
Cons: If you need to know the history of a particular record, you need to join audit table with main table.
Appraoch 2: Store, in audit table, every record that goes into main table ( using system table INSERTED).
Each record that is INSERTED/UPDATED/DELETED to main table is also stored in audit table. So when you insert a new record it is also inserted into audit table. When updated, the new version (from INSERTED) table is stored in Audit table. When deleted, old version (from DELETED) table is stored in audit table.
Pros: If you need to know the history of a particular record, you have everything in one location.
Though I did not list all of them here, each approach has its pros and cons?
thanks,
_UB
September 25, 2009 at 12:55 pm
After I wrote all my thoughts into this question/post and re-read it, approach 2 seems like a better one. The small over head is negligible. Is one more row per item really going to kill the DB?
_UB
September 25, 2009 at 4:08 pm
UB-242988 (9/25/2009)
After I wrote all my thoughts into this question/post and re-read it, approach 2 seems like a better one. The small over head is negligible. Is one more row per item really going to kill the DB?_UB
I guess "It Depends" on how big the tables are that you are auditing, and how many rows are affected by modifications. Option 1 isn't that difficult to handle, a simple UNION between the audit table and the current table ordered on some kind of time stamp should work fine?
September 25, 2009 at 4:18 pm
I'm a bigger fan of #2. You can join to the main table, but I've seen performance problems with this when you have people doing a lot of auditing. Plus this way you can "archive off" the the audit records as one easy bcp out, and have the data in there.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 25, 2009 at 5:07 pm
I agree with you Steve in terms of the amount of auditing done, but I guess it also depends on how wide your tables are. Duplicating the most recent records in a 500 million row table might not exactly come cheap. Then again, one could partition those audit tables and move the older rows off to cheaper/slower media if performance remains acceptable to satisfy the auditing requirements? In fact, the question is really, what ARE the auditing requirements? Compare previous to current version by default, and go back to earlier history by exception only?
September 28, 2009 at 8:24 am
Thanks for the responses.
Just out of curiosity... is there a name to these approaches. Like, I was told Approach #1 is called as "Trans. Log Auditing" and Approach #2 is referring to as "(Regular) Auditing".
I can understand the reason behind it, but not sure if it is standard way of calling them.
thanks again,
_UB
September 28, 2009 at 9:13 am
These two articles and their discussions cover the subject pretty thoroughly. If you haven't already, you might take a look at them:
http://qa.sqlservercentral.com/articles/Auditing/63247/
http://qa.sqlservercentral.com/articles/Auditing/63248/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2009 at 9:28 am
GSquared (9/28/2009)
These two articles and their discussions cover the subject pretty thoroughly. If you haven't already, you might take a look at them:
Very good articles, the author does actually know what he is talking about 😀
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 28, 2009 at 12:04 pm
Silverfox (9/28/2009)
GSquared (9/28/2009)
These two articles and their discussions cover the subject pretty thoroughly. If you haven't already, you might take a look at them:Very good articles, the author does actually know what he is talking about 😀
Thank you. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2009 at 1:32 pm
Thanks GSquared. I've read this article before and its very informative.
_UB
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply