Check that Sql Server exists prior to connection

Introduction

At times a Sql Server database server service is not available for use. In this article I will offer one way for applications to validate the existence of a Sql Service service prior to connection.

Background

Failures happen. Fact of life. Deal with it!

Seriously, at times our applications can fail to connect to a Sql Server service for any number of reasons. Waiting for connection timeout and handling that gracefully is always a good option, but your timeout may be excessive due to various reasons, and having the users wait for an extended period of time for a connection that is going to fail anyway is frustrating to them. It would be nice (and slightly productive) for a user to know that that they can't do what they were going to do a little bit sooner.

Here's what I've come up with to combat this simple but frustrating issue.

Using the code

Sql Server communicates via a TCP connection to a client. The code below simply attempts to open a socket connection to the specified TCP port of a specified machine. If it fails, it returns false. Sql Server's default port is 1433, but the method accepts a port number as a parameter. You can also use an IP address as the address parameter, or the machine name, but DNS must be able to locate the machine.

Note the timeout variable. Because TCP connections are not immediate because network traffic, speed, etc., the connection attempt waits for this specified number of milliseconds before returning. The IAsyncResult.AsncyWaitHandle.WaitOne returns true or false depending on if a connection is made or not. The timeout value is stored in a config file for easy manipulation as you may have to change this depending on your own local network speeds.

Simply call the code as follows:

if(!TestForServer("MySqlServer", 1433))
thrownew ApplicationException("Cannot connection to the Sql Server service on MySqlServer");

Points of Interest

The method above is not just limited to Sql Server. It can also be used to verify the existence of other database platforms utilizing TCP sockets. I have used this to successfully check for existence of FireBird servers. Another interesting use of the method would be to check to see if the user is running in disconnected mode, thus switching to a local database that would then be synchronized upon re-connection to the network.

For the local machine issue, I don't see any reason to be concerned. Just use localhost as the address (or 127.0.0.1).

The second issue is a difficult question to answer. As stated the default port for sql server is 1433. It is actually very difficult to get the port on which a sql server is running. One way is to look at the sql server logs. In the startup portion of the server log, you will find a line that starts with "Server is listening on ['any' <ipv4> xxxx]" or "Server is listening on ['any' <ipv6> xxxx]". The xxxx is the port number.

If you are not using sql server, research what the default port is for the platform of choice. If the server does not use the default port. you will have to go to the server and look at options to find out what port it is using.

Interesting article, but I was wondering why is this route any different (or better) than specifying a connection timeout value in the connection string? Granted, it is specified in seconds so you can't set a timeout to a fraction of a second. But going this route you end up with two connections to the server on a normal basis.

Good point, and in the article text I stipulated that this is still a valid way to check.

Problem with connection timeout is if the timeout is set to 30 seconds for business reasons, and the service is not available, the user has to wait 30 seconds to know that the service is not available. Connection to the socket/port on the remote machine is much quicker than making the connection to the database. Using this method, the user can know immediately that the service is not available and move on to something else.

Imagine this scenario, which I am currently dealing with and which prompted the code. I have a connection to Sql Server which is used by remote users nightly. At some point during the evening, the server is not available due to backups. Because they are remote, connections sometimes take up to 10 seconds because of normal usage. With a 30 second connection timeout, the users had to wait to see if they can get in the application. Using this method, the tcp connection check only takes about 1.2 seconds. So upping the timeout of the code to 1500 allows signal of service down much quicker than allowing the connection to timeout.

Because the Connect method (synchronous) of the socket does not allow me to specify a timeout. This was one of the requirements that I had to be able to adjust the timeout for diverse environments. Your network may be slower or have more traffic than mine, or that server may be under higher demand and thus take longer to connect. The timeout is able to wait for a connection and if it doesn't happen in the specified time limit, then return. False negatives may also occur if the timeout is not set large enough to accommodate the connection time, i.e., you are able to connect, but the timeout ran out before you connected.