Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

less intrusive/high performance auditing

7 posts in Product Futures Discussion Last posting was on 2002-03-12 14:10:07.0Z
George Saylor Posted on 2002-03-07 12:41:12.0Z
From: "George Saylor" <gmsayloriii@email.msn.com>
Subject: less intrusive/high performance auditing
Date: Thu, 7 Mar 2002 07:41:12 -0500
Lines: 5
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <iqfWVVdxBHA.214@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: tow9dhcp209.towson01.md.comcast.net 68.33.9.209
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:738
Article PK: 94265

Many times we have considered auditing and shot it down due to (perceived)
performance overhead. Don't know if it is possible but it can't hurt to
ask...


Sethu Posted on 2002-03-09 03:07:51.0Z
Message-ID: <3C897C87.59BBBDA4@sybase.com>
Date: Fri, 08 Mar 2002 19:07:51 -0800
From: Sethu <sethu@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
To: George Saylor <gmsayloriii@email.msn.com>
Subject: Re: less intrusive/high performance auditing
References: <iqfWVVdxBHA.214@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 15
NNTP-Posting-Host: 10.22.91.118
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:716
Article PK: 94243

Can you give some me some more pointed issues?
You did mention that is perceived. Our auditing works with
in-memory queues to avoid disk i/o latency. The auditing
task will write to the sysaudits table as a regularly
scheduled task.

I have heard only people asking for specific feature
like adding the data that is updated/inserted
in the audit record.

Sethu

George Saylor wrote:
>
> Many times we have considered auditing and shot it down due to (perceived)
> performance overhead. Don't know if it is possible but it can't hurt to
> ask...


Anthony Mandic Posted on 2002-03-10 03:41:40.0Z
Message-ID: <3C8AD5F4.3E48E60E@start.com.au>
Date: Sun, 10 Mar 2002 13:41:40 +1000
From: Anthony Mandic <sp_am_block@start.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: less intrusive/high performance auditing
References: <iqfWVVdxBHA.214@forums.sybase.com> <3C897C87.59BBBDA4@sybase.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 48
NNTP-Posting-Host: 203.3.176.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:705
Article PK: 94233


Sethu wrote:

> Our auditing works with in-memory queues to avoid disk i/o latency.
> The auditing task will write to the sysaudits table as a regularly
> scheduled task.
>
> I have heard only people asking for specific feature
> like adding the data that is updated/inserted
> in the audit record.

Could auditing be configured to record to a database on another
server? This would then reduce any performance issues to only
query gathering and network distribution. The only significant
changes required would be in the audit records (to show the
server the audit data originates from) and the audit command
syntax (to control where audit data is sent).

This then naturally leads to an audit server for centralised
data gathering. Audit records could be written to specifically
named databases on the audit server or one general database. The
idea is somewhat akin to the Historical Server and Replication
Server. And since auditing is similar to performance data gathering,
perhaps this model of auditing could be interpolated to perfomance
gathering as well. That is, record performance stats directly from
an ASE server to a recording server. This would obviate the need
for Monitor Server with the end result being like Historical Server.
The auditing command syntax can act as a model for performance
data collection commands. The same general model could also be
extended to query plan collection with the term and concept of
'auditing' being extended to encompass command auditing, performance
auditing, query plan auditing and security auditing. Of course,
one could ask about auditing the audit server but, since the
current ASE auditing model already works this way, it should
be of no consequence.

The majority of these audit commands could work from either end
to manage auditing issues.

-am © 2002


Sethu Posted on 2002-03-12 02:30:25.0Z
Message-ID: <3C8D6841.62D71623@sybase.com>
Date: Mon, 11 Mar 2002 18:30:25 -0800
From: Sethu <sethu@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
To: Anthony Mandic <sp_am_block@start.com.au>
Subject: Re: less intrusive/high performance auditing
References: <iqfWVVdxBHA.214@forums.sybase.com> <3C897C87.59BBBDA4@sybase.com> <3C8AD5F4.3E48E60E@start.com.au>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 66
NNTP-Posting-Host: 10.22.91.118
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:698
Article PK: 94225

This is a very good idea.

Theoretically one could do this today..This is a wild idea and I have not
verified it.

What you can do is create sybsecurity database. Then create
all the sysaudits table as a proxy table to the central server.
This can technically then put all the audit records in
the remote server thro' proxy sysaudits tables. The issue with this
is there is no accountability in terms of which server did what..

Thanks,
Sethu

Anthony Mandic wrote:
>
> Sethu wrote:
>
> > Our auditing works with in-memory queues to avoid disk i/o latency.
> > The auditing task will write to the sysaudits table as a regularly
> > scheduled task.
> >
> > I have heard only people asking for specific feature
> > like adding the data that is updated/inserted
> > in the audit record.
>
> Could auditing be configured to record to a database on another
> server? This would then reduce any performance issues to only
> query gathering and network distribution. The only significant
> changes required would be in the audit records (to show the
> server the audit data originates from) and the audit command
> syntax (to control where audit data is sent).
>
> This then naturally leads to an audit server for centralised
> data gathering. Audit records could be written to specifically
> named databases on the audit server or one general database. The
> idea is somewhat akin to the Historical Server and Replication
> Server. And since auditing is similar to performance data gathering,
> perhaps this model of auditing could be interpolated to perfomance
> gathering as well. That is, record performance stats directly from
> an ASE server to a recording server. This would obviate the need
> for Monitor Server with the end result being like Historical Server.
> The auditing command syntax can act as a model for performance
> data collection commands. The same general model could also be
> extended to query plan collection with the term and concept of
> 'auditing' being extended to encompass command auditing, performance
> auditing, query plan auditing and security auditing. Of course,
> one could ask about auditing the audit server but, since the
> current ASE auditing model already works this way, it should
> be of no consequence.
>
> The majority of these audit commands could work from either end
> to manage auditing issues.
>
> -am © 2002


Anthony Mandic Posted on 2002-03-12 04:02:23.0Z
Message-ID: <3C8D7DCF.56E04D5F@start.com.au>
Date: Tue, 12 Mar 2002 14:02:23 +1000
From: Anthony Mandic <sp_am_block@start.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: less intrusive/high performance auditing
References: <iqfWVVdxBHA.214@forums.sybase.com> <3C897C87.59BBBDA4@sybase.com> <3C8AD5F4.3E48E60E@start.com.au> <3C8D6841.62D71623@sybase.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 71
NNTP-Posting-Host: DC-25-108.bpb.bigpond.com 203.40.25.108
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:693
Article PK: 94218


Sethu wrote:
>
> This is a very good idea.

It came to me in a flash of inspiration. I still get them
from time to time fortunately. I was so excited by the idea
that I thought about it all the rest of the weekend.

I ended up extending the idea from just general purpose
auditing to admin as well. For example, running a command
like "sp_who <SERVER NAME>" would run sp_who on the local
or admin server and get the sysprocesses selects from the
remote or target server, process them locally and return
the result set. This would mean that even if tempdb was
full on the target (which means doing sp_who there would
block) it wouldn't on the local server (unless the local
tempdb also had a problem). There may still be issues if
system tables were locked preventing reads but all the
queries colud run at an isolation level to avoid this.

Note that this would be different to the "server..sp_who"
syntax and would involve changes to some of the system
sprocs.

As an alternative, the "use" command could be extended
to specify a remote server (or a new command could be
added to specify a server target - perhaps a "connect" and
"unconnect" set). All subsequent commands would target that
server but process the results locally (to avoid issues like
the above mentioned one). I'm not sure of all the ramifications
yet but I don't see it as being unachieveable.

> Theoretically one could do this today..This is a wild idea and I have not
> verified it.

Yes, you can (more or less). Its all in there - the hooks
that is. The audit/admin server is just another server
with RPC calls doing the transport. with a little bit
of work it might also be possible to send a server's
error log to the audit server for storage in a table
too. This would mean you could do selects on it and
split up the lines into a few columns (for date stamps
etc.). Carl might like this.

> What you can do is create sybsecurity database. Then create
> all the sysaudits table as a proxy table to the central server.
> This can technically then put all the audit records in
> the remote server thro' proxy sysaudits tables. The issue with this
> is there is no accountability in terms of which server did what..

Yes, proxy'ing would be another way to do it. But it would
still leave the data on the source server. If you could proxy
the sybsecurity database the other way around, it would
reside on the audit server (under another db name) and
appear to be local to the source sever. It then takes
some of the strain off the source server. This would be
less of a change and easier to implement but the original
idea allows for far more scope (centralised audit, perfmon
and security collection as well as the above admin idea).

-am © 2002


Sethu Posted on 2002-03-12 05:00:11.0Z
Message-ID: <3C8D8B5B.BAB94082@sybase.com>
Date: Mon, 11 Mar 2002 21:00:11 -0800
From: Sethu <sethu@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: less intrusive/high performance auditing
References: <iqfWVVdxBHA.214@forums.sybase.com> <3C897C87.59BBBDA4@sybase.com> <3C8AD5F4.3E48E60E@start.com.au> <3C8D6841.62D71623@sybase.com> <3C8D7DCF.56E04D5F@start.com.au>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 7
NNTP-Posting-Host: 10.22.91.118
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:691
Article PK: 94217


>
> Yes, proxy'ing would be another way to do it. But it would
> still leave the data on the source server. If you could proxy

I did not get this. The audit data will be in the centralized proxy
server.


Sethu


Anthony Mandic Posted on 2002-03-12 14:10:07.0Z
Message-ID: <3C8E0C3F.A83E86A@start.com.au>
Date: Wed, 13 Mar 2002 00:10:07 +1000
From: Anthony Mandic <sp_am_block@start.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: less intrusive/high performance auditing
References: <iqfWVVdxBHA.214@forums.sybase.com> <3C897C87.59BBBDA4@sybase.com> <3C8AD5F4.3E48E60E@start.com.au> <3C8D6841.62D71623@sybase.com> <3C8D7DCF.56E04D5F@start.com.au> <3C8D8B5B.BAB94082@sybase.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 11
NNTP-Posting-Host: 203.3.176.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:686
Article PK: 94215


Sethu wrote:

> > Yes, proxy'ing would be another way to do it. But it would
> > still leave the data on the source server. If you could proxy
>
> I did not get this. The audit data will be in the centralized proxy
> server.

OK, I must have misunderstood you. I took your original
comment to mean proxy from the audit table on the source
server to the centralised audit server.

-am © 2002