Re: Auditing sql server at runtime

SQL Server native tools don't do any port sniffing. There are 3-rd party
tools that do that. Many 3rd-party audit tools also use SQL trace behind the
scene. Note that as long as you don't do statement level tracing (i.e.
tracing every single statement inside a proc, inside a function call, etc),
the load is rather mimimum.

Linchi

"Manpreet Singh" wrote:

Its ok , but I wanted to do it in runtime the approach is fine but it
introduce additional latency And I don’t want to pot extra burden on sql
server instance (if possible). Profiler traces will not able to give me
runtime information. Is there any way through which I can find out which
queries are being executed against my Sql instance? Like runtime auditing or
something?

Manpreet Singh
http://crazysql.co.nr
http://crazysql.wordpress.com
MCITP – DB Admin, DB Dev SQL Server 2005
MCTS – MOSS 2007, SQL server 2005


"Russell Fields" wrote:

Well, using SQL Trace you can do the following:

1. Use SQL Profiler to define your trace, then script it out. (Because you
state that you want to see what is being submitted, I would suggest starting
with tracing only SQL batches, not individual stored procedures, statements,
etc.)
2. Create a SQL Agent job to run that trace, paste the script into it. (You
may need to do a couple of manual edits of the script, since file name (if
you trace to file) and end time do not make it into the script.)
3. Create a stored procedure that will start the SQL Agent job.
4. User sp_procoption to mark the stored procedure for autoexecution when
the SQL Server is restarted.

You can send the trace to a SQL Server table, but that adds extra load to
the server. Or you can trace to files. The file size setting will limit
how big a single file is, but the trace can roll over to numbered files.
So, once a file is complete, you could import it to a table for review. (Or
even import it to another server.)

SQL Profiler is just a user interface. Once you automate the trace, you may
not need to use profiler again for this trace.

RLF



"Manpreet Singh" <ManpreetSingh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:13AEF04D-3BF2-49B6-96A8-2DA504C02ED3@xxxxxxxxxxxxxxxx
Is there a any way through which I can intersect the Sql server 2005
traffic?
I know I can use profiler or profiler trace to achieve this task, but the
problem is that, I wanted to do it programmatically and at run time. eg
like
capturing 1433 port data and getting queries out of them etc . Please help
me
as I wanted to create a application which captures and log all the queries
which are fired against sql server 2005

Manpreet Singh
http://crazysql.co.nr
http://crazysql.wordpress.com
MCITP – DB Admin, DB Dev SQL Server 2005
MCTS – MOSS 2007, SQL server 2005



.

Relevant Pages

  • Re: Running profiler on busy 8-cpu Sql Server
    ... I always start with a light trace and add more detail as I go along to ... SQL Server MVP ... > I think it's also worth saying that you should introduce Profiler ... > SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: URGENT deadlock question
    ... > Profiler to view a trace, SQL Server dumps rowsets across the network to ... > SQL Server doesn't feel like it can keep up with all the activity. ... > server-side trace to a trace file guarantees you'll see everything. ...
    (microsoft.public.sqlserver.server)
  • RE: SQL Server 2000 Profiler (Bug?)
    ... could trace many Error 208 by using Profiler when you using SQL Server ... Enterprise Manager navigate around in the Server.Have I fully understood ...
    (microsoft.public.sqlserver.server)
  • RE: Insert Performance Degradation
    ... Use Profiler to trace the statements sent to sql server. ... duration template and filter for duration greater than, let us say, 1000 ...
    (microsoft.public.sqlserver.programming)
  • Re: URGENT deadlock question
    ... >> network to the Profiler client, but there is no guarantee you'll see ... >> everything if SQL Server doesn't feel like it can keep up with all ... Just make sure the trace file is created on a local ...
    (microsoft.public.sqlserver.server)
Loading