Click here to Skip to main content
16,018,664 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 
I had a situation that the network was physically broken, and the following statement actually returned true
result = asyncResult.AsyncWaitHandle.WaitOne(sqlserver_timeout_milliseconds, true);
Do you know why it did that? With a good network, your code worked well. I am just wondering. Thanks!
--lg

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