none
Keeping Track Of History of tables RRS feed

  • Question

  • This is an interesting topic for me as I am learning more about SQL Server. I'm working for a small business that has all their work in excel and now transferring them into a real application with a backend.

    I've read many articles on this topic and a lot of them are back in 2008 and not sure what is the proper way to do keep track of changes to the tables. My easy solution that I thought of right now is have one table that keeps track of all changes to my tables. It would store: ID, table name, column name, prev value, current value, date.

    The only thing I can see wrong with this is it would only be good for UPDATE. It wouldn't do well with INSERT/DELETE(can't imagine ever using DELETE in my app but good to have).

    I've thought of shadow tables of each table but that seems like I'm storing a lot more data and duplication. What is the best practice of keeping track of history today?(I would like to avoid audit plugins if possible) I'm shooting for performance but it willing to try other methods. We will be using one machine. Not sure if it is a good idea to store the history in 2 databases in the same instance or just 1 database(would like to know more about this with performance if it makes a difference, or have them on 2 different instances). Thanks!

    Thursday, August 24, 2017 9:10 PM

Answers

  • Hi freefora11,

    Basically, you have many options to track data change in SQL server. Firstly, triggers here might not be a good option since it would most definitely decrease the overall performance which contradicts your requirement.

    To track data change with lower overhead, consider using Change Data Capture(CDC) which reads data from SQL Server transaction log, and record in a separate table.

    >>Not sure if it is a good idea to store the history in 2 databases in the same instance or just 1 database(would like to know more about this with performance if it makes a difference, or have them on 2 different instances). 

    As far as I know, changed data tracked by CDC can’t be stored in a different database and it does not affect performance as heavily as triggers. It’s better to test these methods in your DEV environment before applying to production environment. For detailed tuning performance of CDC, please refer to this link.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    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.

    • Marked as answer by freefora11 Friday, August 25, 2017 2:29 PM
    Friday, August 25, 2017 8:43 AM

All replies

  • Hi freefora11,

    Basically, you have many options to track data change in SQL server. Firstly, triggers here might not be a good option since it would most definitely decrease the overall performance which contradicts your requirement.

    To track data change with lower overhead, consider using Change Data Capture(CDC) which reads data from SQL Server transaction log, and record in a separate table.

    >>Not sure if it is a good idea to store the history in 2 databases in the same instance or just 1 database(would like to know more about this with performance if it makes a difference, or have them on 2 different instances). 

    As far as I know, changed data tracked by CDC can’t be stored in a different database and it does not affect performance as heavily as triggers. It’s better to test these methods in your DEV environment before applying to production environment. For detailed tuning performance of CDC, please refer to this link.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    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.

    • Marked as answer by freefora11 Friday, August 25, 2017 2:29 PM
    Friday, August 25, 2017 8:43 AM
  • As mentioned above, Change Data Capture should help you to track INSERT/DELETE activities in SQL server.

    Following resources can help you to gather more information about CDC :

    https://www.mssqltips.com/sqlservertip/1474/using-change-data-capture-cdc-in-sql-server-2008/

    And, https://www.codeproject.com/Articles/166250/Microsoft-SQL-Server-Change-Data-Capture-CDC

    Alternatively, using any third party like, Lepide's SQL server auditor should also be an ideal solution to work around your situation.

    Friday, August 25, 2017 9:31 AM
  • Thank you both for your replies, I'll look more into SQL Server's own audit system. Thanks!
    Friday, August 25, 2017 2:29 PM
  • There is trigger based audit also possible but there will performance will differ.

    Audit By Trigger


    Mssql installation on Centos

    Friday, August 25, 2017 2:38 PM