Click here to Skip to main content
13,056,341 members (109,169 online)
Click here to Skip to main content
Add your own
alternative version


53 bookmarked
Posted 27 Jun 2013

Check that Sql Server exists prior to connection

, 27 Jun 2013
Rate this:
Please Sign up or sign in to vote.
Validate existence of Sql Server


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.


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.

using System.Configuration;
using System.Net.Sockets;
private bool TestForServer(string address, int port)
	int timeout = 500;
	if(ConfigurationManager.AppSettings["RemoteTestTimeout"] != null)
	<span class="Apple-tab-span" style="white-space: pre;">	</span>timeout = int.Parse(ConfigurationManager.AppSettings["RemoteTestTimeout"]);
	var result = false;
		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);
		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:  

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. 


6/27/2013Original submission


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


About the Author

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

You may also be interested in...


Comments and Discussions

QuestionWhat if you do not know the port and/or you're looking on the local machine Pin
Member 43533715-Jun-14 13:21
memberMember 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
memberGreg Osborne24-Jun-14 3:25 
GeneralThoughts Pin
PIEBALDconsult18-Apr-14 7:15
memberPIEBALDconsult18-Apr-14 7:15 
AnswerRe: Thoughts Pin
Greg Osborne24-Jun-14 3:26
memberGreg Osborne24-Jun-14 3:26 
GeneralMy vote of 5 Pin
John B Oliver16-Jul-13 11:33
memberJohn B Oliver16-Jul-13 11:33 
GeneralMy vote of 5 Pin
Mihai MOGA13-Jul-13 20:47
professionalMihai MOGA13-Jul-13 20:47 
QuestionConnection Timeout Pin
jfos28-Jun-13 7:16
memberjfos28-Jun-13 7:16 
AnswerRe: Connection Timeout Pin
Greg Osborne28-Jun-13 7:39
memberGreg Osborne28-Jun-13 7:39 
Questionasync or sync Pin
giammin27-Jun-13 6:38
membergiammin27-Jun-13 6:38 
AnswerRe: async or sync Pin
Greg Osborne27-Jun-13 6:48
memberGreg 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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170728.5 | Last Updated 27 Jun 2013
Article Copyright 2013 by Greg Osborne
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid