AmitJain001
asked on
SQL Server Auditing and Maintaing Logs
Hi,
We are currently in process of building a large ERP solution and one of the requirement is to generate a report (We are using SSRS report Server) of who did what. Management wants a capability to be able to generate a report that could tell them x person created n records in Entity1, m records in Entity2 and so on.
So the most logical solution was to add a Log table for each entity and build a trigger for every Insert Update and Delete.
However, the same information can be extracted from the Transaction Logs as well. Atleast that is an idea I am working with. Can someone point me in the right direction here, as I have been entrusted to explore the possible solutions (Tools that can do that). The idea I am working on is that we should not be required to re-invent the wheel and there should be SQL Server tools that should be able to do that and much more.
Any help in this matter will be much appreciated and I understand, this is a subjective question but I will try my best to do the justice with most suitable response(s).
Thanks in Advance.
Amit
We are currently in process of building a large ERP solution and one of the requirement is to generate a report (We are using SSRS report Server) of who did what. Management wants a capability to be able to generate a report that could tell them x person created n records in Entity1, m records in Entity2 and so on.
So the most logical solution was to add a Log table for each entity and build a trigger for every Insert Update and Delete.
However, the same information can be extracted from the Transaction Logs as well. Atleast that is an idea I am working with. Can someone point me in the right direction here, as I have been entrusted to explore the possible solutions (Tools that can do that). The idea I am working on is that we should not be required to re-invent the wheel and there should be SQL Server tools that should be able to do that and much more.
Any help in this matter will be much appreciated and I understand, this is a subjective question but I will try my best to do the justice with most suitable response(s).
Thanks in Advance.
Amit
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I agree about CDC. A big problem with using a TRIGGER is that more than likely the user is not available to the TRIGGER.
One caveat with CDC is that it is Enterprise Edition only.
One caveat with CDC is that it is Enterprise Edition only.
Good call - I didn't realize it was an Enterprise-only feature.
As Anthony mentions, whatever user is making the DML change will also need rights to the place the trigger is writing the data tracking entries. Maybe not a big deal, but something to consider.
As Anthony mentions, whatever user is making the DML change will also need rights to the place the trigger is writing the data tracking entries. Maybe not a big deal, but something to consider.
ASKER
Thank You Guys. I was initially inclined towards using a Third Party Tool such as ApexSQL LOG or PowerBroker Database for extracting information from Transaction Log but it seems SQL Server 2008 R2 has simplified extracting information from Log files using CDC.
Will definitely explore more in this regard. Thanks Ryan, yours is the most valuable feedback and certainly gives me something to dive into.
Will definitely explore more in this regard. Thanks Ryan, yours is the most valuable feedback and certainly gives me something to dive into.
ASKER
Thanks,
Amit