Audit strategy on a separate database.

  • Hello Dear Friends at the forum!

    I have been reading lately a number of posts regarding an audit strategy for a table that is updated regurlarly.

    The points that I have underlined are 1) It might has impact on performance of the production database.

    2) A separate database might be a better solution.

    Therefore I thought if could use the standby server on a log shipping configuration to have the auditing taking place on that server.

    Any suggestions are deeply appreciated!!

  • Putting your audit table on a separate database has some serious advantages and won't degrade performance at all.

    Putting your audit table on a separate server with or without any form of log shipping has some dead serious disadvantages especially when it comes to performance.

    --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)

  • Thank you for the quick response!

    I was thinking that if I use a copy of the production database on a different server (standby server) then the production environment

    would not be affected by any performance nor will the production server. The log shipping standby provides a read only access to the database so I don't know if the audit on the log shipping standby server is even possible to implement.

  • Understood but, done properly, audit tables are never a performance problem nor a maintenance problem. Putting the audit tables on a separate server brings all sorts of problems to the table including (but not limited to) the cost of additional licensing, maintenance of another server (backups, disk size monitoring, security, etc), and does nothing to alleviate the problems associated with index and stats maintenance never mind the complexities of sending data to and reading from that other server.

    --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)

Viewing 4 posts - 1 through 3 (of 3 total)

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