December 17, 2010 at 12:34 pm
We just recently looked at Idera's Compliance Manager for auditing purposes, but the one big feature it is missing is....Quoting from a review
"The only thing that SQL compliance manager does not do, which some DBAs or auditors might want, is the ability to record what the data was before a change. For example, if a record is updated, the fact that the record was updated is recorded, by who, and when. But there is no record kept of what the data was before it was updated. This is because SQL compliance manager gets its data from traces, and not the database itself. Traces don't contain the "before" data, so it cannot be captured"
What we need is the above. Anyone have any suggestions on a third party tool that will accomplish this.
December 17, 2010 at 12:53 pm
Couldn't you just query their data and pull the most recent record prior to the event you are interested in?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 17, 2010 at 1:01 pm
The problem with out data is we update records and there is no audit trail. I really need something that writes the record somehwere before it is updated and then after it is updated so a compare can be done.
December 17, 2010 at 1:02 pm
How about just doing triggers. Also you may want to look at some transaction log reader options if you want to see 'before' data.
December 17, 2010 at 1:44 pm
I have used this one works pretty good
http://www.toadworld.com/KNOWLEDGE/ToadKnowledge/TipsandTricks/tabid/74/TID/303/cid/38/Default.aspx
good luck
December 17, 2010 at 1:56 pm
Would Change Data Capture work? Curious.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 17, 2010 at 2:07 pm
I would ask two questions before positing a solution.
Is this for a LOT of tables or just a couple? I know that auditors would LOVE to have this for every table but the performance hit is generally too severe AND storage requirements of the changed data beyond budget..
Is there a LOT of I/U/D on the table(s) you need to audit?
If there are a lot of inserts/updates/deletes then triggers to capture the row change can be VERY expensive, although very easy to implement IF you simply write out the before row, if you do any sort of field by field comparison to just capture changes it can be very complex and very expensive. For tables that don't get updated very often then this expense is probably worth it.
If there are a lot of tables then you might consider transactional replication, where only updates and inserts are replicated and sprocs are used as the interface to the log table. Again not a pretty solution.
When I talk about expense I am not talking $$$, I'm talking about I/O and processor costs inherent in performing the actions.
In my experience people will often say log everything, without understanding what the costs are in both setup and maintenance costs ($$$) and processing expenses. Given this I usually end up with a hybrid solution where the important tables that we absolutely need to see before and after values are accounted for and in others who changed what when is captured.
I have been working with SQL 2008 Auditing recently and there is a lot you can do with that, it will capture the statement but not the pre-image.
A good design where tables are only updated/inserted from sprocs is also a way to prevent unauthorized changes, if users don't have write access to the tables they can't make changes without going through sprocs. While this doesn't help with your auditing directly it does help address on of the major reasons for auditing.
And hopefully I haven't rambled too much..
CEWII
December 17, 2010 at 2:21 pm
It is just for a handful of tables; maybe 20 - 30 and there are a reasonable amount of updates with a system that already has inherent performance problems due to poor design from the get go.
December 17, 2010 at 2:29 pm
I would probably opt for transactional replication then. It requires some setup but isn't too heavy on the source system. I would definitely replicate to another database and if possible a different server, so as to not hit the already struggling server any harder.
And although I can't quantify why, triggers *feel* too heavy given this limited amount of information.
I can think of a problem though, it will be harder to tie the actual change record to the log but you will have the before and after row. If the rows have built in ModDt/ModBy data then not so much a problem..
CEWII
December 17, 2010 at 2:35 pm
I wrote articles on auditing and logging, including a lot of options. They're here: http://www.sqlservercentral.com/articles/Auditing/63247/ and (part 2) http://www.sqlservercentral.com/articles/Auditing/63248/.
The discussions include a lot of good data on the subject.
Have you taken a look at those?
- 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
December 17, 2010 at 2:49 pm
dont know if they improved the apex yes...but be careful when you start trying to read files that are large it bombs out...so I had to switch to toad.....this was about a year ago and they were aware of it....besides that it worked good..maybe its fixed
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply