Tips on logging updates and deletes of data, executions of procs and alterations to objects in MSSQL RRS feed

  • Question

  • Hi,
    I am working on improving the way my team is able to create audit trail and review changes made to data and objects within MSSQL. We will mostly be using MSSQL 2016 or higher. Apologies for the generic questions but I am hoping to get some high-level guidance on established techniques for logging changes to data, such that they can be easily reviewed, executions of procedures such that the data used at the time of execution can be identified, and changes to objects and routines. I am primarily looking to not use software/tools outside of MSSQL to do this, because it can lead to difficulties when working within our client's own infrastructure.
    I realise that this is a very large topic and that I should provide much more info if I expect a useful answer. Without going in to too much detail - we use MSSQL to support analytics of very large sets of financial transactions data. We exchange the data with Python, Tableau and proprietary tools which enable workflow, review and tagging of data. We want to be able to efficiently record when data is exchanged between platforms, what has been executed to achieve this and by whom. We also want to be able to record changes to data in tables which contain data that drives logic or defines scope e.g. a reference table which groups transactions or a table of string values which are matched against source data.
    Please provide any suggestions for options or resources which cover this topic.

    EDIT, following Cathy's helpful response, below.


    having looked through the provided links I have found that a database audit is perfect for capturing executed code, which is a partial solution to my overall set of requirements.

    To properly improve the way my team is working, I also need to stop people from creating multiple versions of tables with significant duplication, which primarily happens because they change some logic which transforms a small amount of the data and do not have a simple solution for maintaining versions of data. I am now looking in to the pros/cons of the following solutions with a spec that I need to be able to look at the database audit and see when a procedure was executed then see the contemporaneous data. I want the process of identifying, reviewing and reverting to the contemporaneous data to be as simple as possible and not cause a significant blowup in disk space requirements (given that we should be preventing duplication of data, disk space should actually be freed up). Any expert advice or wisdom would be greatly appreciated.

    user-defined table triggers e.g. https://stackoverflow.com/questions/17546601/best-way-to-implement-an-audit-trail-in-sql-server

    Change Data Capture https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/bb522489(v=sql.105)

    and Temporal Tables https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017

    • Edited by JJHolmes Tuesday, February 25, 2020 2:22 PM Additional requirement
    Tuesday, February 18, 2020 2:53 PM

All replies

  • Hi JJHolmes,

    Please check if below links could help you.
    SQL Server Audit (Database Engine) 
    SQL Server auditing with Server and Database audit specifications.

    Best regard,

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, February 19, 2020 9:11 AM
  • Thank you so much, this looks like the obvious solution I suspected existed but needed a nudge towards.
    Wednesday, February 19, 2020 9:00 PM