SQL Server在查询期间丢失连接 [英] SQL Server losing connection during query

问题描述

下午好，



我们之前使用仓库内的本地PC作为SQL服务器在内部开发了一款应用程序。



在过去的一周里，我们的企业IT为我们提供了一个Windows Server 2008 R2实例，我们将SQL Server Express实例移到了那个盒子里。在我们自己的建筑物内的网络上不再保留物理位置，而是在我们公司总部5英里的路上。



我们每天两到三次看到网络中断，导致我们的SQL查询挂起。我们在VB代码中没有收到任何错误...我们更新了我们的错误陷阱，但它没有任何结果......没有返回任何结果。



有人知道如何处理这些类型的错误，所以我们可以自然恢复吗？奇怪的是，当我们进入内部网络时，任何内部中断都会触发我们的错误陷阱，我们可以通过编程方式恢复，但是在场外服务器上我们什么也得不到......



核心SQL过程详情如下......



非常感谢你的帮助！



-Bert

Good afternoon,

We have previously developed an app in-house using a local PC within the warehouse as a SQL server.

In the past week our corporate IT spun up an instance of Windows Server 2008 R2 for us, and we moved our SQL Server Express instance to that box. The physical location is no longer maintained on the network within our own building, but in our corporate HQ 5 miles up the road.

Two or three times a day we are seeing network disruptions, which are causing our SQL queries to hang. We're receiving no errors within our VB code... we updated our error traps to be wide open, but it's catching nothing... there's just no results being returned.

Does anybody know how to handle these types of errors, so we can recover naturally? It's strange that when we were on our internal network, any internal disruption did fire off our error traps, and we could recover programmatically, but on the off-site server we get nothing...

The "core" SQL process is detailed below...

Thank you very much for your help !

-Bert

Public Function GetSqlServer(cmd As String, Timeout As Integer) As Object
    Try

        Dim da As New SqlClient.SqlDataAdapter
        Dim dc As New SqlClient.SqlCommand
        Dim dc2 As New SqlClient.SqlConnection

        Dim s As Object = Commandstring
        Dim s2 As String = s
        dc2.ConnectionString = s2
        dc.CommandText = cmd
        dc.Connection = dc2
        dc.CommandTimeout = Timeout
        da.SelectCommand = dc
        Dim ds As New DataSet
        da.Fill(ds)
        dc2.Close()
        Return ds
    Catch ex As Exception
        Return ex
    End Try
End Function

推荐答案

可能有几个原因导致失去与SQL Server的连接互联网...

There could be several reasons of losing connection to SQL Server over internet...

1. 互联网提供商：互联网和/或连接中断的速度限制
正如您所说：在我们自己的建筑物内的网络上不再保留物理位置，而是在我们公司总部5英里的路上。这意味着你。这意味着您的公司使用Internet连接，此连接可能随时中断。




2. 大数据传输使用 SELECT *
永远不要使用：

1. Internet provider: speed transfer limit over the internet and/or connection breaks
As you had mentioned: "The physical location is no longer maintained on the network within our own building, but in our corporate HQ 5 miles up the road." This means that you . It means that your company uses Internet connection and this connection could be broken at any time.


2. Large data transfer using SELECT *
Never use:

SELECT *
FROM TableName

更好地使用：

Better use:

SELECT Field1, Field2, Field3
FROM TableName

Windows防火墙设置

Windows防火墙可能会在指定的时间段后终止连接，需要重新配置。

配置Windows防火墙以允许SQL Server访问 [ ^ ]

SQL Server设置

SQL Server在指定的超时后重置连接，并且数据库驱动程序不通知客户端（SqlClient）。

故障排除：超时已过期 [ ^ ]

最后：错误的代码设计 - 数据库连接从未关闭

在我看来，当连接是打开，你应该尽快关闭它。这可能是防火墙定期重置连接的原因。

Windows firewall settings

Windows firewall could kill connection after a specified period of time and need to be re-configured.
Configure the Windows Firewall to Allow SQL Server Access[^]

SQL Server Settings

SQL server resets connection after specified timeout and the client is not notified by the database driver (SqlClient).
Troubleshooting: Timeout Expired[^]

and finally: bad code design - database connection has been never closed

In my opinion, when the connection is opened, you should close it ASAP. It could be the reason why firewall periodically resets connection.

这篇关于SQL Server在查询期间丢失连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！