0

Man... this feels like a broad question, so apologies in advance. The word "auditing" seems to have some depth in the DBA world, and I may not be asking the right question when I google.

I'm trying to get my head around how to do some simple "auditing" in SQL Server. I have a subset of user tables for which I want to track changes (INSERTS, UPDATES, and DELETES). I want to know who made the change, when, and what the before and after results were (in the case of UPDATES).

I've done this in the past with triggers that would write to an "audit" table, and this provided a basic but intuitive way for me to quickly zero in on any concerning changes in the database.

For some licensing and support reasons, building triggers to capture this information won't work in my current case. I'm investigating other options (SQL Server features and commercial applications). I'm running SQL Server on AWS RDS, and it looks like SQL Server Auditing is supported. Does the output of SQL Server Audit lend itself to quick and easy consumption? Like, can I easily query for Table ABC, when/who/what was the last time a certain ROW was changed? Or alternatively is what I'm describing more in line with a commercial product (like Apex SQL)?

  • How will the user be interacting with this table? Through direct T-SQL queries, or a front-end application? Or do you need to cover both cases? How authoritative do you need this audit to be? IOW, is this for regulatory compliance? – alroc Jan 27 at 14:30
  • @alroc thanks for the questions. User interaction would be solely through T-SQL queries within SSMS. For authorativety (?), this is not for compliance, but merely internal management. E.G. if the [price] for apples changes from 3.00 to 3.50, I want to be able to figure out that user:Joe made the change at 20200125 23:35:00 so that I can send him an e-mail and ask him why. – ChipWI Jan 27 at 17:05
  • I don't understand why triggers won't work for this use case, then. There shouldn't be any SQL Server licensing impact there. The downside is that a knowledgeable user, if they have enough access, can disable the trigger or delete from the audit tables to cover their tracks. – alroc Jan 27 at 17:14
  • @alroc... it's probably laziness on my part. When the software vendor applies updates, they update the database schema that they "own". If I create a trigger on one of their tables, I have to create it in their schema. When they apply an update, they would overwrite any edits that I've made to their schema (e.g. adding a trigger). I'd like to avoid having to keep track of all the changes I've made to their schema. – ChipWI Jan 27 at 17:21
2

Since you are using SQL Server 2016, you are having advantage of using temporal tables. It seems like you need to have combination of things for addressing your objective. As far as tracking changes in terms of insert/updates and deletes are concerned, this can be very much addressed by using temporal table. This will also serve your second question i.e. what was the value before. Current value is anyway stored in the subject table.

As far as your first question is concerned, I think you need to have auditing enabled on subject table to track who did mentioned operation i.e. insert/delete/update.

Lets understand temporal table a bit here:

Temporal tables - not to be mistaken with temporary tables - were introduced as a new feature in SQL Server 2016. Temporal tables, also named system-versioned tables, allow SQL Server to automatically keep history of the data in the table.

A system-versioned table allows you to query updated and deleted data, while a normal table can only return the current data. For example, if you update a column value from 5 to 10, you can only retrieve the value 10 in a normal table. A temporal table also allows you to retrieve the old value 5. This is accomplished by keeping a history table. This history table stores the old data together with a start and end data to indicate when the record was active.

These are the most common use cases for temporal tables:

  • Audit --> With temporal tables you can find out what values a specific entity has had over its entire lifetime.
  • Slowly changing dimensions --> A system-versioned table exactly behaves like a dimension with type 2 changing behavior for all of its columns.
  • Repair record-level corruptions --> Think of it as a sort of back-up mechanism on a single table. Accidentally deleted a record? Retrieve it from the history table and insert it back into the main table.

You can read more about temporal tables at below links with examples:

You can enable auditing at database level to capture insert/update and delete operation. This would capture who performed the mentioned operation with some other basic details like at what time this was done.

There are few disadvantage to this auditing:

  • SQL Server Audit uses the resources of the audited SQL Server itself, which can degrade performance
  • It is difficult to comprehensively manage multiple instances and consolidate the audit data.
  • There is a lot of wet-work involved in managing, analyzing and archiving audit data, whether in a file or log, and necessitates manual effort for importing, archiving and reporting.
  • This feature isn’t available in the standard version of SQL Server until SQL Server version 2016.

In your case, if you combine both these concepts, this should serve your purpose.

Hope above helps.

  • 1
    Thanks... this sounds perfect. Is there a way to off-load a temporal table to another database? I imagine that the temporal tables COULD get huge. In my case, querying these tables would be an infrequent and ad-hoc event... so having these on another database as flat tables that could be queried by the source table's primary key value (e.g. show my all the records in the FRUIT history table where the PK='apples') would be sufficient. – ChipWI Jan 27 at 17:17
  • Glad that above helped. You can define retention period for the auto-deletion of data from history table. Alternatively, you can create a SQL job to move data from history table to another database basis on date difference condition. This will keep your history table growth under control. – Learning_DBAdmin Jan 28 at 6:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.