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!

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

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 ...

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?

On 7/14/10 1:32 PM, jbdhl wrote:
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.

jbdhl (jbirksdahl (AT) gmail (DOT) com) writes:
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/libr...ROT.13%29.aspx

Good luck!


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

jbdhl wrote:
Oops - I saw the word "access" in your subject line, and combined with
"Can SQL Server help with this?" I jumped to the mistaken conclusion
that you were asking about MS Access.

If you have SQL 2008 Enterprise, the auditing you are talking about
comes right out of the box. Just do a quick google for "SQL 2008 audit"
to se what I mean. Earlier versions of SQL and less costly editions
(Standard, Express, etc.) of SQL 2008 will not have this.

They use server-side traces - google and BOL will provide lots of
information about creating and running server-side traces.

Re. Banana's suggestion to use SQL Profiler, he is correct that using
Profiler like this will adversely affect performance. If you want to
roll your own, use server-side traces instead - properly configured and
filtered, they will likely have very little affect on performance. But
of course, you still have the problem of accumulating terabytes of trace
data that needs to be analyzed ... along with the risk of shutting your
server down if you run out of space (I would assume you would not want
to simply shut off the traces if you run out of space to store the
results - that defeats the purpose of compliance monitoring, does it
not?)


--
HTH,
Bob Barrows

Bob Barrows (reb01501 (AT) NOyahoo (DOT) SPAMcom) writes:
In case in one the products mention, I can add a testimony: I was
contacted by a person from that company, and he wanted to know how to
get access to the TDS specification. This was when the specification
still was under NDA. Whether they actually were able to get access to
the specification, or whether they opted to use server-side traces
I don't know.

That depends on what you trace. I know. I have caused performance issues
in production environments by running server-side traces. Filtering does
not always help. I inadvertently left a trace running which included the
Showplan event. The trace was filtered for a certain spid, but the entire
server was affected.

I don't think Showplan events are of interest in this case, but I still
wanted to add the caveat that you can cause damage with server-side
traces as well.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

Thanks for all your meticulous and insightful replies!

The thing we are strongly considering to implement (and thus the thing
that causes my questions in here) is a combined analyzer and "sandbox"
for database layouts. As mentioned, it should first of all capture all
statements send to the database for analysis and various post
processing. But secondly, we also consider making this thing a sandbox-
gateway/proxy for developers. That is, a gateway that can rewrite
queries/statements on-the-fly in order to make DBAs experiment and
play with query/schema modifications and their impact without altering
the application code (which, for various reasons, sometimes isn't
available). Therefore, this capturing/modification has to be made
before the queries actually hit SQL Server. It seems that a TDS proxy
is the way to handle this challenge.

The word "access" in subj. was just a verb, and has nothing to do with
the product "Access". :-) Also, my mentioning of pl/sql was a
momentarily absence of brain while I wrote the list of requirements
(I'm normally an Oracle guy).

Once again, thanks for your help!

jbdhl (jbirksdahl (AT) gmail (DOT) com) writes:
Which by no means is a small order. You may be interested to check
out the FreeTDS project, at http://www.freetds.org. They reverse-
engineered TDS before the specification was public. Since this is
open source, their code might give you a head start. But note that
they only have a client; I don't think they have a TDS server.

I have not tried writing a TDS client, even less a server. But having read
the specification, my impression is that the login is the most tricky
part, particularly if you want to support the Kerebors stuff. Sending
queries is not that difficult. But there certainly is a risk that you
confuse the client API if you send your responses in the wrong way!

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx