August 11, 2011 at 9:24 am
Up front caveat: yes, I know about Change Data Capture. I also know how to use it. I would love to use it. It's not an option at this point.
I'm reviewing the way I'm handling record change auditing in a SQL 2008 Standard edition OLTP database. The changes I need to track are in application tables where I cannot change the table structure, so in this particular case I'm stuck with the application software's existing structure. While in some cases I have access to the stored procs that the product uses, in other cases the SQL code is baked into product executables, so I can't always rely on being able to touch the existing program logic. (Our custom software and dbs I have complete control over - this isn't that type of situation.)
I've been handling these with triggers that write to audit tables - in many cases I only need to track change information when a small subset of the available fields in table change, but I often need to track related changes across multiple tables in order to capture the actual information.
I'm about to expand this process, and was wondering - has anyone come up with a better way to track data changes in this type of a situation? An extended hunt through the forums and a wander through Google haven't come up with anything, but it never hurts to ask!
-Ki
August 11, 2011 at 11:21 am
All of my SQL Server 2008 work has been in Enterprise Edition, so I've not not needed to investigate it, but there is a bolt-on product for enabling CDC in Standard Edition that might bear inspection: http://standardeditioncdc.codeplex.com/
August 11, 2011 at 12:47 pm
Dave23 (8/11/2011)
All of my SQL Server 2008 work has been in Enterprise Edition, so I've not not needed to investigate it, but there is a bolt-on product for enabling CDC in Standard Edition that might bear inspection: http://standardeditioncdc.codeplex.com/
Thanks - I hadn't seen that. I'll take a look - I'd love to drop this collection of triggers in favor of a better solution!
-Ki
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply