What's the fastest way to check the availability of a SQL Server server?

Question

I have an MS Access program in use in multiple locations. It connects to MS SQL Server tables, but the server name is different in each location. I am looking for the fastest way to test for the existence of a server. The code I am currently using looks like this:

ShellWait "sc \\" & ServerName & " qdescription MSSQLSERVER > " & Qt(fn)
FNum = FreeFile()
Open fn For Input As #FNum
Line Input #FNum, Result
Close #FNum
Kill fn

If InStr(Result, "SUCCESS") Then ...

ShellWait: executes a shell command and waits for it to finish
Qt: wraps a string in double quotes
fn: temporary filename variable

I run the above code against a list of server names (of which only one is normally available). The code takes about one second if the server is available and takes about 8 seconds for each server that is unavailable. I'd like to get both of these lower, if possible, but especially the fail case as this one happens most often.

Answer 1

The way I've done this (in the distant past) was to use linked tables and have a User form that allows a server to be one-off selected at runtime. Alternatively, you would place the server name in a config file and dynamically create the connection string using it.

Answer 2

telnet servername 1433

Answer 3

You could try to create an ADO connection and set the timeout to some low value, e.g. (untested):

Dim cn As ADODB.Connection 
Set cn = New ADODB.Connection
cn.ConnectionTimeout = 4     ' Wait at most 4 seconds for connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=" & ServerName & ";Integrated Security=SSPI"

On Error Resume Next
cn.Open
If Err.Number > 0 Then
    ...
Else
    cn.Close
    ...
End If
On Error Goto 0   ' replace 0 with previously used error handler

Answer 4

The solution I eventually settled on was to use nslookup.exe as a precursor to my sc.exe command. If the SQL Server server does not exist, nslookup tells me so immediately. Making this change cut down the time it took to fail on a SQL Server lookup from about 8 seconds to well under 1 second. The success case is actually slightly longer, but not noticeable. For those who may be interested, here is my final solution (hopefully the purpose of my personal functions [ShellWait, Qt, PassThru, LogError] will be obvious):

UPDATE: I've updated the function to incorporate dmaruca's clsRunApp (my new favorite class module) and the issue raised by Philippe concerning working in disconnected mode. The result is much better than I originally posted and I'd like to thank both of them for their contributions. Here's the function as it stands now:

Function SQLServerDBExists(ComputerName As String, DbName As String) As Boolean
Const LocalHost = "127.0.0.1"
Dim Result As String, RunApp As New clsRunApp

    On Error GoTo Err_SQLServerDBExists

    If ComputerName <> LocalHost And _
       ComputerName <> "." And _
       ComputerName <> Environ("COMPUTERNAME") Then
        'Check for existence of the server using Name Server Lookup'
        Result = RunApp.RunAppWait_CaptureOutput("nslookup " & ComputerName)
        If InStr(Result, "Non-existent domain") Or _
           InStr(Result, "Default servers are not available") Then
            SQLServerDBExists = False
            GoTo Exit_SQLServerDBExists
        End If
    End If

    Result = RunApp.RunAppWait_CaptureOutput("sc \\" & ComputerName & " qdescription MSSQLSERVER")
    If InStr(Result, "SUCCESS") Then
        With PassThru("SELECT Name FROM sysdatabases " & _
                      "WHERE Name='" & DbName & "'", "master", ComputerName)
            SQLServerDBExists = (Not .EOF)
        End With
    End If

Exit_SQLServerDBExists:
    Exit Function
Err_SQLServerDBExists:
    LogError Err.Number, Err.Description, "SQLServerDBExists", "AttachToSQL"
    Resume Exit_SQLServerDBExists
End Function

Note: I realize Environ("COMPUTERNAME") is not a 100% reliable way of determining the computer's name, so feel free to replace that with your own code if you want. I think the lazy approach is sufficient for its purpose here.

Answer 5

The solution proposed here works great in specific conditions, where the computer is networked and a dns server is available. If your application is supposed to work in both connected (where you can connect to one of the 'main' servers) and disconnected mode (where you connect to your local copy of the database), this solution will not do it.

Our generic, efficient but (I must admit) not-so-smart solution is until now to have a client-side connection table (in fact xml files - one per connection - in the app folder) and let the user choose the connection at startup. depending on the user, the place(s) he works in, and his ability to work off-line, we can choose which xml-connection strings to install on his computer.

Our idea is (when we'll have time) to use the ip address of the computer to identify/calculate the 'best' database server available: if computer is not networked, connection will be set to 'localhost'. Otherwise, connection will be built 'on the fly', on a network where database servers are given a predefinite ip suffix. Thus, when a computer's ip is aaa.bbb.ccc.ddd, the machine will know it has to connect to aaa.bbb.120.132, where 120.132 is the predefined database server suffix.

Answer 6

I apologize if I am out of line since I am new here, but I wanted to make a suggestion. You can actually capture the output of shelled commands without using an intermediate text file using windows api. I created a small class file that wraps this up cleanly (link).

Using this you can refactor the code to this and not have to worry about the added headache of reading files.

Function SQLServerDBExists(ServerName As String, DbName As String) As Boolean
Dim Result As String
Dim cls as new clsRunApp

On Error GoTo Err_SQLServerDBExists

'Check for existence of the server
Result = cls.RunAppWait_CaptureOutput("nslookup " & ServerName)
If InStr(Result, "Non-existent domain") Then
    SQLServerDBExists = False
    GoTo Exit_SQLServerDBExists
End If

 Result = cls.RunAppWait_CaptureOutput("sc \\" & ServerName & " qdescription MSSQLSERVER")
If InStr(Result, "SUCCESS") Then
    With PassThru("SELECT Name FROM sysdatabases " & _
                  "WHERE Name='" & DbName & "'", "master", ServerName)
        SQLServerDBExists = (Not .EOF)
    End With
End If

Exit_SQLServerDBExists:
    Exit Function
Err_SQLServerDBExists:
    LogError Err.Number, Err.Description, "SQLServerDBExists", "AttachToSQL", , , Erl
    Resume Exit_SQLServerDBExists
End Function

Answer 7

Hello I had a dilemma working in Access97 which this solution will work very well for. I just had a question about the PassThru function that appears in the code which I do not see defined anywhere. Can you elaborate please? Thank you!