SQL Trace is a feature in SQL Server to track and audit events occurring in your SQL instance. The events are recorded in a trace file which is by default stored in \MSSQL\LOG directory.
While creating a trace, you can specify the events you want to record by making changes to the Trace Properties dialogue box. Under the General tab of the dialogue box, you can specify information such as the trace name, the storage location of the trace file, trace stop time, etc.
Now, the traces will be collected by incorporating the filters in the specified storage location.
The trace files can be read using fn_trace_gettable function that displays the recorded events in a table format. The records can be aggregated and searched using SQL queries. The trace files are voluminous and manually writing queries to extract fields of interest for generating an audit trail can be a tedious process.
You can also use SQL Profiler to view trace files.
SQL Profiler provides a Graphical User Interface (GUI) to access your trace files by storing, retrieving and auditing stored records. SQL Profiler's main functions are to monitor SQL Server activities, provide insights on the overall health and identify elements that affect the performance of your SQL instance.
SQL Profiler provides visibility into your trace files by allowing you to save trace records and compare it with successive records to troubleshoot any operational issue (viz. a slowly executing query) and fine tune them to enhance performance.
You can store records only up to a specified data limit (say 20 MB) in a single trace file. Trace files can't be retained for long time frames as they are rolled over. There is also a constraint on the maximum number of trace files that can be retained (say 5 files). Once the maximum is reached, the oldest trace file will be wiped off from the memory. This isn't favorable for auditing as you are expected to retain records over prolonged time periods. SQL Profiler can be used only when traces are configured for different activities individually. Analyzing the trace files using SQL Profiler manually is a time-consuming process.
Microsoft has deprecated SQL Trace and SQL Profiler along with Microsoft.SqlServer.Management.Trace namespace and the SQL Server Trace and Replay objects. The Trace and Profiler features can possibly be removed in the upcoming versions of SQL Server.
SQL Servers generate voluminous logs and they should be monitored closely to ensure security and compliance. Instead of relying on SQL Trace and SQL Profiler for auditing, you can use a log management solution that can effectively collect, parse and analyze logs from your SQL Server.
EventLog Analyzer is a holistic log management solution that collects SQL Server logs and analyzes them efficiently. It can produce intuitive and comprehensive reports for DDL, DML, Server account and security attack activities. Some of the audit reports produced by EventLog Analyzer include Column Modified, Delete Operations, Security Changes, Permissions Information, Last Login Time, Logins Information, Unauthorized copies of sensitive data, Account Lockouts, Storage media exposure, SQL Injection, and Denial of Service. Alerts can be configured for anomalous activities and you can be notified in real-time through email and SMS to mitigate threats and prevent attacks. Check out other reports provided by EventLog Analyzer.
Zoho Corporation Pvt. Ltd. All rights reserved.