Checking Whether SQL Server Is Available
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.
# # #