November 17, 2009 at 11:21 pm
Hello
I have to implement a functionality for audit ( insert/update/delete ) log of the each table.
There are two option
1) Through SQL Server 2008 Feature : I heard that in the SQL Server 2008 we have CDC(Change Data Capture) features which can be enable on each table and track each data changes. It insert new row into a special system table which we need audit log.
2) Create a unnormalized table for each table and handle the audit (insert/update/delete) log using the trigger.
Can anybody help to me to choose which options is best and easy to manage in the future?
Thanks,
Craig
November 18, 2009 at 12:02 am
What is it that you want to have in the log, the fact that an action took place and/or the before/after data.. Also the edition of SQL may matter..
CEWII
November 18, 2009 at 12:18 am
Thanks for your reply.
Action took place before any operation. SQL Server 2008 Enterprice edition. Even can you tell me what is the difference in different version of the SQL Server.
Craig
November 18, 2009 at 12:23 am
On 2008 Enterprise you can look at Change Tracking, Change Data Capture and SQL Audit. Haven't actually worked with any of them, so can't really give you an opinion. Play with them, see which does the closest to what you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2009 at 9:44 am
I have had a chance to play with SQL Audit and it is awesome.. But if you enable some features you get WAY TOO MUCH data and you take a performance hit.
But the genesis of my question revolved around what are you trying to get out of the auditing, the statement "Action took place before any operation" was unclear to me what you meant. So I'll ask my question more clearly.
1. Do you want to capture pre and post values of an updated record?
2. What information about the caller do you want to cpature?
3. Do you only want to capture that a caller updated a particular record and maybe some metadata about the caller?
4. What data to you want to capture on an insert?
5. What data to you want to capture on an delete?
6. In the end what are you trying to accomplish, ie what do you want to be able to do with the data you have logged?
7. Have you considered the performance impact of deep logging of every table?
After you answer some of those I will go into my thoughts on logging..
CEWII
November 19, 2009 at 5:20 am
Please, find the answers of your questions as below:
1. Do you want to capture pre and post values of an updated record? Ans : Pre
2. What information about the caller do you want to capture? Ans : All some of the selected table records
3. Do you only want to capture that a caller updated a particular record and maybe some metadata about the caller? Ans : I want to handle this information manually like who updated the record and all
4. What data to you want to capture on an insert? Ans: All the fields of table. If we have user table then all the data needs to be dumped in the table.
5. What data to you want to capture on an delete? Ans : as it is pre event then all the data before delete.
6. In the end what are you trying to accomplish, ie what do you want to be able to do with the data you have logged? Ans : when user clicks on record then full log of this record.
7. Have you considered the performance impact of deep logging of every table? Ans : that is what i want to figure it out
Thanks,
Craig
November 19, 2009 at 8:27 am
totaltech (11/19/2009)
Please, find the answers of your questions as below:1. Do you want to capture pre and post values of an updated record? Ans : Pre
2. What information about the caller do you want to capture? Ans : All some of the selected table records
3. Do you only want to capture that a caller updated a particular record and maybe some metadata about the caller? Ans : I want to handle this information manually like who updated the record and all
4. What data to you want to capture on an insert? Ans: All the fields of table. If we have user table then all the data needs to be dumped in the table.
5. What data to you want to capture on an delete? Ans : as it is pre event then all the data before delete.
6. In the end what are you trying to accomplish, ie what do you want to be able to do with the data you have logged? Ans : when user clicks on record then full log of this record.
7. Have you considered the performance impact of deep logging of every table? Ans : that is what i want to figure it out
Ok,
First, solutions I offer are likely to not be the only way to do it..
1. That really means a trigger, and you can implement it several ways, you could package up the row data into an XML fragment and write it to a central log table or have a second table that closely mimics the base table structure and records are written there.
2. All doesn't tell me much, there are many things that can be captured and not all of them useful, is this a web app or a desktop app. What is your security model, ie: does your app login and manage security or does each user exist in an AD role and access under their credentials. These answers control a lot of what is useful.
3. This question was a little different, I was looking to see how much data you really wanted to capture, it seems you are looking for more of the row data and less of just User X updated row with Key: Y.
4. I don't understand this one. The log IS the row itself, the only thing you need to capture is maybe some metadata about the insert but not the data itself. The reason I say that is that using the other logging you are wanting you can reconstruct the history of a row, all the way back to the beginning without storing the beginning row.
5. This is like #1.
6. Can you clarify a little bit. As I read this you want a full log of everything he changes?
I'm going to talk about #7 here.. I would strongly recommend against deep logging of every table, the main reason is it will take a terrible toll on performance of EVERY write because it is so diffifult to do right (and by right I mean aweful vs terrible). A better approach is to identify key tables and decide the depth to which they need to be logged, it is possible that all fields cannot changed by the user. In the cases of system maintained values it might not make sense to log the row. The level of logging that you are looking at WILL have serious impacts on write performance. It will because every row incurs at least 2 writes, the first being to the row itself, and the second being to the log record. This does not include the costs of the logic to actually accomplish this. You can do this in the way you are thinking, but a more targeted approach is perhaps better and would likely translate into more usable and less performance robbing writes.
CEWII
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply