Home Forums Article Discussions Article Discussions by Author Discuss content posted by Roy Ernest What, When and who? Auditing 101 RE: What, When and who? Auditing 101

  • December 2, 2011 at 9:06 pm

    #1416240

    tymberwyld (11/28/2011)

    When SQL 2005 came out, I cringed at the way SQL Notification services worked and basically threw it out the window as an option for change tracking. However, I re-tooled the same concept using SQL Service Broker, and using a simple, generic Trigger, was able to audit ANY table's changes (real-time, with both previous and new values) asynchronously and we were able to audit all databases into one or many audit databases if we wished. Views were created against the meta-data to make "pseudo" tables which resembled the originals, so comparisons could be made. Anyway, it's worked for years and was completely scalable. If we wanted to move the auditing database(s) to another server, it was as easy as "changing the channel" on the service broker, no messy Trigger or Proc changes.

    That would make one heck of a nice article. Any chance of you taking that on?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)