Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

An Audit Trail Generator Expand / Collapse
Author
Message
Posted Monday, October 10, 2005 10:29 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:47 AM
Points: 6,266, Visits: 2,029

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
Post #227686
Posted Monday, October 10, 2005 10:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 30, 2012 5:12 AM
Points: 40, 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.  

Post #227687
Posted Monday, October 10, 2005 11:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 14, 2010 12:46 PM
Points: 51, 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

Post #227703
Posted Monday, October 10, 2005 5:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 11:12 AM
Points: 184, Visits: 1,086
What happens if you need to reseed your Identity values?

Your friendly High-Tech Janitor... :)
Post #227789
Posted Monday, October 10, 2005 5:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272

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.




Post #227795
Posted Monday, October 10, 2005 6:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272

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




Post #227796
Posted Monday, October 10, 2005 6:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272

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.




Post #227799
Posted Monday, October 10, 2005 6:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272

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.




Post #227800
Posted Monday, October 10, 2005 11:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 12:43 AM
Points: 17, Visits: 46

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

Post #227829
Posted Tuesday, October 11, 2005 4:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444

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.




Post #227880
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse