SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An Audit Trail Generator


An Audit Trail Generator

Author
Message
noeld
noeld
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12746 Visits: 2048

Although the article is well written and many of us have used this sort of techniques in one way or another we should not be blinded by its drawbacks.

1.The use of dynamic SQL in triggers is by all means NOT good. From performance perspective you may need to staore DELETED and INSERTED into Temptables increasing significantly the ovehead of the trigger.

2. As someone already posted you will need to cater for TEXT columns which are not accessible from this kind of triggers.

3.I don't think that metadata volatility is an excuse to use dynamic SQL. Instead the trigger code should be regenerated at metadata change time (IN MY OPINION).

4.This solutions when used across 100+ databases become impractical and Things like Idera's Compliace manager will change that landscape due tue the stability nature of a triggersless solution. Only when the number of databases is "manageable" is that a solution like this should be implemented.

Just my $0.02




* Noel
LP-181697
LP-181697
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 143

Sorry if you consider the comments so offensive. I am an author and I have been a developer for about 10 years before become an architect/DBA. And I did the same mistakes because this is a peoples nature to think about their work but not to view a big picture of the corporate project. This is why the architect position is so important. And if a company is not understand it then there is a problem for everybody including developers. I am not blaming developers directly, but in a lot of cases I hear how developers (and I got the same sin before) telling to managers that they do not need an Architect/DBA because they can do the job by ourselves. And I do understand that there are different DBAs and different developers. My point is that both always should work together.


Mike-263299
Mike-263299
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 38

Option 1) Would it be possible to put a trigger in place that would alter the audit tables with the same alter that was performed on the base table?

Option 2) No alter table commands could be submitted without a corresponding alter table for the audit table.

Personally, I'm not sure how much I'd like having one big table storing all the audits, etc...I would think sifting through millions of rows of changes to compile an audit on a table would suck.

Regardless...

Has anyone calculated the overhead of doing auditing like this? I would think it would be really painful on an OLTP environment that had many users. I haven't looked, but I bet there are tools that create audits based on the network traffic to the server, which would surely lesson the performance hit on your production system.

One more thing...There's no way this would work on a true 3-tier application, right? I mean, the application has to be written to store something like "LastUpdateUser" in the table that is being updated. If it isn't then you can't pull hostname or username because you'll just get a bunch of changes done by the web/app server.

Mike


Gordon-265412
Gordon-265412
SSC-Addicted
SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)

Group: General Forum Members
Points: 462 Visits: 1188
What happens if you need to reseed your Identity values?

Your friendly High-Tech Janitor... Smile
Leo Peysakhovich
Leo Peysakhovich
Say Hey Kid
Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)

Group: General Forum Members
Points: 675 Visits: 361

Thanks Anatol. For some reason people get the articles as 'dogma' but not as the idea to review and extend it based on your conditions. I am trying to put an idea. In reality, the full solution has much better metadata portion, ability to write only specific columns (when required) and some other staff. I described main portion that allows anybody to add project specific parts.

Thanks again,

Leo P.





Leo Peysakhovich
Leo Peysakhovich
Say Hey Kid
Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)

Group: General Forum Members
Points: 675 Visits: 361

What kind of reporting?

First, you can write not a old row of data to the audit table, but new row of data. This will allow you to keep everything in audit table - previous and current rows. Then you can get report from one table.

meandi1999





Leo Peysakhovich
Leo Peysakhovich
Say Hey Kid
Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)

Group: General Forum Members
Points: 675 Visits: 361

If a text field is a part of the table, then separate statement should be added as a part of the trigger for the table. But it will bring the performance a little bit down. In generator, this is can be analyzed.

There will be 3 solutions:

1. Update record in the audit table

2. Create a new record for the text column only in the audit table, e.g. will be 2 records if text and the other fields are updated.

3. Create separate table with next columns: TableName, TEXTCOLUMNNAME, TextColumnValue and insert the text changes for all tables in database into one table.

I think, the third one is the most elegant and has the best performance results.

Leo P.





Leo Peysakhovich
Leo Peysakhovich
Say Hey Kid
Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)

Group: General Forum Members
Points: 675 Visits: 361

I have used such triggers for a while. Performance degradation is about 20%.

For non-heavy OLTP systems this is a simple solution that is working. We got 200+ databases every year and generator actually made the live is much easier. In addition, table and database parameters can be added to make generator completely dynamic without compiling the same stored procs over and over again. In my case it was completely dynamic solution and all procs were stored in support database. All I need to provide database name as parameter, flag for all tables or comma separated list of tables. Then the generator is creating a script and script should be applied to the database.





Anatol Romanov
Anatol Romanov
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 56

As we all know - each project has different auditing requirements, and there is no magic solution, I'm afraid. Let's consider just some of the possible requirements:

1. Metadata never changes.

2. Metadata changes frequently because user tables are created dynamically by the application.

3. No text or image datatypes are present.

4. Text or image datatypes may exist, but we are only interested in the fact they've been changed, not the actual before/after values.

5. All tables are owned by the dbo.

6. Any user can create a table which is to be audited.

7. Every single change to data has to be recorded into audit log.

8. Only selected operations on selected columns should be audited. The application admin (not a DBA) should be given a tool to configure what's audited.

9. The reason to set up data audit is to satisfy a statutory requirement. Nobody is interested in querying the audit data effectively.

10. The audit data is to be queried on a regular basis, effective table design and indexing is important.

So far, there was nothing a good database specialist couldn't address. Now a few requirements representing a challenge in SQL2K:

11. Operations which don't fire a trigger must also be audited. This includes data access without modification (SELECT) and TRUNCATE TABLE statements.

12. Changes to business objects rather than database objects should be audited. A business object may be spread over a number of database tables.

13. For systems where auditing is not critical, failing to insert a record into the audit table shall not cause the data modification to rollback.

I am most interested to discuss the last three.

Anatol


Ian Yates
Ian Yates
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2552 Visits: 445

Well for 11, you might have to have a profiler trace with very particular filters running to catch such statements against your database.

For 12, I assume you mean recording say, an order and its detail lines (which would hopefully be in two separate tables!) - might be easier to piece it back together from the audit table as required.

For 13... The new queues in SQL 2005 might be helpful as the records could be asynchronously processed to avoid blocking. In SQL 2000, you could get really inefficient and use some COM calls to put things into MSMQ - yuk! Or, make sure that if your app is querying from the audit tables, it does so with no locking. Also have error checking so that if an insert fails it can be retried.
On that note - are you saying that the record may fail to be inserted due to blocking or due to primary key violations?

For the other points, yes, text fields would require that a join is done back to the original table, which might decrease performance, but if the auditing is worth it, then you pay the price.
To catch all tables as they are created (roughly), you could schedule a job to recreate all of the triggers, possibly taking some configuration info from a central config table so that certain tables/columns could be exempt from auditing.





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search