March 26, 2008 at 9:52 am
Firstly, apologies for what I'm sure is a very noobish question. I did thrash around with the forum search, but I gather I'm not phrasing the problem correctly.
I'd like to know the best way to track changes to records (adds, deletes, updates) in our database. My knee-jerk response is use a trigger for each table that simply writes the tablename, pkey, action to one central table for the database (and maybe the whole record for an update event). But something in my noggin tells me that might be kludgy.
Mind, our transaction rate is fairly low - I'd guess < 1,000 per hour on some core tables. Probably half that.
C2 auditing would be catching fish with nukes, in this environment.
What's the graceful, intelligent way to handle this? Major modifications to the tables (like actually having a datestamped gerund to handle historical data between entities) ain't happening.
...man, I hate posting this. I feel like I'm about to get creamed with the "durr" stick.
March 26, 2008 at 11:23 am
Not even, don't worry about it.
You've got a lot of options. You mentioned triggers. That is one possibility. Another would be to use the OUTPUT clause from your data modification queries to capture the data being modified and insert it into auditing tables. Since modifying the tables is out of the question you can't go with some of the more interesting solutions. We use a method, not open to you at this point, where we only do inserts to our tables. All data changes are kept forever. Transactionally, we're pretty low end, but we have to have an absolute collection of what changed when and this is how we did it.
That's just to get you started. The more qualified people will be coming along with much more detailed posts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 26, 2008 at 11:43 am
First, it's not a "n00b" question. There are some complex things that have to be taken into account for this kind of thing.
Now, on to the meat of the question.
There are a number of options.
For "passive logging", you use a log-parsing application to pull audit data out of the log files and backups for the database. This is a very reliable means of getting an audit trail, since the log (so long as you are in Full recovery mode) keeps track of every change made. The reason I call this "passive logging" is that it requires no extra action on your part or on the part of the database. You just buy a program that reads log files and follow the directions and you're good to go. Lumigent, Red Hat and Apex all make very good log parsing applications. The disadvantage to these is that, in my experience, they are slow and somewhat clunky, and you can't use T-SQL to query the log or build reports from it.
For active logging, the usual solutions are either have all of your Update/Delete/Insert procs log the changes, or use triggers to do the same. This adds some processing overhead to the database, and it results in data being stored in standard tables. This has the advantages of being relatively easy to set up (there are products that will set it up for you if you can't do it yourself), easy to query and report against, and looking up row history is as fast as your server can query a table. It has the disadvantages that it does require more CPU and more I/O for the logging, and, if you end up with a dishonest person who has too much access to the log tables, it becomes easy to falsify records.
Personally, I go with active logging for key tables, and passive for the ones that "will never come up". There's a discussion I started a while back on this site (http://www.sqlservercentral.com/Forums/Topic472257-149-1.aspx), on this very subject. In summary, I proposed logging using triggers and XML, and Jeff Moden disagreed with how much I was logging. His solution would take a bit more work to set up and to generate reports from, but would definitely take less disk space, etc.
If you read that and have specific questions about any of it, I'd be glad to help out on them.
- 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
March 26, 2008 at 12:39 pm
Red Hat ...
did you mean Red Gate?
March 26, 2008 at 1:00 pm
Grant, I'm sorry, but this is my second interaction with you and...well..you aren't very scary 😀 Quite helpful, in fact. And thank you G^2, great thoughts and link reference.
I did have an internal "durr", as parsing the logs never occurred to me 😉
March 26, 2008 at 3:12 pm
Harveysburger (3/26/2008)
Red Hat ...
did you mean Red Gate?
Yes. (Here I am, with their logo staring me in the face as I type these things, and I still managed to get that one wrong. Gotta laugh at myself on that.)
- 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
March 27, 2008 at 5:33 am
A teenager down at the dojo says I'm losing my touch... He's testing in August. I'll "touch" him then.
Anyway, glad to be of use. One other point worth mentioning is that SQL Server 2008 is going to have a whole set of mechanisms built around auditing. The most interesting of which is Change Data Capture, a mechanism to pick up the data as it changes and store it along side the table(s) you're auditing all built in as part of the data definition. Pretty cool stuff.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 27, 2008 at 8:40 am
Yeah, the audit features in '08 look pretty useful.
- 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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply