Custom Auditing

If auditing had no cost, we'd always audit everything, but it can have major performance costs, so the principle is: Audit only what you have to audit for each system, depending on how much protection is necessary for the data in each system. 

SQL Server Profiler can be great to use for a little while to capture data for troubleshooting, but it's less than ideal for long-term auditing. That was resolved in SQL Server 2008 with built-in, first-class auditing, but in SQL Server 2005, the best solution is server-side traces.

Creating a server-side trace basically just means using T-SQL to create and control a trace instead of using Profiler. The catch is that each trace imposes two forms of cost. First is the cost to evaluate every single event in order to determine if the event is one that has to be recorded. The second cost is the actual process of recording. So, if you only have a trace recording failed logins, and there's never a failed login, it will still have to check every single event, but it won't have to write anything. On the other hand, if you have C2 auditing turned on, it will not only have to evaluate every event, it will have to record almost every event. That's a good way to fill up hard drive space in a hurry.

SQL Server 2005 introduced a relatively light-weight default trace (light-weight because it's server-side and records relatively infrequent events), and it serves the basic needs of both performance troubleshooting and security forensics. If you need to audit more than is monitored by the default trace, and since the default trace can't be modified, some folks suggest just adding an additional server-side trace. I don't.

If you add a second trace to run 24/7, you're requiring SQL Server to evaluate every single event twice. A better solution, in my assessment, is to disable the default trace and replace it with a server-side trace that records everything you need to record. I've attached a script that will help you replace the default trace, but obviously, you'll have to tweak it before you run it, so it will record exactly what you need recorded and no more.

If you need to record events that are much different from the default trace, an easy way to do so is to use Profiler to define your trace, then export it, then tweak that. See the comments in the script for more info.

Replace default trace with custom trace.sql

Comments

• Anonymous
  January 01, 2003
  Hi, How to continue the trace if SQL Server restarts

• Anonymous
  January 01, 2003
  I mean to restart the trace with file rolleover. Thanks a lot.