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


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.

Who is Participating?
Ryan McCauleyData and Analytics ManagerCommented:
Not any more than running a SQL Profiler session, in my experience - certainly, it would have less of an impact than putting a trigger on every table to capture data change activity. We use a product from Idera called SQL Compliance Manager to capture database activity, and this is essentially what it does - runs a trace on the server and pumps all the statements back to a centralized collection server (whereas C2 auditing writes it locally). We've never noticed an issue with performance on any monitored servers, especially since there's no network overhead involved.

One potential performance killer is if you're doing the C2 logging onto the default database location, which in my cases is the same physical disk as your SQL Server data - you may not have a choice, but C2 writes logs into the default data directory, and to my knowledge, that's not configurable. To compensate, the best thing to do would be to set the default data directory to a dedicated disk where logs can be captured - that way, the C2 logs have the LUN all to themselves (if you're on a SAN).

If you're looking to capture all the DML activity on every table in the database, I think it may be your best option - there's no way to do this with zero overhead, but only testing it would tell you for sure.
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
David ToddSenior DBACommented:

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.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 McCauleyData and Analytics ManagerCommented:
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:

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 ToddSenior DBACommented:

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

ezkhanAuthor Commented:
Many Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.