SQL Server Performance

SQL Server 2008 functionality for Audit Log

Discussion in 'SQL Server 2008 General DBA Questions' started by totaltech, Nov 17, 2009.

  1. totaltech New Member

    Hello,
    I have to implement a functionality for audit ( insert/update/delete )log of the each table.
    There are two option
    1) Through SQLServer 2008 Feature : I heard that in the SQL Server 2008 we haveCDC(Change Data Capture) features which can be enable on each tableand track each data changes. It insert new row into a special systemtable which we need audit log.
    2) Create a unnormalized table foreach table and handle the audit (insert/update/delete) log using thetrigger.

    Can anybody help to me to choose which options isbest and easy to manage in the future?

    Thanks,
    CraigDatabaseDevelopment Company San Diego
  2. ghemant Moderator

    IMO the 1st option is good to go with
  3. satya Moderator

    Welcome to the forums.
    How frequently the rows are inserted?
    How big will be the insertion (number of rows)?
  4. totaltech New Member

    Find the answers of your questions below:
    How frequently the rows are inserted? Ans:master has less frequency and transaction has more frequency.

    How big will be the insertion (number of rows)? Ans: I cant say no of rows as it may huge number and some of the table have less rows.
    Thanks,
    Craigs
    Database Development Company San Diego
  5. MohammedU New Member

    In your two options, CDC is the best choice for data cleanup and easy management but I believe it requires EE where as triggers don't need EE but managing might be a pain....
    CDC is better suited for ETL process than auditing...
    In my opinion you should check totally different section of SQL Server for audit...check the following BOL topics of SQL Server 2008.
    SQL Server 2008 Books Online (October 2009)
    Auditing (Database Engine)
    http://msdn.microsoft.com/en-us/library/cc280526.aspx
    SQL Server 2008 Books Online (October 2009)
    Understanding SQL Server Audit
    http://msdn.microsoft.com/en-us/library/cc280386.aspx
    http://msdn.microsoft.com/en-us/library/cc280663.aspx
    SQL Server 2008 Books Online (October 2009)
    SQL Server Audit Action Groups and Actions
  6. mcdye New Member

    On some research it appears,
    - Have to use SQL2008 Ent
    - It appears to write to the NT event logs, not sure if can be done to another table....

Share This Page