Post reply

How can I monitor updates to a database?

  • September 19, 2008 at 3:23 am

    #154739

    I am being asked if we can monitor updates that are being performed against a database? As am more familiar with SQL2000 and don't believe this is possible.

    Is it possible with SQL2005?

    Are there any third party applications that can do this?

    I understand that this would normally be done at application level and hence, not a feture of SQL?

    Many thanks for any feedback.

    Jim

  • September 19, 2008 at 3:40 am

    #874082

    What exactly is it you're being asked to monitor?

  • September 19, 2008 at 3:51 am

    #874091

    I am trying to establish what the business requirement is.

    At the moment their request is to monitor ANY updates made to the application database. I'm am assuming this may be for some auditing purpose.

    Or security reason so we can monitor what changes are being made my who?

    The application dosent offer this and so I am being asked if I can offer some kind of solution.

  • September 19, 2008 at 3:56 am

    #874094

    Ok, the business requirement is...

    "well its more any changes that are made to any table what so ever at any stage. basically a log of what, who and time of any changes which we keep for a defined period of time "

    Any ideas please???

  • September 19, 2008 at 4:14 am

    #874099

    Is this a third party app, an in house app using sp's for updates inserts and deletes or a scary inhouse app using all kinds of passthrough and general nastyness?

  • September 19, 2008 at 5:05 am

    #874116

    Andrew, we dont have anything at the mo, I was hoping someone here may already be doing this and be able to advise on a third party product or SQL solution.

    I have found the following application which seems to be able to do what is required (and a lot more besides)...

    Anyone used this Quest Change Director??

    Jim

  • September 19, 2008 at 6:15 am

    #874150

    I've used the demo version of Lumigent Log Explorer in the past and seems like it'd do what you need. Someone else on the board may have used this product and can offer more insight. This product reads the Transaction logs and can tell you who changed what and when. It also provides the ability to back out those transactions if necessary

  • September 19, 2008 at 6:22 am

    #874155

    If you're going to build it - then you can simply make an addition to your CRUD procs to insert into an audit table;

    spTableInsert ( ... params)

    INSERT table fields ... params

    INSERT tableAudit fields + User ... params, SYSTEM_USER

    with a similar tracking for updates and deletes - quicker than using triggers as you're not having to access the transaction log for the INSERTED etc tables. Also means that the app has to log in to SQL Server as the user - NOT just using a generic login for everyone (which would apply to anything you'd be looking at for sensible audit).

    Drawback with this is;

    a) Numbnuts app developer not using the CRUD layer if you don't set up secirity to prevent access to base tables

    b) You've lost auditing for anything where you as DBA access tables directly - which may be unacceptable from a business perspective and is certainly unacceptable if this is related to things like SOX of BASLE

    In which case, the alternative would be to use triggers to populate the audit tables

  • September 19, 2008 at 6:22 am

    #874156

    Many thanks for that.

    As we have used Quest products in the passed it looks like we will be going down that road.

    Thanks for all the help guys.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply