January 29, 2010 at 9:28 am
Hey All,
I am trying to implement an audit trail on a table (pretty big) that has pretty massive updates, inserts, deletes through out the day.
I was thinking of having an audit table with the and audit_index (Primary Key, identity), old and new xml data stored as XML columns along with info on who made the change, etc.
For inserts, I'll only have new xml, for updates both old and new XML and for deletes have old xml.
With this, I could shred the XML data (old and new) to say 2 temp tables. I'll join these 2 temp tables on the audit_index to see what changed.
Am I going to face any performance issues? I am using sql 2008 standard edition. Are there any better solutions? Thanks
January 29, 2010 at 9:31 am
These two articles and their discussions might help:
http://www.sqlservercentral.com/articles/Auditing/63247/
http://www.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
January 29, 2010 at 10:17 am
I'd second a read on those articles.
You're going to face some contention no matter what. One thing to minimize it is get the audit tables onto another filegroup/disk.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply