Prev: unable to schedule file copy within sql server sched job
Next: MSSQL implicit conversion problem
From: jbdhl on 14 Jul 2010 05:21
Currently the client app connects directly to the database, like this:

[client] <--> [database]

For various reasons we need to develop a piece of software that can
capture/log all incoming commands (queries, updates, etc.) to the
database, and analyze them in various ways. This software must sit in
between the client and database, like this:

[client] <--> [analyzer] <--> [database]

Two questions:

1) How can we make such a capturing? We need to capture *everything*
send to the database. Every single command! That includes:
a) select/insert/update/delete statements
b) table, view and index creations and modifications
c) stored procedures, pl/sql commands, etc
d) everything else

2) Given an arbitrary database command, i.e. one of a), b), c) or d)
above, how can we most easily parse it and extract which tables and/or
table columns it refers to? Can SQL Server help with this?

Any help would be much appreciated!
From: Bob Barrows on 14 Jul 2010 07:14
jbdhl wrote:
> Currently the client app connects directly to the database, like this:
>
> [client] <--> [database]
>
> For various reasons we need to develop a piece of software that can
> capture/log all incoming commands (queries, updates, etc.) to the
> database, and analyze them in various ways. This software must sit in
> between the client and database, like this:
>
> [client] <--> [analyzer] <--> [database]
>
> Two questions:
>
> 1) How can we make such a capturing? We need to capture *everything*
> send to the database. Every single command! That includes:
> a) select/insert/update/delete statements
> b) table, view and index creations and modifications
> c) stored procedures, pl/sql commands, etc

"pl/sql"? Are we talking about Jet or Oracle? pl/sql is only relevant to
Oracle. Perhaps you meant "Jet SQL commands".

> d) everything else
>
> 2) Given an arbitrary database command, i.e. one of a), b), c) or d)
> above, how can we most easily parse it and extract which tables and/or
> table columns it refers to? Can SQL Server help with this?
>
> Any help would be much appreciated!

With a Jet backend (a .mdb file) I have never seen anything that meets your
requirements, so you're pretty much talking about reinventing the entire
Access front end. You would have to write an application that does
everything that Access does as far as interfacing with the user and
implementing the user's tasks in the backend database file. And you need to
be aware that Jet is not very secure, so a knowledgeable user could fairly
easily bypass your application and work directly with the .mdb file if he
wanted.

With a SQL Server backend, the story is a little more satisfying. There are
tools on the market (Idera, Trillium, Guardium, etc.) that provide the type
of compliance monitoring you require. You should be aware of the immense
volume of data that will be generated. These tools typically involve setting
up server-side traces that write to either text files or to another
database, or both. Depending on the number of users, you can quickly fill up
a hard drive with the type of in-depth monitoring you are talking about. Are
you prepared to see your database go offline when that happens? Just
something to think about ...


From: jbdhl on 14 Jul 2010 16:32
> With a SQL Server backend, the story is a little more satisfying. There are
> tools on the market (Idera, Trillium, Guardium, etc.) that provide the type
> of compliance monitoring you require.

Thanks for your elaborate answer! You're right about the pl/sql thing,
but luckily Access is not relevant here.
We will need to perform a large amount of post processing of the data,
and possibly even modify what gets captured, so we would like to
perform the capturing ourselves, instead of using a third-party
product. There are also a number of other reasons why we would like to
do it ourselves but that's out of the scope of this thread. :-)
So, does anyone know how to perform such a capturing? How do the three
products mentioned in the above citation do it?
From: Banana on 14 Jul 2010 17:35
On 7/14/10 1:32 PM, jbdhl wrote:
> Thanks for your elaborate answer! You're right about the pl/sql thing,
> but luckily Access is not relevant here.
> We will need to perform a large amount of post processing of the data,
> and possibly even modify what gets captured, so we would like to
> perform the capturing ourselves, instead of using a third-party
> product. There are also a number of other reasons why we would like to
> do it ourselves but that's out of the scope of this thread. :-)
> So, does anyone know how to perform such a capturing? How do the three
> products mentioned in the above citation do it?

I have to admit I'm a bit lost following on what is going on - If you're
asking here, I'm inclined to assume your database is a SQL Server and
thus pl/sql couldn't possibly be relevant in that context, unless the
client app here was something akin to SSIS running a package against
Oracle database. Maybe more explanation on what the client app actually
is may help us provide an informed answer.

I'm going to assume that the database here is SQL Server:

I suppose you could use SQL Profiler to trace all statements sent to the
SQL Server. You could set the trace template to log everything and bid
the performance a fond farewell. You can then import the results of
trace into a table for analysis.

I also wonder if you're ruling out the third party products Bob already
suggested out of hand - I'm sure that it may cost less resources to use
one of those tool in-house than trying to re-invent the wheel with the
profiler and analyzing the log.
From: Erland Sommarskog on 14 Jul 2010 17:59
jbdhl (jbirksdahl(a)gmail.com) writes:
> We will need to perform a large amount of post processing of the data,
> and possibly even modify what gets captured, so we would like to
> perform the capturing ourselves, instead of using a third-party
> product. There are also a number of other reasons why we would like to
> do it ourselves but that's out of the scope of this thread. :-)
> So, does anyone know how to perform such a capturing?

If I am to take what you say by the letter, the only way to do it,
is to implement a TDS proxy. That is, a process which in one end
plays SQL Server, and in the other end talks TDS with SQL Server.

For a long time, the TDS specification was entirely proprietary, and
you needed to sign some heavy NDAs to get access to it. But this have
changed, and you find the specification here:
http://msdn.microsoft.com/en-us/library/dd304523%28v=PROT.13%29.aspx

Good luck!


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: unable to schedule file copy within sql server sched job
Next: MSSQL implicit conversion problem