detecting that a SQL db is running

[bill ramsay]

Hi

I wonder if anyone can help me with a problem that I have with MSDE
SQL db. [I am changing to SQL server soon].

I have an app that runs on a server that communicates with remote
devices using email, this and other similar servers then sends
XML/SOAP messages to a master application.

I am in the process of intercepting these XML messages and forwarding
them on to a service managment platform for notification alerts etc.
This app reads a SQL db to get some additional information which is
also included in the XML message.

the problem is that the app on the comms server is an aggregate of
modules, that has a tendency to lock up, the server still runs, but
it causes the SQL server to stop. This is being worked on by the
developers, i expect that it will take some time to resolve.

using ASR we just reboot the server when this happens.

Using simple SQL statements, i am able to detect when SQL is up and
running, and to detect when it has stopped.

Is there a simple way to detect if the SQL server has actually
started?

i had a look at investigating the registry, but to be honest, from
the various recipes that i have seen, i haven't a clue what I am
looking at,

any advice?

any information shall be gratefully received

kind regards

Bill

 

[Dennis Lee Bieber]

I'm afraid this is not going to be very helpful... Unless you are

Hi

I wonder if anyone can help me with a problem that I have with MSDE
SQL db. [I am changing to SQL server soon].

Shouldn't be much of a difference... MSDE is a version of
SQL-Server... (I've seen both "MicroSoft Data Engine" and "Microsoft
SQL-Server Desktop Engine" used, in the same document even, for the
expansion of MSDE). MSDE is just a limited version -- smaller databases
(2 or 4GB), restricted parallel queries (5 queries, others get
queued)... Sort of a step up from the JET engine (which you may believe
is Access -- but Access is really just a GUI report/form/query tool
using JET for native database storage), but still "personal".

Though your use of "SQL" is rather ambiguous... SQL is a
semi-standardized language for accessing databases -- it is technically
independent of the actual database management system (it could even be
used with an old hierarchical DBMS, though I'd hate to have to write the
implementation that translates a query into the database native). "SQL
db" is, thereby, a rather meaningless term...

I have an app that runs on a server that communicates with remote

"an app" and "a server"

devices using email, this and other similar servers then sends
XML/SOAP messages to a master application.

"other similar servers" and "master application"

I am in the process of intercepting these XML messages and forwarding
them on to a service managment platform for notification alerts etc.
This app reads a SQL db to get some additional information which is

"This app"

(and more likely, it is using SQL to send queries to the DBMS -- the
DBMS reads the database files and returns the results to the
application)

also included in the XML message.

the problem is that the app on the comms server is an aggregate of

"the app" and "comms server"

How many applications and servers are we looking at in this post?

modules, that has a tendency to lock up, the server still runs, but
it causes the SQL server to stop. This is being worked on by the
developers, i expect that it will take some time to resolve.

And here we have a confusion of "server"... You appear to be using
the term for both the hardware and the software. And you opened by
implying you were on MSDE, going to move to SQL Server -- this indicates
you are already on SQL Server?

So how many "server programs" are in play, and how many "server
machines" are they running on...

using ASR we just reboot the server when this happens.

Using simple SQL statements, i am able to detect when SQL is up and
running, and to detect when it has stopped.

Uhm... What statements? "SQL" is a language, it has no up or down
state. I'd expect to get a failure to connect to the DB server if the
server machine is dead, and maybe timeouts if the DB server process is
dead on a live machine; if it is accepting connections, but not
responding to queries I'd think the problem is some limit in the server
on how many queries it will process in parallel /and/ that the queries
it /is/ processing are taking excessively long to complete.

Is there a simple way to detect if the SQL server has actually
started?

Not sure about your machines, but I think mine (I have available
MSDE, Firebird, MaxDB, MySQL as server programs, though only MySQL is
set to start on boot; I also have JET/Access and SQLite3, but those are
"file servers" where the processing code is part of the application)
wouldn't accept connections until after the boot sequence had started
the service.

i had a look at investigating the registry, but to be honest, from
the various recipes that i have seen, i haven't a clue what I am
looking at,

Probably not a help -- the registry is more likely to be used to
define what service is to be started on a reboot... not what processes
/were/ started.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[triode]

Sorry if i did not make myself clear. let me try again.

1. I have a series of servers running windows server 2003.
2 on each server there is an application running that communicates
with remote devices using email.
3. this application is made up from a series of modules that also
extract data from a local MSDE db.
4. this application pulls emails from remote devices, interrogates a
the DB, then passes on an XML message to a central server.
5. I am intercepting these XML messages to insert additional data
that I extract from the DB, and then forward on to another
application. Call this my local app.

The problem that I have is that the original application occasionally
hangs, causing the DB to stop. I have ASR routines in place to
re-boot the server when this happens.

I can detect when the db is up and not responding, however, if the DB
does not start at all, my local application hangs. I need to find a
way to determine if the DB has started, that's all.

if any of you have any insight into how I can detect that the DB has
started as is running, that would be very useful.

any contributions, most gratefully received.

kind regards

bill

 

[Dennis Lee Bieber]

Sorry if i did not make myself clear. let me try again.

Still a lot of questions -- I'm trying to really understand the
architecture (pity there isn't a comp.lang.python.binaries where an
architecture diagram could be posted)

1. I have a series of servers running windows server 2003.

