low cost method to check database before inserting data

[Jared Evans]

I developed a console application that will continually check a message
queue to watch for any incoming data that needs to be inserted into MS
SQL database.

What would be a low-cost method I could use inside this console
application to make sure the MS SQL database is operational before I
perform the insert?

[Simon Hayes]

You can use DATABASEPROPERTYEX() in TSQL or the Database.Status
property in SQLDMO to see if a particular database is available.

If that doesn't help, you'll probably need to give some more
information about how your console app will connect to MSSQL (ODBC, ADO
etc.).

Simon

[Jared Evans]

I don't think I could use any TSQL code since that would live on the
database and if the database server was down, I would not be able to
access the code.

What I think I need is a way to fast and cheap way to "ping" the
database server to ensure that it's up and accepting connections before
the console application attempts to connect and use a stored procedure
to insert the data.

If the "ping" failed, the console application would wait a few minutes
before trying again to insert data.

Maybe in terms of greater scalability, a better way would be to go
ahead and insert the data and if the database was down for some reasons
and it returned a "insert failed. - no such database." status. The
console application could go into a "waiting" mode to wait a few
minutes before attempting to insert the data again. Once the database
is back online, the console application would resume "normal" mode to
keep on inserting data as it's found on the MSMQ Queue.

[Simon Hayes]

"Jared Evans" <jn*****@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I don't think I could use any TSQL code since that would live on the
database and if the database server was down, I would not be able to
access the code.

What I think I need is a way to fast and cheap way to "ping" the
database server to ensure that it's up and accepting connections before
the console application attempts to connect and use a stored procedure
to insert the data.

If the "ping" failed, the console application would wait a few minutes
before trying again to insert data.

Maybe in terms of greater scalability, a better way would be to go
ahead and insert the data and if the database was down for some reasons
and it returned a "insert failed. - no such database." status. The
console application could go into a "waiting" mode to wait a few
minutes before attempting to insert the data again. Once the database
is back online, the console application would resume "normal" mode to
keep on inserting data as it's found on the MSMQ Queue.

You seem to be mixing the terms "server" and "database" a bit - the server
can be up and accepting connections, but one individual database may be
unavailable. So you'll have to decide what level of checking you want to do
before trying the INSERT - check the Windows service is running, check you
can login to MSSQL, check the database is available, check you can execute
the proc etc. At one end of the scale, if you get very few messages then
just try connecting and inserting; at the other, with very large numbers of
messages, you might find it's worth keeping an open connection. It's
probably a case of just experimenting to find the right balance for your
application.

Simon

[Lee Tudor]

The SQL Agent alert engine pings the server periodically with a TSQL
statement, you could just take a leaf out of their book.

USE master
GO
SELECT 'Testing Connection...'

Mr Tea

"Jared Evans" <jn*****@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

I developed a console application that will continually check a message
queue to watch for any incoming data that needs to be inserted into MS
SQL database.

What would be a low-cost method I could use inside this console
application to make sure the MS SQL database is operational before I
perform the insert?

[Jared Evans]

I would hate to have to poll the database every time a data insert was
attempted. Something about this concept doesn't sit right with me. I
am expecting periods of high volume of data inserts but I'm also
expecting the database and the server to be up the vast majority of
time. The database would only be taken offline for upgrade reasons or
unexpected hardware failure.

While double-checking with the database prior to any data insert sounds
like it would be more reliable, it would also take away performance
that could be applied elsewhere. I think it would be more
architecturally sound if the console application was able to revert to
a "waiting" mode once it detected that the database was not available
regardless of if the database server was accepting connections or not.

[Jared Evans]

By detection when the database is not available, I meant that when the
insert attempt returns an error message or exception.

[Erland Sommarskog]

Jared Evans (jn*****@gmail.com) writes:

I don't think I could use any TSQL code since that would live on the
database and if the database server was down, I would not be able to
access the code. What I think I need is a way to fast and cheap way to "ping" the
database server to ensure that it's up and accepting connections before
the console application attempts to connect and use a stored procedure
to insert the data.

The simple-minded way of doing it would simply be to try to connect. If
the connect fails, the server is not available. If the connect succeeds,
submit your query. You can reduce the connection timeout, if you don't
want wait 15 seconds when the server is down.

It could also be worth considering turning off connection pooling, so
that you really get a connection attempt each time. Then again, that
requires you to stay connected to complete all inserts once your
are connected.

Note that if the server is up, but the database is unavailable for
some reason, and you connection string specifies the datbase to
connect to, your connection will fail.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp