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.

Hide   Copy Code

using System.Configuration;
using System.Net.Sockets;

Hide   Copy Code

private bool TestForServer(string address, int port)
{  
 int timeout = 500;
 if(ConfigurationManager.AppSettings["RemoteTestTimeout"] != null)
  timeout = int.Parse(ConfigurationManager.AppSettings["RemoteTestTimeout"]);
 var result = false;
 try
 {
  using(var socket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp))
  {
   IAsyncResult asyncResult = socket.BeginConnect(address, port, null, null);
   result = asyncResult.AsyncWaitHandle.WaitOne(timeout, true);
   socket.Close();
  }
  return result;
 }
 catch { return false; }
}

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. TheIAsyncResult.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:  

Hide   Copy Code

if(!TestForServer("MySqlServer", 1433))
 throw new 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. 

-------------------------------------------------------------------------------------------------------------------

This Article is from: http://www.codeproject.com/Articles/612751/Check-that-Sql-Server-exists-prior-to-connection