Click here to Skip to main content
16,019,983 members
Articles / Database Development / SQL Server

Check that Sql Server exists prior to connection

Rate me:
Please Sign up or sign in to vote.
4.80/5 (27 votes)
27 Jun 2013CPOL2 min read 59.9K   55   11
Validate existence of Sql Server

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.

C#
using System.Configuration;
using System.Net.Sockets;
C#
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. 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:  

C#
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. 

History

6/27/2013Original submission

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
Visual Basic Developer since version 1.0
Java web developer
Currently developing in vb and c#

Comments and Discussions

 
QuestionProblem with broken network Pin
loyal ginger22-Jul-22 4:44
loyal ginger22-Jul-22 4:44 
QuestionWhat if you do not know the port and/or you're looking on the local machine Pin
Member 43533715-Jun-14 13:21
Member 43533715-Jun-14 13:21 
AnswerRe: What if you do not know the port and/or you're looking on the local machine Pin
Greg Osborne24-Jun-14 3:25
Greg Osborne24-Jun-14 3:25 
Sorry about not responding in a timely manner.

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.

If you have access to the server you could use the netstat command to get port information. If you can't get to the machine, you can try the suggestions listed at http://www.governmentsecurity.org/forum/topic/30799-getting-netstat-information-off-a-remote-host/

Hope this helps
Please rate my articles!
Greg Osborne

GeneralThoughts Pin
PIEBALDconsult18-Apr-14 7:15
mvePIEBALDconsult18-Apr-14 7:15 
AnswerRe: Thoughts Pin
Greg Osborne24-Jun-14 3:26
Greg Osborne24-Jun-14 3:26 
GeneralMy vote of 5 Pin
John B Oliver16-Jul-13 11:33
John B Oliver16-Jul-13 11:33 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA13-Jul-13 20:47
professionalȘtefan-Mihai MOGA13-Jul-13 20:47 
QuestionConnection Timeout Pin
jfos28-Jun-13 7:16
jfos28-Jun-13 7:16 
AnswerRe: Connection Timeout Pin
Greg Osborne28-Jun-13 7:39
Greg Osborne28-Jun-13 7:39 
Questionasync or sync Pin
giammin27-Jun-13 6:38
giammin27-Jun-13 6:38 
AnswerRe: async or sync Pin
Greg Osborne27-Jun-13 6:48
Greg Osborne27-Jun-13 6:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.