This project has moved and is read-only. For the latest updates, please go here.

SQL Server Change Tracking vs AuditTriggers/AuditLog

May 10, 2013 at 5:39 PM
I've had to disable/drop some audit update triggers due to incredible overhead. I've researched a mechanism called 'Change Tracking' in SQL Server. Does GPA have any information as to which to use?
May 24, 2013 at 3:58 PM
Since we generically support multiple databases a native support mechanism was not as feasible - that is, if we need to consistently look in the "AuditLog" table to query change logs from a coding perspective regardless of database type we couldn't use native mechanisms.

The nice thing about change tracking in SQL Server is that is doesn't seem to add triggers to perform needed functions (if it does they are not visible anyway) and I would suspect that the operation would be faster, however you will have to be able to enable column based change tracking in order to capture all changes - this might end up being equally as slow - you'll just have to test. It does offer an option for automatic curtailment of the log based on time range which is nice.

When we looked at this initially we didn't see in the documentation that it logged "who" made the change - perhaps you can test and see if it does. Without this information you will not have a CIP compliant change log so this will be the most important bit of information to verify. This would get tricky if the "database user" did not always match the "openPDC Manager user", i.e., you were not using active directory with pass-thru authentication to your SQL Server database (i.e., SSPI style connection).

Thanks,
Ritchie
May 24, 2013 at 4:15 PM
I enabled Change Tracking and removed the Audit triggers because large scale update took several minutes to complete! Without the triggers, but with Change Tracking, updates were very fast (instantaneous).

I believe column tracking is automatic. Seems that you enable the database for change tracking, and then each table that is desired. A primary key is required on the table for such tracking.

Might be a lot of work, but sets of views could be created to examine the 'AuditLog', however implemented. When Change Tracking is used (SQL Server), or LogMiner is used (Oracle), the views should return the same data that would be seen if AuditLogs and associated triggers had been used. I created a few queries of this sort, but as usual Microsoft's documentation is so poor that I gave up anything exhaustive.

If the who/when/what aren't stored, including at the column level (Change Tracking/LogMiner/AuditLog), then the vendor should be severely chastised - or worse!