Determine whether SQL Server exists

[_DD]

I'd like to include a 'Test Connection' button in an app, for testing
validity of a SQL connection string. I'd prefer to keep the timeout
low. What is the conventional way of doing this?

 

[Michael Nemtsev]

Hello _DD,

Just make connection to DB to check whether it valid or not

_> I'd like to include a 'Test Connection' button in an app, for testing
_> validity of a SQL connection string. I'd prefer to keep the timeout
_> low. What is the conventional way of doing this
_>
---
WBR,
Michael Nemtsev :: blog: http://spaces.live.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche

 

[Cowboy \(Gregory A. Beamer\)]

If you want a relatively short timeout, you can use ADOX. It is a COM
library that allows you to determine if a server exists, if a database
exists, the objects in a database, etc. The other option I can think of off
hand is to simply connect and allow it to fail.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************

 

[Willy Denoyette [MVP]]

| I'd like to include a 'Test Connection' button in an app, for testing
| validity of a SQL connection string. I'd prefer to keep the timeout
| low. What is the conventional way of doing this?

Set the timeout value ("Connect Timeout") in the connection string to the
desired value and issue a SqlConnect.Open. This is the only way to test
whether a Connection string is valid AND the server is accepting the
connection.

Willy.

 

[_DD]

| I'd like to include a 'Test Connection' button in an app, for testing
| validity of a SQL connection string. I'd prefer to keep the timeout
| low. What is the conventional way of doing this?

Set the timeout value ("Connect Timeout") in the connection string to the
desired value and issue a SqlConnect.Open. This is the only way to test
whether a Connection string is valid AND the server is accepting the
connection.

Willy.

Willy (and others),

I don't think the connection string's "Connect Timeout" affects the
'hard fail' timeout when the connection string points at a server that
does not exist...does it?

I was initially just trying to connect and getting a go/no go. But I
want to do a couple things that seem out of scope for that approach:
I'd like to differentiate between 'server does not exist' and
'database does not exist'. And I'd like to eliminate the long delay
incurred when the server can't be found.

 

[Cor Ligthert [MVP]]

_DD,

Do as in my idea Michael suggest,

Use an SQLClient Connection and put that in a catch, if it fails then there
is no connection. Although in my idea it has not much sense, to do this
appart from your normal ADONET datalayer parts.

SQLConnection con = new SQLConnection();
Try
{con.open;}
Catch
{MessageBox.Show("There is no or malfunction connection");}

If this is not in a form class than you have beside the reference to
SQLClient to set a reference to the System.Windows.Forms for the MessageBox.

Typed in this message so watch typos.

I hope this helps,

Cor

 

