An auditing mind set
2May 31, 2022 by Kenneth Fisher
I little while back I presented at Pass Data Community Summit. Specifically the presentation was Auditing your data and data access and as with each of the presentations I’ve done I not only learned a few things but I clarified something that I’d understood for a while, but didn’t really understand that I understood. In this particular case, there is a auditing mindset.
This is the idea that you can pull metadata information from all over the place. Want to know when a database was last used but don’t have an extended events session in place? Check for audit columns (i.e. a column that tells you when the row was created). It’s not perfect, but it might get you close. Or how about the query cache? You can mine that for any non-maintenance queries applied to a database. Want to know when a user last logged in? The error log can give you that!
SQL Server has information all over the place. No, there isn’t an audit automatically created to tell you the last time Bob queried the table ISawASquirrel but with some creative use of the information you do have available you might be able to give the person asking the question some sort of answer. And a lot of times they are happier with that than nothing.
SQL Studies 
Auditing & metadata are great when you are expected to be able answer questions about who changed what when and under whose authority.
The big question is: how much of an impact is tolerable?
• Is a much bigger DB OK?
• Is more RAM OK?
• Is more CPU-cores OK?
• Is more disk-space OK?
• Is less performance OK, especially under times of heavy load?
• Metadata columns can be added to a table and you immediately get fewer rows per page than beforehand. Maybe you have so much RAM that it is not a concern. Metadata columns don’t solve the problem of multiple updates though.
• Temporal Tables alleviate this problem but takes much more CPU-cycles and uses much more disk-space. Maybe disk-space isn’t a problem.
• A similar solution is to use a table which, except, for the EndDate column, only receives INSERTs and never UPDATEs. Each row has two DATETIME2(7) columns to mark the lifetime of the row. The EndDate column is left as NULL until its successor row is to be created.
This method also uses a lot of disk-space and makes tables very long on account of the history element of the table taking up so much space. This will require an regular (but not frequent) archiving system to allow the indexes to work well.
• Another option to keep tables with hot data as performant as possible is to create a sister metadata table which contains who, what, where & why and so on. This solution requires writes to another table at the same time as the DML statement to the main table and requires more disk-space.
Some weeks’ ago, a product manager wanted to know who had changed the details of a particular user when and why. I told her that we didn’t have that information, only that of the last user to make a change to that row.
I could initiate the implementation of temporal tables, I would surely need more CPU-cores, RAM and more space depending on what tables were to be affected and it would take a couple of months until it had wound its way through the release & testing process.
One needs to make those demanding such functionality aware, that auditing has a cost, and the more info they may want at any time, the more it will cost.
All very true. Auditing, like a lot of things, is a balance between what you want and what you can afford.