1

I have a requirement to log all changes in a table. I have gone through many articles in SO on data audit logging. All seem to be complex to me.

I plan to keep a copy of the table with all before update and deleted data. Is this a good idea?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
zak
  • 310
  • 3
  • 19
  • No one can say what is a "good idea" except you or your users/customers. What is the business requirement here? If you are working in a legally controlled environment where you need a full audit trail of every change then perhaps you do need to set it up for every table, even if it's more work. But perhaps the requirement is only to track one or two specific tables, and you can do something simpler. If you can make your question more precise and technical, you may get a better response. – Pondlife May 17 '13 at 14:02

3 Answers3

2

If we are talking about only one table then yes. If there are several tables then maybe. If there are a lot of tables you need to audit then no.

Reason is that the more duplicate tables you have to maintain the more complex it becomes. Also, reporting from many tables may turn out to be a difficult task.

Here are couple other ideas:

Two table design for storage: Idea is to keep details about transaction in one table (user, host machine, transaction time, table name, database name, etc) and data changes in second table (old values, keys and such)

Third party tools: There are several tools that can provide auditing at a different level . ApexSQL Audit is a trigger based auditing tool, ApexSQL Log is more advanced and can audit permission and schema changes, Idera’s Compliance manager is the most advanced and can even capture select statements.

Change Data Capture: This is built into SQL Server 2008+ but only in enterprise version.

Dwoolk
  • 1,491
  • 13
  • 8
  • yes. it is not one table. i plan to use triggers to store the changed and the deleted data (entire row) into the audit table of each of the table. all the audit tables are in the same db. if i use CDC how easy it is to show a report of changes between 2 versions of a row of data? in my approach, it is easy to show the diff between 2 versions of a row of data, as the entire row is available... – zak May 17 '13 at 11:46
  • It would be pretty easy since there are built in system functions that display this info and you can also query the audit tables using CDC schema. See [this](https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-(cdc)-in-sql-server-2008/) for more details. – Dwoolk May 17 '13 at 20:51
  • [SQL Table Audit](https://renholdsoftware.com/SQLTableAudit) by Renhold Software is another relatively well priced third party alternative. It works by generating triggers. – rpearsondev Jun 29 '13 at 18:47
0

For knowing the changes done in each and every table present in the database, I believe that you should go for LepideAuditor for SQL Server. Through this tool you are able to track the changes regarding create, alter, drop activities done on the database and SQL Server objects. It gives an elaborate view of who, what, when and where for all the operations done on these objects.

0

If you plan to keep copies of your tables, I recommend you to see Database Snapshots. It will help you to create read only copies of your database and you can easily restore and compare your tables with the snapshot.

About the SQL Audit, it is a little hard to create at the beginning, but once you have one working, the second audit will take few minutes to be implemented.

SQL Audit is a good choice for few tables. If you have more than one, use Change data capture or third party tools like Lepide or apexsql trigger.