Microsoft & .NETASPChecking Whether SQL Server Is Available

Checking Whether SQL Server Is Available

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

When you’re designing applications that work with databases over the Internet, perhaps the largest problem you face is ensuring that everything can actually connect to everything else. You need to ensure that your users have a valid connection to the Net, and you need to ensure that your database server is up and running.

It’s not a typically easy process—and it’s a topic that attracts an unusually large amount of error handling code.

However, why not take the simple route and avoid potential big errors later on by checking whether your SQL Server is available to the user before starting your database code?

The following function does just that for you. Simply call IsSQLServerAvailable, passing in your server or IP address. It attempts a connection and returns a True if everything seems fine. Here’s the code:

Public Function IsSQLServerAvailable( _
                  ByVal ServerAddress _
                  As String) As Boolean
' Tests an SQL Server connection by 
' name or IP address
 Try
  ' Attempt to get server address
  Dim objIPHost As New System.Net.IPHostEntry()
  objIPHost = _
     System.Net.Dns.Resolve(ServerAddress)
  Dim objAddress As System.Net.IPAddress
  objAddress = objIPHost.AddressList(0)
  ' Connect to port 1433, most common SQL Server
  ' port. If your target is different, change here
  Dim objTCP As System.Net.Sockets.TcpClient = _
      New System.Net.Sockets.TcpClient()
  objTCP.Connect(objAddress, 1433)
  ' No problems (hurrah!)
  ' Close and cleanup
  objTCP.Close()
  objTCP = Nothing
  objAddress = Nothing
  objIPHost = Nothing
  ' Return success
  Return True

 Catch ex As Exception
  ' Server unavailable, return fail value
  Return False
 End Try
End Function

And here’s how you might call this function:

Dim blnCanConnect As Boolean
blnCanConnect = IsSQLServerOnline("maxsql001.maximumasp.com")

About the Author

Karl Moore (MCSD, MVP) is an experience author living in Yorkshire, England. He is author of numerous technology books, including the new Ultimate VB .NET and ASP.NET Code Book, plus regularly features at industry conferences and on BBC radio. Moore also runs his own creative consultancy, White Cliff Computing Ltd. Visit his official Web site at www.karlmoore.com.

# # #

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories