December 2, 2020 at 10:56 am
I'm currently looking to design some auditing on my SQL application and am wondering what would be the best way.
Basically when a user Inserts or Updates I would like to capture what the previous value was and what the new value is. This would be across about most of my tables and could be multiple columns affected.
I have a couple of solutions I can think of
1 - Create a trigger on each table for Insert/Update & delete if needed to insert into a history table
2 - Create multiple stored procedure that will insert into a history table with previous/new values.
3 - Create a Stored Procedure which is dynamic and have the application pass me the table they are changing and what the condition is passed and then add it to a new audit table table which somehow i'll design to be dynamic.
This data will need to be kept with no archiving (at this stage) and used as a report when business wants to see what's happened. So i'm also exploring the ability to store it in 1 new Audit table instead of multiple history tables.
Any Thoughts or ideas on the above??
December 2, 2020 at 11:29 am
Since you posted in the SQL 2019 forum, I would say look at using temporal tables. What your after sounds a lot like CDC but switching that on along with doing triggers on each and every table is going to be a pain. Temporal tables track row history so would say that they would meet requirements, but you would need to double check in case something is needed that hasn't been mentioned.
December 2, 2020 at 11:53 am
Hey, thanks for the quick reply.
I posted in 2019 but I'm actually running 2017 standard which is still supported.
I did also think of CDC as an option but didnt think it matched my requirement when i looked into it further. I could be wrong though and may be the way to go.
I'll look into your link as im not 100 percent sure of it.
December 2, 2020 at 12:07 pm
I've just read and if i understand it correctly, it needs to have 2 additional columns added to each table (i.e. start/end date) which is something I probably would like to avoid especially as it need to happy for primary tables as well.
December 2, 2020 at 12:18 pm
Yes still supported in 2017 and 2016 are temporal table and yes they need extra columns adding, going around changing existing tables to become temporal tables shouldn't be that much of a headache if development has been following standard like using column names for CRUD operations and not relying on SELECT *, or INSERT table VALUES.
Ok its a bit of work to add primary keys to tables which don't have them, add the columns set the dates but in the constraints and set it as a temporal table but it is all very much doable.
CDC uses replication technology under the hood so it needs a Primary Key or a unique key at least.
Other than that your into creating triggers or doing some funky custom auditing which is going to be a right administrator nightmare to say the least and cause slowdowns in application response time as everything has extra work to do.
December 2, 2020 at 3:04 pm
I'd say use CDC as that will require no changes to any existing table that already has a unique index. Presumably the vast majority of your tables already have at least one unique index. That said, the existing system procs for use with CDC are, well, frankly, not good. In fact, they're so annoying and bug prone, I've developed my own that are much easier and more intuitive to use. Bug prone in that they are so finicky you have to be perfect about everything you send into them ... and people just aren't that perfect all the time.
Instead, using temporal tables, and thus having to adding all those columns to the table, will indeed cause massive performance changes. It's extremely likely to cause page splits all over, so you'd have to rebuild all the tables. That alone could cause a lot of disruptions to normal database activities.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 7, 2020 at 12:23 am
Thanks for the information, I've decided to not use CDC for that reason and for the temporal tables at this stage i've also kept this as an option in the event i cant get anything else working.
I'm thinking of writing my own scripts to do this since the system isn't that highly transactional and also possibility of going back to triggers which shouldn't be that much of a performance hit for what it will do.
December 7, 2020 at 3:36 pm
I prefer to use triggers. We have a table named AuditLog that stores the table name, date and timestamp, who changed it, the previous value, and the new value.
Once you develop the trigger on one table it is easy to replicate to other tables.
December 7, 2020 at 9:37 pm
Do you mean a separate row for every column that changed? That's massive overhead. And if you get the column name(s) dynamically, that's really insane overhead.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 10, 2020 at 12:51 pm
Have a look at the DataVault pattern. It allows you to store temporal changes in a very efficient manner as you can split the columns in a table into fast and slow changing Sattelites which means you don't fill up your production database with a new row of 100 columns just because someone updated a single field. It works very well as a staging environment for data warehouses too as you can (in theory) recreate the source database at any given moment in time and replay transactions into the DWH if the business requirements change. It can parallel load too so it is quite fast and does not put any write locks on your production system.
Don't use triggers if you can help it, they can cause funny things to happen if you are trying to return the last record ID to be updated and they can be disabled which completely defeats the object of audit tables.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply