greetings,

i have been tasked with developing a remote monitoring service that we can sell
to our clients. before i ask for specifics, let me give a bit of background:

we use ase (12.0, 12.5 on HP) as the database for our application. all access
to the server is programmatic, with most queries being ad hoc. we have a set
number of databases and create numerous tables daily. essentially we are a
datawarehouse. once we put the data in and do a bit of massaging, it is
basically read-only access. total data size ranges from ~100GB to over 1TB. all
access to the systems is via modem(<= 28.8k) with no X11.

most of our clients are 24x7, but because ase is the back engine for our app,
most clients don't really care about the database and they don't want a minute
by minute accounting. mostly they want to know how the server worked this
day/week/month, and what if anything, needs to be done to keep it working at
least that well or better in the future.

given the above constraints, what types of things can/should i monitor? so far i
have come up with the obvious things like monitoring the error/backup logs,
checking free space, running dbcc checkstorage.

also, what types of resources are available to help me automate all this
monitoring/reporting. ideally i would like to cron a script that will run the
monitoring and prepare reports, then ftp the file to our ftp site (most hosts do
not have mail enabled). i know abut sypron.nl, sybperl, edbarlow.com and the
isug faq. are there any other sites that might have scripts/stored procs that
would help me monitor our servers?

any advice or info is appreciated. TIA

ken

------------------------------------------------------------------------
"Those who don't understand UNIX are condemned to reinvent it, poorly."
--Henry Spencer

Ken Kroel wrote:

> i have been tasked with developing a remote monitoring service that we can sell
> to our clients. before i ask for specifics, let me give a bit of background:
>
> we use ase (12.0, 12.5 on HP) as the database for our application. all access
> to the server is programmatic, with most queries being ad hoc. we have a set
> number of databases and create numerous tables daily. essentially we are a
> datawarehouse. once we put the data in and do a bit of massaging, it is
> basically read-only access. total data size ranges from ~100GB to over 1TB. all
> access to the systems is via modem(<= 28.8k) with no X11.
>
> most of our clients are 24x7, but because ase is the back engine for our app,
> most clients don't really care about the database and they don't want a minute
> by minute accounting. mostly they want to know how the server worked this
> day/week/month, and what if anything, needs to be done to keep it working at
> least that well or better in the future.

What kind of monitoring do you do at the OS level now? If you
provide something to your clients for that, could you add in
the ASE monitoring? Or will it be separate?

> given the above constraints, what types of things can/should i monitor? so far i
> have come up with the obvious things like monitoring the error/backup logs,
> checking free space, running dbcc checkstorage.

Database and table growth (for capacity planning). Alerts
for serious server problems. Other dbcc routines for more
finegrained checking. A way to check problem connections
(see what command they are running or showplan their running
query, etc.).

> also, what types of resources are available to help me automate all this
> monitoring/reporting. ideally i would like to cron a script that will run the
> monitoring and prepare reports, then ftp the file to our ftp site (most hosts do
> not have mail enabled). i know abut sypron.nl, sybperl, edbarlow.com and the
> isug faq. are there any other sites that might have scripts/stored procs that
> would help me monitor our servers?

I suspect you may wish to get a little more sophisticated. It
may depend on how you plan to package this product (sell only
to your existing clients along with your app or sell it as a
separate product to any other client). It won't hurt to start
by talking to your Sybase sales rep. Ask them about the old
SNMP subagent, monitor server and historical server (the latter
sounds like a good match for you) and the MON API. There's also
the new monitoring tables. There are also existing products
that you could OEM (BMC's DB X-Ray, for example).

I don't think fetching reports thru ftp is particularly easy
for some people. It might be better to set up a GUI on the
client and use Sybase's Open Client to access data and display
it locally.

-am © 2003

Ken Kroel wrote:

> > What kind of monitoring do you do at the OS level now? If you
> > provide something to your clients for that, could you add in
> > the ASE monitoring? Or will it be separate?
>
> we have a sysadmin service that we also sell to clients. i believe the db admin
> service will be a separate service, although i'm sure there will be a package
> deal available.
>
> so, while its possible i could coordinate with the sysadmin guy, its not
> something i can count on at every site.

OK, I was wondering if you integrate the two somehow. At least,
you sohlud be able to see what the database server is doing from
the OS's perspective (how much CPU, disk i/o, network, etc.).

> > Database and table growth (for capacity planning). Alerts
> > for serious server problems. Other dbcc routines for more
> > finegrained checking. A way to check problem connections
> > (see what command they are running or showplan their running
> > query, etc.).
>
> hmm, i assume the checking of problem connections would be a real time issue.
> part of the service would include real time support of any sybase problem,
> although that's partly already available to clients because if ase stops, so
> does our app. i believe the main thrust of the sell is that we will do the
> necessary things to keep any database related problems from occurring in the
> first place.

A lot of problems are hard to predict. I often see posts from people
with ASE problems using the same versions my clients are using and they
never see the same problems. So there are a lot of complicated factors
involved. How complex your schema and queries are may indicate how
likely you are to experience unusual problems.

> this will only be sold to businesses that already have our app. its not meant to
> be a new product for our company, more of a revenue enhancement for clients that
> don't want to be bothered with minor details, such as is the database going to
> fill up today ;-)

OK.

> > I don't think fetching reports thru ftp is particularly easy
> > for some people. It might be better to set up a GUI on the
> > client and use Sybase's Open Client to access data and display
> > it locally.
>
> would this approach work over old, slow modems and text only connections?

Open Client itself isn't a very demanding protocol. The GUI runs only
on the client end. Its only the data being transferred that would be
an issue. How slow are the modems? 28.8 should be adequate.

> in addition to being slow, the modems are often unreliable and drop connections,
> so i would like to either do all the processing on the host and just grab the
> file, or just generate raw data, then do all analysis after getting the file
> onsite. it can be tedious getting connections opened up for some clients, so the
> automated ftp was an attempt to avoid that issue.

Yes, you'd definitely need to consider reaquiring the same data. This
could be a file or data from the database. All that's really needed
is for the client to know it needs to refetch since the last attempt
didn't complete.

-am © 2003

If you haven't yet closed the door on the build vs. buy decision, you
should check out http://www.techtell.com. They can customize the
probes you need, and can definitely do it cost effectively.

And yes, I work there. If you have any questions, or if I can help in
any way, please drop me a note.

They are geographically diverse, with diverse routes and robust power
backup.

From what I read in the news, that's a big benefit.