asked on

Auditing SQL Server 2005 Standard Database on Insert Update and Delete operations

Hi,

For one of the project I am required to record auditing for all the additions and updates. The version of SQL Server in use is SQL Server 2005 Standard. There is no possibility to upgrade it to SQL Server 2008 R2 Enterprise Edition (So no option to use Change Data Capture Feature). Now, I am left with only one option i.e., to use triggers after insert and after update.

My concern is the performance of the database system as there will be tens of tables involved in multiple users writing to database and periodic and overnight jobs will write to database as well.

Can you please suggest some possible solution to the issue. Is there only one option to use trigger to achieve the purpose or there are some alternatives to it. Your advice, suggestions are appreciated.

Thanks.

pinaldave

This article will cover the most spectacular feature of SQL 2008 – Policy-based management and how the configuration of SQL Server with policy-based management architecture can make a powerful difference. Policy based management is loaded with several advantages. It can help you implement various policies for reliable configuration of the system. It also provides additional administration assistance to DBAs and helps them effortlessly manage various tasks of SQL Server across the enterprise.

1 Introduction
2 Basics of Policy Management
3 Policy Management Terms
4 Practical Example of Policy Management
4.1 Exploring of Facets
4.2 Create a Condition
4.3 Create a Policy
4.4 Evaluate a Policy
4.5 Fix Non-complying Policy
5 Summary
http://blog.sqlauthority.com/2009/06/30/sql-server-2008-policy-based-management-create-evaluate-and-fix-policies/

David Todd

Hi,

The key thoughts for triggers are:
Keep them small as they are part of the implicit transaction.
Write them to be able to handle multiple rows - don't assume that the trigger will be fired at the row level - a bulk update/insert/delete will fire the trigger once and inserted/deleted tables will have more than one row.

HTH
David

TempDBA

the place where I work depend on triggers a lot. Believe me, till now, there are not much issues even though it is an OLTP environment with 1000 + users working simultaneously. You can create triggers and monitor the performance in test environment. It shouldn't be an issue unless a huge batch is applied once.

Ryan McCauley

Have you considered using C2 Audit logs? It may be a bit overkill for what you're looking, but it will audit all the statements executed on a server for review later (not at the table-level, but any DML statements will be captured). Take a look here:

http://www.sqlservercentral.com/articles/Monitoring/basicsofc2auditing/1547/

There are some caveats - you have to make sure to backup and archive those trace logs as the SQL Service will stop if it runs out of disk space and can't write more audit files, and there may be some performance impact. However, since it's native tracing to SQL Server, the performance impact will be minimal (or as minimal as possible for a process that records everything!), so it might meet your needs.

David Todd

Hi,

Doesn't turning on C2 auditing basically kill a servers performance?

Regards
David

ASKER CERTIFIED SOLUTION

Ryan McCauley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ezkhan

ASKER

Many Thanks.