Designing a way to audit changes to tables

Question

I want to be able to audit activity that is performed on db tables (updates, inserts etc). What would be the most effective way to design this? One solution I have in mind is to have a audit table for every table and use SQL triggers to do a insert the previous value before the table is updated with a new value.

Answer 1

For the application side, I would look at ORMs to help you with this, if you are still in the design stage. I would look and limiting as much as possible, users having access to the raw data tables.

• Nhibernate Versioning
• Entity Framework Audit Log

FYI, depending on your situation this may be at too high of a level, since you may want to keep tabs on every change that someone makes in the DB with any tool.

Answer 2

SQL Server from version 2008 include a built-in option for what you are looking for. It named CDC or Change Data Capture. You can get more information in the BOL (officially Book-On-Line) in the first link or from the blog in the sec link:

• https://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx
• https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-(cdc)-in-sql-server-2008/

There are other options as well that you can use. but in order to choose the best option for you we need more information on your system. It will help if (1) you can describe your system architecture, and (2) what information do you want to store in your auditing (do you need the user name or just audit the data it self), (3) do you need to keep history or only get the events, (4) is this temporary needs or part of the database structure (CDC will create elements like triggers and is not fits for temporary audit, for this you better use extended event or profiler). CDC is very good option for audit data changes, if it fits your needs.

Answer 3

SQL Server, from version 2008, also includes SQL Server Audit. There are database-level audit actions that enable you to audit those operations on tables. (Please note that database-level auditing is available only on Enterprise and Developer versions. See blog on SQL Server Audit Support in Different Editions and Versions.)