Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Nov 2009
    Posts
    4

    Unanswered: SQL Server 2008 functionality for Audit Log

    Hello

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

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

    Thanks,
    Craig
    Database Development Company San Diego

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    CDC. It takes only a few minutes to enable it and the impact in most cases should be minimal (of course you should make sure you test it). Triggers introduce lots of complexity and frequently harm performance and make support and development more difficult.

  3. #3
    Join Date
    Nov 2009
    Posts
    4
    but, how we can get data if we use CDC in the asp.net

    Craig
    Database Development Company San Diego

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Triggers are not only appropriate for this type of business logic, in my opinion they are the best solution and I have been implementing archive tables using them for years.

    Here is a link to a script you can run which will itself generate a script for adding archive tables and supporting triggers to an existing database:
    http://dl.dropbox.com/u/2507186/Scripts/AutoArchive.sql

    I have not played around yet with 2008's auditing features, but my initial impression is that it would not give me the fine-tune control that I get by implementing my own scripts.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by totaltech View Post
    but, how we can get data if we use CDC in the asp.net[/URL]
    Same way you access any other data. With a query. Usually in a stored procedure. You access CDC data with a SELECT statement.

    Quote Originally Posted by blindman View Post
    Triggers are not only appropriate for this type of business logic, in my opinion they are the best solution and I have been implementing archive tables using them for years.
    You would use triggers rather than CDC? Any particular reason other than familiarity? One of the disadvantages of a trigger for audit is that the audit all happens in the transaction, which means your server works twice as hard. CDC reads the log and it does so asynchronously so the overhead is almost negligible.

    If you only have Standard Edition then you don't have CDC so that's a reason to use triggers (or to upgrade).

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i think cdc is cool. will i jump in on version 1.0? probably not.

    I do have a question about this. am i wrong in remembering triggers use the row versioning thingie in the tempdb in 2008? does that ultimately read from the logs? same with CDC? I forget.

    Again I think CDC is cool because of it's ease of use. i have not done any performance bench marking yet, but I strongly suspect a read is a read and a write is a write and unless CDC uses fewer reads, I am doubtful how much the perf will improve. cpu improvement maybe.

    the other thing that might bother me about CDC is tieing your auditing to a specific platform.
    Last edited by Thrasymachus; 11-18-09 at 18:57.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Nov 2009
    Posts
    4
    hello

    Thank you for the information.

    Can you give some useful guiled link where i can find information about the steps how to get CDC data?

    Also if you have any statistic about the performance then it will be great.

    One of my application required this feature but he is worring about the performance and scalability.

    Thanks
    Craig

    Database Development Company San Diego

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by dportas View Post
    You would use triggers rather than CDC? Any particular reason other than familiarity?
    Familiarity and control, but I will definitely look into CDC.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by dportas View Post
    Triggers introduce lots of complexity and frequently harm performance and make support and development more difficult.
    Can I have a hit too?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman View Post
    Familiarity and control, but I will definitely look into CDC.
    I won't...just as I won't EVER employee CASCADE

    And it's only in Enterprise Edition?

    Know the cost difference?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You're such a Luddite, Brett.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    dude, why do people hate cascade delete?

    when you use it properly, it is physically more efficient and saves from having to write all those DELETE statements in the right order.

    of course you should never use it where it does not make sense.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman View Post
    You're such a Luddite, Brett.
    If ALL of these BS features were as good as a loom, I wouldn't have a problem...and, well, they mostly smoke and mirrors

    Luddite - Wikipedia, the free encyclopedia

    Why don't people like Cascade?

    Because front end developers don't know what they are doing, and neither do the users

    OOOOOPPPPSSS what HAPPEND to my data

    Better have History tables to get the sheet back
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Thrasymachus View Post
    it is physically more efficient

    OK, NOW you need to back THAT tidbit up with some facts
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •