Audit Log.

  • ben.brugman

    SSChampion

    Points: 13334

    Our SQL-server versions are not all up to date.

    For myself I am not up to date at all.

    Although I have worked a lot with SQL-server engines, I normally work with constructions which work in the standard (and express) editions of SQL-server. So my question is:

    What is needed for an Audit Log ?

    (System/version ???)

    What are the possibilities ?

    In my version of SQL-server and SSMS I can not locate the Audit possibilities. (But I do not have the Enterprise edition and I do not have a recent SQL-server).  **)

    Ben

    **)

    One of the reasons with staying behind is that I limit myself not to use new functionality, some of our systems and the systems customers use are older systems and often the standard edition.

     

     

     

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21197

    what are you trying to audit?

    you can audit data changes (you'll get lots of suggestions), maybe schema changes (that one is my speciality), configuration changes ?

    in sql server management studio there Is a "schema changes history report" - but it is not too good - I've used Database triggers and logged them to a table for DDL and permission changes.

    I might be able to help if you can give me more detail about what you are trying to do

    MVDBA

  • ben.brugman

    SSChampion

    Points: 13334

    Mainly (???)

    Data changes.

    A requirement is that something similar to SQL Anywhere Auditing is implemented. For two main purposes:

    1. To discover problems with the database and/or database software. (Debugging problems).

    2. To track changes to inspect what users have done. (Mistakes or doing something correctly in the wrong way).

    Schema are not the priority.

    Ben

     

     

     

     

     

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21197

    I think on this one you will have to write  an audit system that suits your own needs.

    It's going to be triggers that write to a log table, but trust me... expect a lot of traffic

    MVDBA

  • John Mitchell-245523

    SSC Guru

    Points: 148453

    I think Audit is available in Standard Edition (depending on version), just with reduced functionality.  Or you can use an Extended Events session, which I think is what an Audit is behind the scenes.

    John

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21197

    is CDC an option ?

    MVDBA

  • ben.brugman

    SSChampion

    Points: 13334

    Maybe I was not clear (sorry):

    Maybe the answer is: "Use the most recent Enterprise edition."

    At this moment I do not use the Enterprise edition, that's why I asked the question. If Enterprise Edition is the answer, that's ok. Only I can not check that for this moment. So the edition I am using at the moment is not the limitation, but is what I have access to. So If I/we/the customer wants audit logging and another edition is needed, that's a completely acceptable answer.

    Sorry that the question was not clear on this point.

    Ben

    (This was written on Friday 29 november, but did not hit the Submit button correctly, oops)   🙁

     

    • This reply was modified 1 week, 4 days ago by  ben.brugman.
  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21197

    HI Ben

    for my own learning, why "the most recent version of enterprise" fixes your needs

     

    MVDBA

  • ben.brugman

    SSChampion

    Points: 13334

    Prologue:

    A large number of database systems provide something like an Auditing Trail or Log.

    Sometimes primitive, sometimes difficult to read, sometimes based on data, sometimes based on command/instructions/scripts.

    SQL-server lacked (at least in older standard editions) a feature to access the audit information on both data and scripts.

    Why ?

    Mike Vessey asked Why? (At least I think he asked why).

    My answer to that : I think that there is at least some Auditing trail reading possibility in the latest (or more recent) Enterprise versions of SQLserver.

    Would this be sufficient for my situation. At the moment we are looking to change a database from SQLanywhere to SQLserver. SQLanywhere has an Audit trail possibility where scripts can be generated to 'replay' actions. Is something available in SQLserver ?

    In :

    https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15

    There is mention of an audit database engine within SQLserver, but I have no experience with this. Building up enough experience with something like an Audit trail takes time and might lead to disappointment.

    Epiloge:

    My question is, what are the possibilities with SQLserver for auditing? And more specific what is build into the engine.

    (Not building your own triggers. Not specifically switching on the profiler.)What can be used to audit trail actions which have resulted into a problem, after the problem has occurred ?

    Thanks for your time and attention,

    Ben

     

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21197

    if you are looking at data auditing

    CDC can be quite cool

    https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15

    it might not be your solution, but give it a look over

    MVDBA

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21197

    I really do think that you should look at Change data capture (CDC)

    https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15

    MVDBA

  • Jeff Moden

    SSC Guru

    Points: 995161

    Regardless of which method you plan on, if you intend to audit every data change even in "just" user databases, you should immediately at least double the disk space and all the places you backup data to.  Personally, I'd quadruple it.  Even just auditing which code made changes is going to produce a hell of a lot of data.

     

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Andrey

    Say Hey Kid

    Points: 691

    it's worth to check the content of default trace which is enabled by default on all editions of SQL server 2005+

    https://www.mssqltips.com/sqlservertip/1739/using-the-default-trace-in-sql-server/

     

     

  • Jeff Moden

    SSC Guru

    Points: 995161

    Andrey wrote:

    it's worth to check the content of default trace which is enabled by default on all editions of SQL server 2005+

    https://www.mssqltips.com/sqlservertip/1739/using-the-default-trace-in-sql-server/

    "It Depends".  On my main production server, the default trace recycles so quickly that there's no way to count on it.  There's also no way to make it bigger or tell it to use more than the handful of history traces it makes unless you sample it once every minute or so.

     

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Andrey

    Say Hey Kid

    Points: 691

    Jeff Moden wrote:

    Andrey wrote:

    it's worth to check the content of default trace which is enabled by default on all editions of SQL server 2005+

    https://www.mssqltips.com/sqlservertip/1739/using-the-default-trace-in-sql-server/

    "It Depends".  On my main production server, the default trace recycles so quickly that there's no way to count on it.  There's also no way to make it bigger or tell it to use more than the handful of history traces it makes unless you sample it once every minute or so.

    You're right, Jeff, that's why default trace  was mentioned as "it's worth to check" but not as "the only source you can rely and trust".

    Regards,

    Andrey.

     

     

Viewing 15 posts - 1 through 15 (of 16 total)

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