检查SQL Server服务器可用性的最快方法是什么?

问题描述

我有一个在多个位置使用的MS Access程序.它连接到MS SQL Server表，但是服务器名称在每个位置都不同.我正在寻找测试服务器是否存在的最快方法.我当前正在使用的代码如下所示:

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:执行一个shell命令并等待其完成
Qt:将字符串用双引号引起来
fn:临时文件名变量

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

我对服务器名称列表(通常只有一个服务器名称)运行以上代码.如果服务器可用，该代码将花费大约一秒钟的时间，而对于每个不可用的服务器，该代码将花费大约8秒的时间.如果可能的话，我想将这两个数值都降低一些，但尤其是失败的情况，因为这种情况最常发生.

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.

推荐答案

我最终选择的解决方案是使用nslookup.exe作为sc.exe命令的前身.如果SQL Server服务器不存在，则nslookup会立即告诉我.进行此更改将SQL Server查找失败所需的时间从大约8秒减少到不到1秒.成功案例实际上会稍长一些，但并不引人注意.对于那些可能感兴趣的人，这是我的最终解决方案(希望我的个人功能[ShellWait，Qt，PassThru，LogError]的目的很明显):

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):

更新:我已经更新了该功能，以合并dmaruca的clsRunApp(我最喜欢的新类模块)以及Philippe提出的有关在断开模式下工作的问题.结果比我最初发布的要好得多，我要感谢他们俩的贡献.现在的功能如下:

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

注意:我知道Environ("COMPUTERNAME")不是确定计算机名称的100％可靠的方法，因此，如果需要，可以随时用您自己的代码替换.我认为在这里，懒惰方法足以满足其目的.

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.

这篇关于检查SQL Server服务器可用性的最快方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！