Okay... multiple boxes with M$ Server OS

2 on each server there is an application running that communicates
with remote devices using email.

Each running "Application-A"... Now question one... Is this outgoing
email, or incoming email (and if outgoing, do these boxes run the mail
servers [smtpd or some other?] or transfer using a single dedicated mail
server box).

3. this application is made up from a series of modules that also
extract data from a local MSDE db.

Is "this application" still Application-A? If so, what does "local
MSDE" mean -- each of the boxes in #1 is running its OWN COPY of MSDE,
and not using a shared database server process running on just a single
machine?

4. this application pulls emails from remote devices, interrogates a
the DB, then passes on an XML message to a central server.

Still "Application-A"? What does "pulls emails" mean? To me, that
implies that the "remote devices" are running a POP3 server program,
with local email storage, and your boxes with "this application" are
making POP3 logins to the remote device -- using some known account --
to download the contents of the mailbox for that account.

"interrogates the DB" meaning Application-A and the MSDE on that
box?

What protocol is the "passes on" using. Something custom?

5. I am intercepting these XML messages to insert additional data
that I extract from the DB, and then forward on to another
application. Call this my local app.

How do you intercept? Either the boxes in #1, using the application
in #4, is sending the data /to/ your "local app" (Application-B), or --
as it sounds -- you are packet sniffing in between the #1 boxes and the
#4 central server.

Now, since it seems the boxes in #1 have individual MSDE processes,
where is the database process of #5 residing? On your "interception"
box? On the "central server" (are they the same?)

And where does "another application" (Application-C) live? Same
machine, another machine?

The problem that I have is that the original application occasionally
hangs, causing the DB to stop. I have ASR routines in place to
re-boot the server when this happens.

Application-A is hanging -- during some MSDE operation to the
database server on its own machine? Sounds like the point of attack
should be Application-A -- what is it doing to lock up MSDE... Rather
than having an outside process go "Something's wrong -- reboot and
hope".

I can detect when the db is up and not responding, however, if the DB
does not start at all, my local application hangs. I need to find a
way to determine if the DB has started, that's all.

The attempt to connect to it /should/ eventually time-out. I just
tried a connect from "SQL Server Query Analyzer" to a NON-RUNNING SQL
Server/MSDE instance... After about 15 seconds it popped up an error
code:

Unable to connect to server \\WOLFPACK:

Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does
not exist or access denied

{Now if I can just remember the admin accounts that were created when I
installed MSDE, and MaxDB <G>}

What adapter are you using to connect with? (I don't have a M$ SQL
Server adapter for Python installed... but kinterbasdb (for Firebird,
when no Firebird server is running) took about 10 seconds before:
Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "E:\Python24\lib\site-packages\kinterbasdb\__init__.py", line
433, in connect
return Connection(*args, **keywords_args)
File "E:\Python24\lib\site-packages\kinterbasdb\__init__.py", line
610, in __init__
b.dsn, b.dpb, b.dialect, timeout
OperationalError: (-902, 'isc_attach_database: \n Unable to complete
network request to host "localhost".\n Failed to establish a
connection.\n No connection could be made because the target machine
actively refused it.')

Everything I know indicates that the code attempting to connect to
the server process should generate some sort of error/exception
condition... It should not just "hang"...
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[bill ramsay]

Dennis

none of this matters, all i am trying to find out is whether or not
the local MSDE is actually running.

I put all the other bits in there to try and put some background to
it.

kind regards

bill

 

[Paul McNett]

none of this matters, all i am trying to find out is whether or not
the local MSDE is actually running.

If it is a local MSDE then you may be able to rely on the connection
being refused if the server isn't running.

#-- begin
import socket

host = "127.0.0.1"
port = 1433 ## replace with msde_port, if it differs

s = socket.socket(socket.AF_INET)
try:
s.connect((host, port))
print "Server on %s is running" % port
except socket.error, e:
print "Server on %s appears to be down (%s)" % (port, e)

#-- end

Please note that this is untested and not very well thought through. But
try it and see if it gets you on the right track. If this isn't run
locally, you'll probably need to set the timeout low enough for the
connect call not to appear to hang before returning the timeout error.

 

[bill ramsay]

thank you paul, much appreciated

 

[Dennis Lee Bieber]

Dennis

none of this matters, all i am trying to find out is whether or not
the local MSDE is actually running.

From my reading of your system, you have multiple "local MSDE"
server processes distributed about, and something on those distributed
systems that causes the initial problem... So I've never been clear of
just where any given application/server process actual resides...

However, all the tests I've been able to perform on my desktop
indicate that /I/ get time-outs or failure to connect messages within 15
seconds of a connection request when the server process is running.

I don't get unending lock-ups...
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[bill ramsay]

From my reading of your system, you have multiple "local MSDE"
server processes distributed about, and something on those distributed
systems that causes the initial problem... So I've never been clear of
just where any given application/server process actual resides...

However, all the tests I've been able to perform on my desktop
indicate that /I/ get time-outs or failure to connect messages within 15
seconds of a connection request when the server process is running.

I don't get unending lock-ups...

dennis

it doesn't matter what is causing the lockups, it's a problem with a
supposedly professionally written application package that I have no
control over. I am just at this moment trying to deal with the
consequences.

I think that I haave found a way to deal with the issue that I have.

Kind regards

Bill