Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2012-09-16
Medium Priority
Last Modified: 2012-10-21

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.

Question by:ezkhan
LVL 21

Expert Comment

ID: 38403797
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
LVL 35

Expert Comment

by:David Todd
ID: 38446296

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.

LVL 25

Expert Comment

ID: 38449248
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.
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

LVL 28

Expert Comment

by:Ryan McCauley
ID: 38451709
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.
LVL 35

Expert Comment

by:David Todd
ID: 38452102

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

LVL 28

Accepted Solution

Ryan McCauley earned 2000 total points
ID: 38452534
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.

Author Closing Comment

ID: 38519320
Many Thanks.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question