[Sahil Malik [MVP C#]]

_DD.

Your answer here -
http://blah.winsmarts.com//2006-9-Determine_whether_a_SQL_Server_exists.aspx

- Sahil Malik
http://www.winsmarts.com

 

[Michael Nemtsev]

Hello Sahil Malik [MVP C#],

Yep, but this check only existence of SQL Server not its availability.
For example, other DBs like Oracle check you connection by requesting your
credentials, but not checking existence of this db instance

S> _DD.
S>
S> Your answer here -
S> http://blah.winsmarts.com//2006-9-Determine_whether_a_SQL_Server_exis
S> ts.aspx
S>
S> - Sahil Malik
S> http://www.winsmarts.com
S> S>---
WBR,
Michael Nemtsev :: blog: http://spaces.live.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche

 

[William \(Bill\) Vaughn]

Ok, in response to this and the other thread that wanted to test to see if
SQL Server is available try the code from my new blog entry:
http://betav.com/blog/billva/2006/09/question_of_the_day_enumeratin.html
This includes part of an example from my new book that lists all of the SQL
Servers and permits you to determine the state of the service. In other
words you can tell if the SQL Server is running. Does this validate a
ConnectionString? Nope, but it can tell you if the ConnectionString
validation has failed because the service was not there or not running. Even
if you do check to see if you can open a connection, you'll get a pooled
connection that's already connected after the first attempt... not
particularly useful if the service is actually dead... I drone on and on
about this in the book.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Sahil Malik [MVP C#]]

Okay you are going to explain a bit more to me.

So once I check for the existence - why can't I just try and connect to it
and verify it's availability?

SM

Hello Sahil Malik [MVP C#],

Yep, but this check only existence of SQL Server not its availability.
For example, other DBs like Oracle check you connection by requesting your
credentials, but not checking existence of this db instance

S> _DD.
S> S> Your answer here -
S> http://blah.winsmarts.com//2006-9-Determine_whether_a_SQL_Server_exis
S> ts.aspx
S> S> - Sahil Malik
S> http://www.winsmarts.com
S> S>---
WBR,
Michael Nemtsev :: blog: http://spaces.live.com/laflour

"At times one remains faithful to a cause only because its opponents do
not cease to be insipid." (c) Friedrich Nietzsche

 

[Willy Denoyette [MVP]]

| On Sun, 17 Sep 2006 16:17:16 +0200, "Willy Denoyette [MVP]"
|
| >
| >| >| I'd like to include a 'Test Connection' button in an app, for testing
| >| validity of a SQL connection string. I'd prefer to keep the timeout
| >| low. What is the conventional way of doing this?
| >
| >Set the timeout value ("Connect Timeout") in the connection string to the
| >desired value and issue a SqlConnect.Open. This is the only way to test
| >whether a Connection string is valid AND the server is accepting the
| >connection.
| >
| >Willy.
|
| Willy (and others),
|
| I don't think the connection string's "Connect Timeout" affects the
| 'hard fail' timeout when the connection string points at a server that
| does not exist...does it?
|
That's right, when connecting to SQL server, SQLClient uses the Sql Native
Interface (SNI) layer (unmanaged code) to connect using the protocol as
specified in the connection string or the default protocol, but SNI ignores
the "Connect Timeout" for the initial physical connect (it issues a
synchronous connect) and uses it's own hard coded timeout. The reason for
this is that SQLClient cannot make any progress without a physical
connection, it needs it to authenticate the client in the first place,
without an authenticated connection you won't be able to progress. That
means that this method cannot be used to achieve your goal.
Now, there might be many reasons for a connect failure: the client network
connection may be broken (both at the physically and network stack level),
same for the server side, the netwok itself may be down (think:router
switches etc...), the server (IP or NP host) may be down or non-existing the
SQL instance may be down or non-existing, SQL instance way be in maintenace
mode etc..,
It's just impossible to distinguish between all these type of failures, all
you can do is check the client side of the network stack, you can check if
the network is up and connected to, you could 'ping' the server (host), you
can 'telnet' the sql server instance (telnet hostaname sqlport), but hey
what if one of these fail, what's the cause?
And what if this succeeds, who says that finally your client will use TCP to
connect to the SQL instance? Or what if authentication fails? or the login
server is not available (when using integrated security in an AD domain
realm).
I think you should forget about this, if a connection fails, you will have
to investigate the failure, but don't try to implement this in an end-user
application, it makes no sense, really.


Willy.

 

[Willy Denoyette [MVP]]

| On Sun, 17 Sep 2006 16:17:16 +0200, "Willy Denoyette [MVP]"
|
| >
| >| >| I'd like to include a 'Test Connection' button in an app, for testing
| >| validity of a SQL connection string. I'd prefer to keep the timeout
| >| low. What is the conventional way of doing this?
| >
| >Set the timeout value ("Connect Timeout") in the connection string to the
| >desired value and issue a SqlConnect.Open. This is the only way to test
| >whether a Connection string is valid AND the server is accepting the
| >connection.
| >
| >Willy.
|
| Willy (and others),
|
| I don't think the connection string's "Connect Timeout" affects the
| 'hard fail' timeout when the connection string points at a server that
| does not exist...does it?
|
| I was initially just trying to connect and getting a go/no go. But I
| want to do a couple things that seem out of scope for that approach:
| I'd like to differentiate between 'server does not exist' and
| 'database does not exist'. And I'd like to eliminate the long delay
| incurred when the server can't be found.
|

Ok, did some further investigation, and it looks like SQLClient (V2 of the
framework and SQL2005) effectively considers the "Connect Timeout" value for
the initial connect (+ 1 second). That means that if you set Connect
Timeout=1 in the connectionstring, the connection will timeout after 2
seconds, while a value of 3 will take 4 seconds.

Willy.

 

[William \(Bill\) Vaughn]

Wow--I'm impressed. (seriously).
I agree... but I'm not convinced that the provider "can't" bubble up more
details on why it couldn't connect. I expect that the generic nature of .NET
providers makes it tough to return more granular details about the failure.
Thanks -- Learn something new every day.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Willy Denoyette [MVP]]

| Wow--I'm impressed. (seriously).
| I agree... but I'm not convinced that the provider "can't" bubble up more
| details on why it couldn't connect. I expect that the generic nature of
..NET
| providers makes it tough to return more granular details about the
failure.
| Thanks -- Learn something new every day.
|
| --
|

William, the provider could possibly bubble up some more details, but it can
only bubble up what's coming from the network stack.
To explain what I mean we should take a close look at (ISO) stack as used by
SQL server (or any other DBMS):
Starting from the top - the Application layer - we have the SQLClient
managed API, (SNI and the TDS parser),
below this one (the Session layer) we have the network protocol library
(pipes, sockets, shared memory etc...),
sitting in top of our Network stack (except for the shared memory protocol),
this one depends on the used client session protocol library, but for the
socket protocol we have TCP at the transport layer, IP at the protocol
layer, HDLC the network layer and finally the physical layer (Ethernet LAN,
WAN, etc....)
Now , you see that if you want to bubble up detailed failure notifications,
you need a Network stack in which each layer not only provides in error
recovery but also returns detailed error message when something goes wrong
at the lower layer to the upper layer, and here's where the problem starts.
The network stack does not (should not and in most cases cannot) provide
this status reporting (though ICMP provides some possibilities, it's
un-common to use it from a regular application), it only has provisions for
error recovery at his own layer, and a very simple protocol for error
reporting to it's upper layer. Consider that the physical layer cannot
connect (say cable broken), it will signal it's upper layer that a physical
connection is not possible so the network layer cannot push a connection
HDLC request frame through the network infrastructure. But, it cannot say
what's actually broken at the physical layer, where is the cable broken? at
the client side or at the switch or the router or ...?
The same is true for every layer in the stack, in some cases detailed info
may be available, like irrecoverable transmission errors at the HDLC layer,
but most of the time there is no such info, and more, it makes no sense to
"bubble up" this to the application layer, it's not it's concern, it's up to
the IP layer to start a recovery action (retry) when there is a failure at
the Network layer and it's up to IP to signal the same to it's upper layer
and so on until you reach the application layer, where the only thing that
can be done is report a failure message to the user and/or start a recovery
action, that is, retry the SQLClient Open or give up and let the network and
the DB admin's try to find out the cause of the failure.

Willy.


Willy.

 

[William \(Bill\) Vaughn]

Yes, this has been detailed to me about a dozen times over the last
decade... You have a firm grasp on the issues.I see it as a long-term
problem that MS and the provider developers have not been able to fully
address. I've worked with these drivers for awhile and I know it's tough to
be able to bubble up something that's only manifested as a timeout on a NIC
or a "broken pipe" back from the server. When we were (a bit) closer to the
TDS with DBLib we could give developers a bit more information--far more
than the "stuff happened" errors we now see. I also think the exception
string that comes back suggesting that the server is not properly configured
is like telling a driver that he should have been driving slower after
having a bread truck sideswipe him on the freeway.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Willy Denoyette [MVP]]

| Yes, this has been detailed to me about a dozen times over the last
| decade... You have a firm grasp on the issues.I see it as a long-term
| problem that MS and the provider developers have not been able to fully
| address.

True, but even if the Network stack could bubble up extended error
information (through error codes f.i), they would not be able to report
details about what's wrong at the physical network layer (infrastructure).
Even in the simple case like a wrong hostname (program error) or instance
name , the network layer has no idea about what's wrong in this case. The
"name lookup" isn't able to resolve the address, but why? Say that the error
message in this case looks like "Name lookup for server "xxxx" failed , make
sure the server name refers to an existing server, check you DNS server or
your etc\hosts file. Check also you network infrastructure, like routers and
switches and cables."., honestly, this is not what I call more helpfull as
we get now

Another thing you must consider is performance, the network stack (the
winsock library and NDIS) is not a place to waste too many cycles, I really
don't think that 3COM and MS are willing to change the NSDIS specifications,
nor will anyone ever change the WinSock2 specifications,let alone change the
implementation, in order to include extended error reporting.
I have since long accepted this inconvenience, just like I accepted that
TCP/IP is far from perfect, I'm sure you also have done so ;-).

Willy.

 

[Cor Ligthert [MVP]]

Willy and Bill,

You have forgotten the power cables on more places.

To be serious, what has this for sense. In my opinion is it only needed to
see if the data on the DataBase server is reachable. For that Michael's
solution as first stated is in my opinion more than enough,

Cor

 

[Willy Denoyette [MVP]]

| Willy and Bill,
|
| You have forgotten the power cables on more places.
|
| To be serious, what has this for sense. In my opinion is it only needed to
| see if the data on the DataBase server is reachable. For that Michael's
| solution as first stated is in my opinion more than enough,
|
| Cor
|

Which was exactly my answer, all I did was answering both of OP's questions:

Q1 ... testing validity of a SQL connection string ...
A. use SqlConnect.Open
Q2 ... to keep the timeout low...
A. set the connetionstring "Connect Timeout" parameter to a lower value.

The rest was just a technical chat with Bill.

Note that I don't necessarily agree with the OP to add a "test connection"
in an end-user's application (end-users should not test connections).

Willy.

 

[William \(Bill\) Vaughn]

Using the "test connection" approach is like picking up the phone to see if
there is dialtone. The second you hang up the proverbial beer truck can hit
the phone pole outside and cut you off. I suggest robust exception handlers
to deal with the stuff that happens when connecting. This is why the
"Getting Connected" chapter in my new book is 70 or so pages long (IIRC).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Cor Ligthert [MVP]]

Bill,

What is the trouble to help that the beer is not going over the road and
help to unload that beer truck?

Although Willy will probably write that if it is about beer, you have to be
sure it is good beer first before you are doing work that has no sense.

(He is from Belgian you know, and there is no country in the world with more
beer experts).



Cor