如何让SqlConnection超时更快 [英] How to make SqlConnection timeout more quickly

问题描述

我正在使用带有 SqlClient.SqlConnection 的 SQL 连接字符串并在字符串中指定 Connection Timeout=5，但它仍然等待 30 秒才返回失败.我如何让它放弃并更快地返回?我在一个快速的本地网络上，不想等待 30 秒.未开启的服务器需要 30 秒才能发生故障.这只是一个快速的实用程序，将始终仅在此本地网络上运行.

I am using an SQL connection string with SqlClient.SqlConnection and specifying Connection Timeout=5 in the string, but it still waits 30 seconds before returning failure. How do I make it give up and return faster? I'm on a fast local network and don't want to wait 30 seconds. The servers that are not turned on take 30 seconds to fail. This is just a quick utility program that's going to always run just on this local network.

编辑:抱歉，如果我不清楚.我希望 SqlConnection.Open 更快地失败.希望这可以从我想要更快失败的服务器关闭这一事实推导出来.

Edit: Sorry if I was unclear. I want the SqlConnection.Open to fail more quickly. Hopefully that could be deduced from the fact that the servers I want to fail more quickly are turned off.

编辑:似乎设置有时会失败.就像它知道服务器的 IP 地址，并且正在使用 TCP/IP 与它通信(不是本地的)，但无法通过该地址联系 SQL Server?我不确定该模式是什么，但是在本地连接 SQL Server 时我没有看到问题，并且在尝试连接到不存在的服务器时我没有看到它.不过，我在尝试联系 Windows 2008 防火墙阻止 SQL Server 的服务器时看到了它.

Edit: It seems that the setting only fails sometimes. Like it knows the IP address of the server, and is using TCP/IP to talk to it (not local) but can't contact SQL Server at that address? I'm not sure what the pattern is, but I don't see the problem when connecting locally with SQL Server stopped, and I don't see it when attempting to connect to a non-existent server. I have seen it when attempting to contact a server where the Windows 2008 firewall is blocking SQL Server, though.

推荐答案

看起来所有导致长时间延迟的情况都可以通过尝试像这样的直接套接字连接来更快地解决:

It looks like all the cases that were causing long delays could be resolved much more quickly by attempting a direct socket connection like this:

foreach (string svrName in args)
{
   try
   {
      System.Net.Sockets.TcpClient tcp = new System.Net.Sockets.TcpClient(svrName, 1433);
      if (tcp.Connected)
         Console.WriteLine("Opened connection to {0}", svrName);
      else
         Console.WriteLine("{0} not connected", svrName);
      tcp.Close();
   }
   catch (Exception ex)
   {
      Console.WriteLine("Error connecting to {0}: {1}", svrName, ex.Message);
   }
}

我将使用此代码来检查服务器是否在 SQL Server 端口上响应，并且仅在响应时尝试打开连接.我认为(根据其他人的经验)即使在这个级别也会有 30 秒的延迟，但我收到一条消息，即机器立即主动拒绝连接".

I'm going to use this code to check if the server responds on the SQL Server port, and only attempt to open a connection if it does. I thought (based on others' experience) that there would be a 30 second delay even at this level, but I get a message that the machine "actively refused the connection" on these right away.

如果机器不存在，它也会立即告诉我.我找不到 30 秒的延迟.

And if the machine doesn't exist, it tells me that right away too. No 30-second delays that I can find.

在网络上但没有关闭的机器我猜仍然需要 30 秒才能失败.不过，受防火墙保护的机器失败得更快.

Machines that were on the network but are not turned off still take 30 seconds to fail I guess. The firewalled machines fail faster, though.

这是更新后的代码.我觉得关闭套接字比中止线程更干净:

Here's the updated code. I feel like it's cleaner to close a socket than abort a thread:

static void TestConn(string server)
{
   try
   {
      using (System.Net.Sockets.TcpClient tcpSocket = new System.Net.Sockets.TcpClient())
      {
         IAsyncResult async = tcpSocket.BeginConnect(server, 1433, ConnectCallback, null);
         DateTime startTime = DateTime.Now;
         do
         {
            System.Threading.Thread.Sleep(500);
            if (async.IsCompleted) break;
         } while (DateTime.Now.Subtract(startTime).TotalSeconds < 5);
         if (async.IsCompleted)
         {
            tcpSocket.EndConnect(async);
            Console.WriteLine("Connection succeeded");
         }
         tcpSocket.Close();
         if (!async.IsCompleted)
         {
            Console.WriteLine("Server did not respond");
            return;
         }
      }
   }
   catch(System.Net.Sockets.SocketException ex)
   {
      Console.WriteLine(ex.Message);
   }
}

这篇关于如何让SqlConnection超时更快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！