.NET SQL Server连接问题-可能与连接池有关 [英] .NET SQL Server connection issue - Maybe connection pool related
问题描述
我遇到了一个非常奇怪的问题，希望那里的某人也有类似的经历。
I am having a very strange problem and am hoping someone out there has had a similar experience.
我的公司针对一位客户的申请被禁止了SQL Server位于我们应用程序的开头。行为很奇怪。我将它以点的形式写出来。
My companies application for one client is getting "banned" from the SQL Server at the beginning of our application. The behavior is strange. I'll write it out in point form.
SQL连接已创建，数据已检索，连接已关闭，与另一个数据源对话，然后拒绝访问SQL Server
SQL Connections are created, data is retrieved, the connections are closed, talk to another datasource and then denied access to SQL Server.
这是冗长的版本：
.NET应用程序多次连接到数据库。获取一些数据，完成一些工作。然后，它将获取更多数据，然后收到错误消息找不到SQL Server或访问被拒绝。如果重新启动该过程而不重新启动该应用程序，则无法与SQL Server建立更多连接。所有新连接均导致找不到SQL Server或访问被拒绝。如果应用程序重新启动，则将重复上述过程。
.NET application connects to database multiple times. Gets some data, does some work. It then goes to get some more data and then gets an error that the "SQL Server cannot be found or access is denied". If the process is started over again without re-starting the app then no more connections are able to be made to SQL Server. All new connections result in "SQL Server cannot be found or access is denied". If the application is restarted then it will repeat the above process.
这是我使用该软件的5年中第一次遇到此问题。该应用程序确实有用Delphi 7编写的代码。dephi 7 / VBA代码没有问题。我执行实际查询的.NET代码如下：
This is the first in 5 years of my experience with the software to have this problem. The application does have code written in Delphi 7. The dephi 7 / VBA code has not issues. My .NET code that performs the actual query looks like:
protected abstract DbConnection GetConnection();
protected abstract DbDataAdapter GetDataAdapter(DbCommand cmd);
protected abstract DbCommand GetCommand(DbConnection conn, String sql);
protected abstract DbCommandBuilder GetCommandBuilder(DbDataAdapter adapter);
public virtual DataTable Query(string sql)
{
var dt = new DataTable();
using (var conn = GetConnection())
{
try
{
using (var cmd = GetCommand(conn, sql))
{
using (var adapter = GetDataAdapter(cmd))
{
adapter.Fill(dt);
}
}
}
catch (Exception ex)
{
throw new SqlStatementException(sql, ex);
}
}
return dt;
}
这是我自己的DAL。
It is my own quite and dirty DAL. When it is used it is using an OleDbConnection.
注意：由于有遗留代码，因此为OleDbConnection配置了连接字符串。花一点时间查看我的代码后，我确实可以将连接类型更改为SqlConnection。我还没有尝试过。
Note: Due to legacy code the connection string is configured for OleDbConnection. After taking a moment to review my code I do have the ability to change the connection type to SqlConnection. I haven't tried that yet.
在客户端计算机上，我无法在主应用程序之外重现该问题。我尝试创建一个小应用程序，该应用程序使用上面的格式和OleDbConnection进行了100次调用，但执行成功。
On the client's machine I have not been able to reproduce the issue outside of the main application. I tried creating a little app that would make 100 calls back to back using the format above with an OleDbConnection but it executed successfully.
主应用程序中的失败发生在同一地点。那应该给我一个线索，除非我无法理解它，因为它在进行重复查询，获取相同的数据。但是我要说的是，该应用程序与两个数据源通信，并将数据从一个传输到另一个。在进行传输之前，它会对源进行一些验证。因此它通过ODBC与另一个数据库（基于专有文件）进行通信，并成功返回，然后在尝试通过OleDbConnection与SQL Server进行通信时失败。
The failure in the main app happens in the same spot. That should give me a clue except I cannot make sense of it since it is making duplicate query, getting the same data. But I will say that the application talks to two data sources and transfers data from one to the other. Before it does the transfer it does some validation on the sources. So it talks to another database (proprietary file based) via ODBC and comes back successfully and then fails when trying to talk to SQL Server through OleDbConnection.
我的怀疑是发生在连接池中。这会导致故障，进而导致拒绝访问。
My suspicion is something is happening in the connection pool. That is causing a failure which in turns causes a denial of access.
其他有趣的地方。一切正常，运行了大约一年，几个月前客户购买了一台新机器，一切正常，然后突然停止。我将应用程序放在客户端站点上的另一台计算机上，并且运行了一周都正常，然后出现了同样的问题。我们关闭了客户端计算机上的所有内容，但问题仍然存在。我以为是防火墙，但是那里没有运气。
Other interesting points. All worked fine for about a year, client got a new machine a couple of months ago, all work fine and then suddenly stopped. I put the application on another machine at the client's site and all worked well for a week and then the same issue appeared. We turned everything off on the client's machine but the issue persisted. I thought firewall but no luck there.
任何帮助都将不胜感激。
Any assistance is greatly appreciated.
推荐答案
将要发表评论，但它太大了：-）
Was gonna put this in a comment, but it got too big :-)
我看到您的连接创建方法是抽象的。当然，这意味着派生工具在创建连接时可能会做各种坏事。我先去那里看看。
I see your connection-creating methods are abstract. This of course means that derivatives can do all sorts of bad things when they create the connection. I'd look there first.
我在类似情况下发现的一件事...如果您正在代码中做一些事情来创建连接字符串 unique ，您将不会重复使用这些池化连接。因此...执行诸如添加 App = MyApp +递增的数字，日期/时间或guid之类的操作，将会破坏您使用池化连接的能力。当这发生在我身上时，我花了我一辈子才能弄清楚。
One thing I found in a similar situation...if you're doing something in the code that creates the connection that makes the connection string unique, you won't be reusing those pooled connections. So...doing something like adding an "App=MyApp" + an incrementing number, date/time, or guid, it will destroy your ability to use pooled connections. When this happened to me, it took me forever to figure it out.
如果您的应用程序过去足够慢，以至于旧的共享连接断开从池中出来，您可能永远都不会遇到问题...但是，然后，说客户得到了新的热门硬件...而责备...从无处而来的奇怪错误！这可能不是您正在发生的事情，但也许它将为您提供一些有关外观的想法。祝你好运！
If your application was "slow enough" in the past, such that "old" pooled connections fall out of the pool, you might never see a problem...but then, say a customer gets hot new hardware...and blam...weird errors from nowhere! This might not be what's happening to you, but maybe it will give you some ideas about where to look. Good luck!
这篇关于.NET SQL Server连接问题-可能与连接池有关的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
